June 9, 2008 at 11:23 am
Is there anything wrong in the Query. It is taking longer time to run this .
select
P.productid as ISBN,
Max(product.TAP) + Max(product.TAR) + Max(product.TAC) as TAPTARTAC,
Max(product.client) as Client,
Max(product.description) as Title,
Max(product.author) as Author,
--ISNULL(Max(F.FirstSale),' ') as PubDate,
Max(product.PubDate) as PubDate,
CASE
--WHEN ISNULL(Max(F.FirstSale),' ') > 0 THEN DATEADD(dd,20,Max(F.FirstSale) )
WHEN ISNULL(Max(product.PubDate),' ') > 0 THEN DATEADD(dd,20,Max(product.PubDate) )
ELSE ' '
END as SaleDate,
ISNULL(Max(product.list),0) as ListPrice,
ISNULL(Max(product.canlist),0) as CanPrice,
0 as UKPrice,
Max( CommitQty.CommitQty ) as CommitQty,
Max(ISNULL(ProductInventoryWithProductID.QOHMinusIntl,0) - ISNULL(CommitQty.CommitQty,0) - ISNULL(ProductInventoryWithProductID.RsvrdQtyMinusIntl,0) - ISNULL(BO.BOQty,0) ) as AvailableStock,
Max(ISNULL(ProductInventoryWithProductID.QOHMinusIntl,0) - ISNULL(CommitQty.CommitQty,0) - ISNULL(ProductInventoryWithProductID.RsvrdQtyMinusIntl,0) ) as AvailableBeforeBO,
Max(ISNULL(BO.BOQty,0)) as BackorderQty,
'UNUSED' as FWDEXT,
'UNUSED' as ESITE,
Max(ProductInventoryWithProductID.RsvrdQtyMinusIntl) as QuotationsReserved,
Max(BO.BOQty) as Dues,
'NYA' as FrozenLotType,
'UNUSED' as ZFWDAVAIL,
'UNUSED' as ZFROZEN,
'NYA' as ALTSITES,
Max(ProductInventoryWithProductID.QOHMinusIntl) as ActualStock,
ISNULL(Max(PerseusWarningLevel.WarningLevel),0) as WarningLevel,
ISNULL(Max(PerseusWarningLevel.ReviewDate),'1/1/1900') as ReviewDate,
Max(product.LastPriceChangeDate) as LastPriceDate,
Max(product.FormerPrice) as FormerPrice,
Max(product.EAN) as EAN,
Max(product.BISACCode) as BISACCode,
Max(product.SubjectInterestCode) as SubjectInterestCode,
Max(product.Edition) as Edition,
Max(product.ProductClass) as TheProductClass,
Max(product.ClientCode) as ClientCode,
ISNULL(Max(Product.Carton),0) as PackSizeCarton,
ISNULL(Max(Product.PageCount),0) as PageCount,
ISNULL(Max(Product.TrimSize),0) as TrimSize,
ISNULL(Max(product.BookWeight),0) as BookWeight,
ISNULL(Max(product.BookHeight),0) as BookHeight,
ISNULL(Max(product.BookLength),0) as BookLength,
ISNULL(Max(product.BookWidth),0) as BookWidth,
ISNULL(Max(product.CartonWeight),0) as CartonWeight,
ISNULL(Max(product.CartonHeight),0) as CartonHeight,
ISNULL(Max(product.CartonLength),0) as CartonLength,
ISNULL(Max(product.CartonWidth),0) as CartonWidth,
Max(product.ReturnableFlag) as Returnable,
Max(product.ImpressionNumber) as ImpressionNumber,
Max(product.SellingTerritories) as SellingTerritories,
Max(product.BinderyManufacturer) as BinderyManufacturer,
Max(product.LastUnitCost) as UnitCost,
Max(product.AnswerCode) AS AnswerCodeStatus,
Max(ProductInventoryWithProductID.ProdStatus) as InventoryStatus,
CASE Max(ProductInventoryWithProductID.ProdStatus)
WHEN 1 THEN 'ACTIVE'
WHEN 2 THEN 'NYP'
WHEN 3 THEN 'O/P'
WHEN 4 THEN 'O/S CNCL'
WHEN 5 THEN 'NYP CNCL'
WHEN 6 THEN 'NYP'
WHEN 7 THEN 'NO FILE'
WHEN 8 THEN 'PRM O/S'
WHEN 9 THEN 'O/S BKO'
WHEN 999 THEN 'DELETED'
END as InventoryStatusText,
CASE
WHEN ISNULL(Max(product.PubDate),' ') > 0 AND DATEADD(dd,20,Max(product.PubDate) ) > GetDate() THEN 1
WHEN ISNULL(Max(product.PubDate),' ') <= 0 and Max(ProductInventoryWithProductID.ProdStatus) = 2 THEN 1
WHEN ISNULL(Max(product.PubDate),' ') <= 0 and Max(ProductInventoryWithProductID.ProdStatus) = 5 THEN 1
WHEN ISNULL(Max(product.PubDate),' ') <= 0 and Max(ProductInventoryWithProductID.ProdStatus) = 6 THEN 1
ELSE 0
END as NYPSwitch,
Max(product.DiscCl) as TitleDiscount,
Max(product.ProductClass) as ProductClass,
Max(PAS.qty) as TotalInPrint,
'UNUSED' as AnswerDate,
'NYA' as SubDept,
'NYA' as Imprint,
'Unused' as Unauthorized,
Max(product.ReorderNumberA) as ReprintAQty,
Max(product.ReorderNumberB) as ReprintBQty,
Max(product.ReorderNumberC) as ReprintCQty,
Max(product.ReorderNumberD) as ReprintDQty,
Max(product.ReorderNumberE) as ReprintEQty,
Max(product.StockDueDatesA) as ReprintADate,
Max(product.StockDueDatesB) as ReprintBDate,
Max(product.StockDueDatesC) as ReprintCDate,
Max(product.StockDueDatesD) as ReprintDDate,
Max(product.StockDueDatesE) as ReprintEDate,
ISNULL(Max(TC.Comment),' ') as Comment,
'Comments drilldown' as CommentDrilldown,
@MonthName13 as MonthName13,
@MonthName12 as MonthName12,
@MonthName11 as MonthName11,
@MonthName10 as MonthName10,
@MonthName9 as MonthName9,
@MonthName8 as MonthName8,
@MonthName7 as MonthName7,
@MonthName6 as MonthName6,
@MonthName5 as MonthName5,
@MonthName4 as MonthName4,
@MonthName3 as MonthName3,
@MonthName2 as MonthName2,
@MonthName1 as MonthName1,
Max(R13.NetQty13) + Max(R13.ARCreditQty13) + Max(R13.ARRebillQty13) + Max(R13.BillableXferQty13) AS Month13NetQty,
Max(R13.ReturnQty13) AS Month13ReturnQty,
Max(R13.SalesQty13) + Max(R13.ARCreditQty13) + Max(R13.ARRebillQty13) + Max(R13.BillableXferQty13) AS Month13GrossQty,
Max(R13.ReviewQty13) AS Month13ReviewQty,
Max(R13.LooseQty13) + Max(R13.LooseARCreditQty13) + Max(R13.LooseARRebillQty13) + Max(R13.LooseBillableXferQty13) AS Month13LooseQty,
Max(R13.NetQty12) + Max(R13.ARCreditQty12) + Max(R13.ARRebillQty12) + Max(R13.BillableXferQty12) AS Month12NetQty,
Max(R13.ReturnQty12) AS Month12ReturnQty,
Max(R13.SalesQty12) + Max(R13.ARCreditQty12) + Max(R13.ARRebillQty12) + Max(R13.BillableXferQty12) AS Month12GrossQty,
Max(R13.ReviewQty12) AS Month12ReviewQty,
Max(R13.LooseQty12) + Max(R13.LooseARCreditQty12) + Max(R13.LooseARRebillQty12) + Max(R13.LooseBillableXferQty12) AS Month12LooseQty,
Max(R13.NetQty11) + Max(R13.ARCreditQty11) + Max(R13.ARRebillQty11) + Max(R13.BillableXferQty11) AS Month11NetQty,
Max(R13.ReturnQty11) AS Month11ReturnQty,
Max(R13.SalesQty11) + Max(R13.ARCreditQty11) + Max(R13.ARRebillQty11) + Max(R13.BillableXferQty11) AS Month11GrossQty,
Max(R13.ReviewQty11) AS Month11ReviewQty,
Max(R13.LooseQty11) + Max(R13.LooseARCreditQty11) + Max(R13.LooseARRebillQty11) + Max(R13.LooseBillableXferQty11) AS Month11LooseQty,
Max(R13.NetQty10) + Max(R13.ARCreditQty10) + Max(R13.ARRebillQty10) + Max(R13.BillableXferQty10) AS Month10NetQty,
Max(R13.ReturnQty10) AS Month10ReturnQty,
Max(R13.SalesQty10) + Max(R13.ARCreditQty10) + Max(R13.ARRebillQty10) + Max(R13.BillableXferQty10) AS Month10GrossQty,
Max(R13.ReviewQty10) AS Month10ReviewQty,
Max(R13.LooseQty10) + Max(R13.LooseARCreditQty10) + Max(R13.LooseARRebillQty10) + Max(R13.LooseBillableXferQty10) AS Month10LooseQty,
Max(R13.NetQty9) + Max(R13.ARCreditQty9) + Max(R13.ARRebillQty9) + Max(R13.BillableXferQty9) AS Month9NetQty,
Max(R13.ReturnQty9) AS Month9ReturnQty,
Max(R13.SalesQty9) + Max(R13.ARCreditQty9) + Max(R13.ARRebillQty9) + Max(R13.BillableXferQty9) AS Month9GrossQty,
Max(R13.ReviewQty9) AS Month9ReviewQty,
Max(R13.LooseQty9) + Max(R13.LooseARCreditQty9) + Max(R13.LooseARRebillQty9) + Max(R13.LooseBillableXferQty9) AS Month9LooseQty,
Max(R13.NetQty8) + Max(R13.ARCreditQty8) + Max(R13.ARRebillQty8) + Max(R13.BillableXferQty8) AS Month8NetQty,
Max(R13.ReturnQty8) AS Month8ReturnQty,
Max(R13.SalesQty8) + Max(R13.ARCreditQty8) + Max(R13.ARRebillQty8) + Max(R13.BillableXferQty8) AS Month8GrossQty,
Max(R13.ReviewQty8) AS Month8ReviewQty,
Max(R13.LooseQty8) + Max(R13.LooseARCreditQty8) + Max(R13.LooseARRebillQty8) + Max(R13.LooseBillableXferQty8) AS Month8LooseQty,
Max(R13.NetQty7) + Max(R13.ARCreditQty7) + Max(R13.ARRebillQty7) + Max(R13.BillableXferQty7) AS Month7NetQty,
Max(R13.ReturnQty7) AS Month7ReturnQty,
Max(R13.SalesQty7) + Max(R13.ARCreditQty7) + Max(R13.ARRebillQty7) + Max(R13.BillableXferQty7) AS Month7GrossQty,
Max(R13.ReviewQty7) AS Month7ReviewQty,
Max(R13.LooseQty7) + Max(R13.LooseARCreditQty7) + Max(R13.LooseARRebillQty7) + Max(R13.LooseBillableXferQty7) AS Month7LooseQty,
Max(R13.NetQty6) + Max(R13.ARCreditQty6) + Max(R13.ARRebillQty6) + Max(R13.BillableXferQty6) AS Month6NetQty,
Max(R13.ReturnQty6) AS Month6ReturnQty,
Max(R13.SalesQty6) + Max(R13.ARCreditQty6) + Max(R13.ARRebillQty6) + Max(R13.BillableXferQty6) AS Month6GrossQty,
Max(R13.ReviewQty6) AS Month6ReviewQty,
Max(R13.LooseQty6) + Max(R13.LooseARCreditQty6) + Max(R13.LooseARRebillQty6) + Max(R13.LooseBillableXferQty6) AS Month6LooseQty,
Max(R13.NetQty5) + Max(R13.ARCreditQty5) + Max(R13.ARRebillQty5) + Max(R13.BillableXferQty5) AS Month5NetQty,
Max(R13.ReturnQty5) AS Month5ReturnQty,
Max(R13.SalesQty5 ) + Max(R13.ARCreditQty5) + Max(R13.ARRebillQty5) + Max(R13.BillableXferQty5) AS Month5GrossQty,
Max(R13.ReviewQty5 ) AS Month5ReviewQty,
Max(R13.LooseQty5) + Max(R13.LooseARCreditQty5) + Max(R13.LooseARRebillQty5) + Max(R13.LooseBillableXferQty5) AS Month5LooseQty,
Max(R13.NetQty4) + Max(R13.ARCreditQty4) + Max(R13.ARRebillQty4) + Max(R13.BillableXferQty4) AS Month4NetQty,
Max(R13.ReturnQty4 ) AS Month4ReturnQty, Max(R13.SalesQty4) + Max(R13.ARCreditQty4) + Max(R13.ARRebillQty4) + Max(R13.BillableXferQty4) AS Month4GrossQty,
Max(R13.ReviewQty4 ) AS Month4ReviewQty,
Max(R13.LooseQty4) + Max(R13.LooseARCreditQty4) + Max(R13.LooseARRebillQty4) + Max(R13.LooseBillableXferQty4) AS Month4LooseQty,
Max(R13.NetQty3) + Max(R13.ARCreditQty3) + Max(R13.ARRebillQty3) + Max(R13.BillableXferQty3) AS Month3NetQty,
Max(R13.ReturnQty3 ) AS Month3ReturnQty,
Max(R13.SalesQty3 ) + Max(R13.ARCreditQty3) + Max(R13.ARRebillQty3) + Max(R13.BillableXferQty3) AS Month3GrossQty,
Max(R13.ReviewQty3 ) AS Month3ReviewQty,
Max(R13.LooseQty3) + Max(R13.LooseARCreditQty3) + Max(R13.LooseARRebillQty3) + Max(R13.LooseBillableXferQty3) AS Month3LooseQty,
Max(R13.NetQty2) + Max(R13.ARCreditQty2) + Max(R13.ARRebillQty2) + Max(R13.BillableXferQty2) AS Month2NetQty,
Max(R13.ReturnQty2 ) AS Month2ReturnQty,
Max(R13.SalesQty2 ) + Max(R13.ARCreditQty2) + Max(R13.ARRebillQty2) + Max(R13.BillableXferQty2) AS Month2GrossQty,
Max(R13.ReviewQty2 ) AS Month2ReviewQty,
Max(R13.LooseQty2) + Max(R13.LooseARCreditQty2) + Max(R13.LooseARRebillQty2) + Max(R13.LooseBillableXferQty2) AS Month2LooseQty,
Max(R13.NetQty1) + Max(R13.ARCreditQty1) + Max(R13.ARRebillQty1) + Max(R13.BillableXferQty1) AS Month1NetQty,
Max(R13.ReturnQty1 ) AS Month1ReturnQty,
Max(R13.SalesQty1 ) + Max(R13.ARCreditQty1) + Max(R13.ARRebillQty1) + Max(R13.BillableXferQty1) AS Month1GrossQty,
Max(R13.ReviewQty1 ) AS Month1ReviewQty,
Max(R13.LooseQty1 ) + Max(R13.LooseARCreditQty1) + Max(R13.LooseARRebillQty1) + Max(R13.LooseBillableXferQty1) AS Month1LooseQty,
Max(FYCurr.NetQty) + Max(FYCurr.ARCreditQty) + Max(FYCurr.ARRebillQty) + Max(FYCurr.BillableXferQty) AS FYCurrNetQty,
Max(FYCurr.ReturnQty) AS FYCurrReturnQty,
Max(FYCurr.SalesQty) + Max(FYCurr.ARCreditQty) + Max(FYCurr.ARRebillQty) + Max(FYCurr.BillableXferQty) AS FYCurrGrossQty,
Max(FYCurr.ReviewQty) AS FYCurrReviewQty,
Max(FYCurr.LooseQty) + Max(FYCurr.LooseARCreditQty) + Max(FYCurr.LooseARRebillQty) + Max(FYCurr.LooseBillableXferQty) AS FYCurrLooseQty,
Max(FYPrev.NetQty) + Max(FYPrev.ARCreditQty) + Max(FYPrev.ARRebillQty) + Max(FYPrev.BillableXferQty) AS FYPrevNetQty,
Max(FYPrev.ReturnQty) AS FYPrevReturnQty,
Max(FYPrev.SalesQty) + Max(FYPrev.ARCreditQty) + Max(FYPrev.ARRebillQty) + Max(FYPrev.BillableXferQty) AS FYPrevGrossQty,
Max(FYPrev.ReviewQty) AS FYPrevReviewQty,
Max(FYPrev.LooseQty) + Max(FYPrev.LooseARCreditQty) + Max(FYPrev.LooseARRebillQty) + Max(FYPrev.LooseBillableXferQty) AS FYPrevLooseQty,
Max(FY2Ago.NetQty) + Max(FY2Ago.ARCreditQty) + Max(FY2Ago.ARRebillQty) + Max(FY2Ago.BillableXferQty) AS FY2AgoNetQty,
Max(FY2Ago.ReturnQty) AS FY2AgoReturnQty,
Max(FY2Ago.SalesQty) + Max(FY2Ago.ARCreditQty) + Max(FY2Ago.ARRebillQty) + Max(FY2Ago.BillableXferQty) AS FY2AgoGrossQty,
Max(FY2Ago.ReviewQty) AS FY2AgoReviewQty,
Max(FY2Ago.LooseQty) + Max(FY2Ago.LooseARCreditQty) + Max(FY2Ago.LooseARRebillQty) + Max(FY2Ago.LooseBillableXferQty) AS FY2AgoLooseQty,
Max(ROM.Avg3MoROM) AS ROM3Month,
Max(ROM.Avg6MoROM) AS ROM6Month,
Max(ROM.Avg12MoROM) AS ROM12Month,
Max(ROM.FrontListROM) AS ROMFrontList,
Max(Lifetime.NetQty) + Max(Lifetime.ARCreditQty) + Max(Lifetime.ARRebillQty) + Max(Lifetime.BillableXferQty) AS LifetimeNetQty,
Max(Lifetime.ReturnQty) AS LifetimeReturnQty,
Max(Lifetime.SalesQty) + Max(Lifetime.ARCreditQty) + Max(Lifetime.ARRebillQty) + Max(Lifetime.BillableXferQty) AS LifetimeGrossQty,
Max(Lifetime.ReviewQty) AS LifetimeReviewQty
from
@P P
inner join Product on P.ProductID = Product.ProductID
--left outer join ( select * from fn_rpt_GetFirstSaleDate(@list_number) ) F on P.ProductID = F.ISBN
left outer join ProductInventoryWithProductID on P.productid = ProductInventoryWithProductID.productid
left outer join CommitQty on P.productid = CommitQty.productid
left outer join @PAS PAS on P.productid = PAS.productid
left outer join PerseusWarningLevel on P.productid = PerseusWarningLevel.productid
left outer join @TC TC on P.productid = TC.productid
/*
left outer join ProductActivity_Summary on P.productid = ProductActivity_Summary.productid
and ProductActivity_Summary.TranType = 'RCT'
left outer join PerseusWarningLevel on P.productid = PerseusWarningLevel.productid
left outer join PerseusTitleComments on P.productid = PerseusTitleComments.productid
and PerseusTitleComments.DatePosted
in (select max(DatePosted) from PerseusTitleComments
where PerseusTitleComments.productid = P.ProductID and PerseusTitleComments.Protected = 'Y' )
and PerseusTitleComments.Protected = 'Y'
*/
LEFT OUTER JOIN
( select * from fn_rpt_GetGNR13Months(@list_number, @varToday, @StartMo1, @EndMo1) ) R13
on P.ProductID = R13.ISBN
left outer join ( select * from fn_rpt_GetGNR_Month(@list_number, @varToday, @FYCurrStart, @FYCurrEnd) ) FYCurr
on P.ProductID = FYCurr.ISBN
left outer join ( select * from fn_rpt_GetGNR_Month(@list_number, @varToday, @FYPrevStart, @FYPrevEnd) ) FYPrev
on P.ProductID = FYPrev.ISBN
left outer join ( select * from fn_rpt_GetGNR_Month(@list_number, @varToday, @FY2AgoStart, @FY2AgoEnd) ) FY2Ago
on P.ProductID = FY2Ago.ISBN
left outer join ( select * from fn_rpt_GetROMExcludingOrderTypes(@list_number, @varToday, @StartMo1, @EndMo1) ) ROM
on P.ProductID = ROM.ISBN
left outer join ( select * from fn_rpt_GetGNR_Lifetime(@list_number) ) Lifetime
on P.ProductID = Lifetime.ISBN
left outer join ( select * from fn_rpt_GetBO(@list_number, @TempDate ) ) BO
on P.ProductID = BO.ISBN
group by P.productid
ORDER BY P.ProductId DESC
June 9, 2008 at 11:52 am
yes it is.
Perhaps is you give us some more information we can help you. See this link: http://qa.sqlservercentral.com/articles/Best+Practices/61537/
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
June 9, 2008 at 1:47 pm
Yes, I would be surprised if that query ran very quickly at all.
I'd break the thing down into a few temp tables, based on the various cases and functions being run, then join them together in the end.
Is it really aggregating all those MAX columns for some mathematical reason, or is it just to get rid of duplicate rows?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply