Part Usage - Rolling Months with Sum Case Code Question 2

[Jcfx]

Greetings,

Can someone pinpoint where I may have gone wrong with my code here? CurMonth results are correct, but the rest are coming up as zero.

Sample Data:
50012  12-01-08 100
50012  12-02-08  36
50012  11-28-08  52
50012  10-01-08  46
50012  10-15-08  12

output desired:

50012  136  52  58 etc

Declare @CurrentMonth Int
Set @CurrentMonth = Datediff(Month,0,Getdate())

SELECT

  ol.partnumber
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth then (ol.qtyreqd) else 0 End) as CurMonth
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth + 1 then (ol.qtyreqd) else 0 End) as Prev01
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth + 2 then (ol.qtyreqd) else 0 End) as Prev02
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth + 3 then (ol.qtyreqd) else 0 End) as Prev03
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth + 4 then (ol.qtyreqd) else 0 End) as Prev04
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth + 5 then (ol.qtyreqd) else 0 End) as Prev05
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth + 6 then (ol.qtyreqd) else 0 End) as Prev06
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth + 7 then (ol.qtyreqd) else 0 End) as Prev07
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth + 8 then (ol.qtyreqd) else 0 End) as Prev08
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth + 9 then (ol.qtyreqd) else 0 End) as Prev09


FROM
  Orders o 
  LEFT JOIN 
  Orderln ol 
  ON (ol.orderid = o.orderid)

WHERE
  ol.orderid = o.orderid
  AND o.ordertype IN ('INVOICE', 'INDIRECT', 'WARRANTY')
  AND ol.linetype = 'PART'
  AND ol.shopid = 'MIDSAG'
  AND ol.partnumber = '50012'  
-- AND EXISTS
--(SELECT i.itemid FROM dbo.ITEM i  WHERE i.itemid = ol.itemid AND i.activecode = 'Y')
GROUP BY
  ol.partnumber

For the moment I've hardcoded the partnumber to test, but it doesn''t matter what number I use, same results. CurMonth only column. Gotta be something simple that I am too blind to see.
Thank you!

Julie
CRXI CE10 / RS2005 Sql DB

 

[gmmastros]

Try changing the + 1, + 2, + 3 to -1, -2, -3.

Declare @CurrentMonth Int
Set @CurrentMonth = Datediff(Month,0,Getdate())

SELECT

  ol.partnumber
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth then (ol.qtyreqd) else 0 End) as CurMonth
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth - 1 then (ol.qtyreqd) else 0 End) as Prev01
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth - 2 then (ol.qtyreqd) else 0 End) as Prev02
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth - 3 then (ol.qtyreqd) else 0 End) as Prev03
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth - 4 then (ol.qtyreqd) else 0 End) as Prev04
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth - 5 then (ol.qtyreqd) else 0 End) as Prev05
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth - 6 then (ol.qtyreqd) else 0 End) as Prev06
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth - 7 then (ol.qtyreqd) else 0 End) as Prev07
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth - 8 then (ol.qtyreqd) else 0 End) as Prev08
, SUM(Case when datediff(Month,0,ol.chgdate)= @CurrentMonth - 9 then (ol.qtyreqd) else 0 End) as Prev09


FROM
  Orders o 
  LEFT JOIN 
  Orderln ol 
  ON (ol.orderid = o.orderid)

WHERE
  ol.orderid = o.orderid
  AND o.ordertype IN ('INVOICE', 'INDIRECT', 'WARRANTY')
  AND ol.linetype = 'PART'
  AND ol.shopid = 'MIDSAG'
  AND ol.partnumber = '50012'  
-- AND EXISTS
--(SELECT i.itemid FROM dbo.ITEM i  WHERE i.itemid = ol.itemid AND i.activecode = 'Y')
GROUP BY
  ol.partnumber

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[RyanEK]

Hi Julie,

If you're getting previous months the case statement should be subtracting from the current month ie. @CurrentMonth - 1.

I think that would solve it?

Ryan

 

[Jcfx]

Thank you both,

That hit me about two hours after I left work with a wonderful Homer Simpson type of DUH!!





Julie
CRXI CE10 / RS2005 Sql DB