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 Quantity6MonthsPS, 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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy