June 17, 2014 at 8:58 am
Hi Guys,
I am using a PIVOT function to obtain the Invoice Values, but they appear in different currencies so need to perform a case function.
But am struggling with the syntax;
This fails a syntax check with
Msg 156, Level 15, State 1, Line 33
Incorrect syntax near the keyword 'Case'.
The script I have is as follows;
SELECT
TrnYear,
[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
FROM
( SELECT
TrnYear, TrnMonth, PostValue
FROM [ApInvoicePay]
Where TrnYear >= Year( GetDate() ) -1 ) p
PIVOT
(
Sum( Case when PostCurrency = 'GBP' then PostValue else PostValue / PostConvRate end )
FOR TrnMonth IN
( [1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS Pvt
GO
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
June 17, 2014 at 9:11 am
Apply your currency rate conversion before PIVOT:
....
(SELECT TrnYear, TrnMonth, CASE WHEN PostCurrency = 'GBP' THEN PostValue ELSE PostValue / PostConvRate END AS PostValue
FROM [ApInvoicePay]
Where TrnYear >= Year( GetDate() ) -1 ) P
...
June 17, 2014 at 9:20 am
Thank you.
Looking past the selection into the PIVOT ! - sometimes look too hard for a solution to a problem that is never there.
________________________________________________________________________________________________
Regards
Steve
SQL 2008 DBA/DBD - MCTS/MCITP
Please don't trust me, test the solutions I give you before using them.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply