October 5, 2011 at 8:53 am
Hi all,
I am trying to do something quite simple - but am having trouble and not sure if it is a problem with datatypes or just my syntax ;
Heres the deal ;
Simple Sales Table
SHIP_DATE
STOCKCODE
ORDER_QTY
What I am trying to do is to flatten the Sales Table into a view with Weeks from today - so the view would have 55 columns as below ..
StockCode, Q_Arrears, Q_WK_01, Q_WK_02 .... Q_WK_52, Q_FORWARD
I am using
Select
MStockCode
,Sum( CASE( DateDiff( wk, GetDate(), SHIP_DATE ) WHEN BETWEEN -999 AND 0 Then ORDER_QTY ELSE 0 END ) as Q_Arrears
,Sum( CASE( DateDiff( wk, GetDate(), SHIP_DATE ) WHEN = 1 Then ORDER_QTY ELSE 0 END ) as Q_WK_01
Group by StockCode
But when running I get a message
Msg 156, Level 15, State 1, Line 8
Incorrect syntax near the keyword 'BETWEEN'.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
October 5, 2011 at 8:59 am
The case needs to come earlier in the syntax... this works in my prod server :
, SUM(Quantity) AS QuantityAll
, SUM(CASE WHEN [Posting Date] >= DATEADD(M , -3 , GETDATE())
THEN Quantity
ELSE 0
END) AS Quantity3Months
, SUM(CASE WHEN [Posting Date] >= DATEADD(M , -6 , GETDATE())
THEN Quantity
ELSE 0
END) AS Quantity6Months
PS, I'm not totally sure I'd pivot this on the server side. Maybe I'd let SSRS do it (or whatever presentation layer).
October 5, 2011 at 9:19 am
Ninja's_RGR'us (10/5/2011)
The case needs to come earlier in the syntax... this works in my prod server :
, SUM(Quantity) AS QuantityAll, SUM(CASE WHEN [Posting Date] >= DATEADD(M , -3 , GETDATE())
THEN Quantity
ELSE 0
END) AS Quantity3Months
, SUM(CASE WHEN [Posting Date] >= DATEADD(M , -6 , GETDATE())
THEN Quantity
ELSE 0
END) AS Quantity6Months
PS, I'm not totally sure I'd pivot this on the server side. Maybe I'd let SSRS do it (or whatever presentation layer).
Thanks - but
Still not sure - have tried your example - but now get a syntax error " - near Sum"
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
October 5, 2011 at 9:19 am
Please post your new code and the error you get.
October 5, 2011 at 9:25 am
My mistake - moved the WHEN - well duplicated the WHEN ...
Sum( CASE WHEN (DateDiff(wk,GetDate(), MLineShipDate)) = 1 THEN (MShipQty + MBackOrderQty) ELSE 0 END) as OQ_Wk_01,
Sum( CASE WHEN (DateDiff(wk,GetDate(), MLineShipDate)) = 2 THEN (MShipQty + MBackOrderQty) ELSE 0 END) as OQ_Wk_02,
Sum( CASE WHEN (DateDiff(wk,GetDate(), MLineShipDate)) = 3 THEN (MShipQty + MBackOrderQty) ELSE 0 END) as OQ_Wk_03,
Works a treat now - thank you
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
October 5, 2011 at 9:29 am
Cool.
Just a fyi. For big displays like this I find it easier to nullify all the 0s (unless they mean something, even if it's just 2 operations that cancel one another).
When you have 50-60 columns and 100s of lines, seeing 5 000 zeros and only 100 usefull numbers, it just becomes visual noise.
October 5, 2011 at 9:32 am
I totally agree - but the only person that sees the RAW is myself - I will now put this into a crystal report that will be tidy for the users.
Thanks again for pointing me correctly
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply