September 20, 2018 at 1:34 pm
I read all of the posts on this site and can't figure out how to translate that information to my code.
I found out real fast that I can't do ISNULL(XX,0) in my Select when I pivot.
Ideas? Thanks Here is my code -
SELECT
*
From
(SELECT
ISNULL(Line_item_Duty,0) as [DUTY],
[Supplier] as [SUPPLIER],
Datepart(MM,Entry_Summary_Date) as [MONTH]
from [ADHOC].[ATS_ESH] H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.trans_Sk
WHERE
H.Importer = 'XXXXX'
AND Entry_Summary_Date > '9/1/2017')
as Data
PIVOT
(
Sum([DUTY])
FOR
[Month]
IN ("01","02","03","04","05","06","07","08","09","10","11","12")
) AS JEFF
order by [01] Desc
September 20, 2018 at 1:53 pm
jeffshelix - Thursday, September 20, 2018 1:34 PMI read all of the posts on this site and can't figure out how to translate that information to my code.
I found out real fast that I can't do ISNULL(XX,0) in my Select when I pivot.
Ideas? Thanks Here is my code -
SELECT
*
From
(SELECT
ISNULL(Line_item_Duty,0) as [DUTY],
[Supplier] as [SUPPLIER],
Datepart(MM,Entry_Summary_Date) as [MONTH]
from [ADHOC].[ATS_ESH] H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.trans_Sk
WHERE
H.Importer = 'XXXXX'
AND Entry_Summary_Date > '9/1/2017')
as Data
PIVOT
(
Sum([DUTY])
FOR
[Month]
IN ("01","02","03","04","05","06","07","08","09","10","11","12")
) AS JEFF
order by [01] Desc
CASE WHEN Line_item_Duty IS NULL THEN 0 ELSE Line_item_Duty END???
Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/
September 20, 2018 at 2:10 pm
Sorry, Case didn't work for me. Same result.
September 20, 2018 at 2:15 pm
This code from another site (https://www.experts-exchange.com, to give proper credit) got me this far.
But now i get an error , invalid Column Name "Line Item Duty" on Line 3. That really confuses me.
Thanks again
USE GTM_ODS
SELECT
Line_item_Duty,
[Supplier] as [SUPPLIER],
isnull([01], 0) as [Jan],
isnull([02], 0) as [Feb],
isnull([03], 0) as [Mar],
isnull([04], 0) as [Apr],
isnull([05], 0) as [May],
isnull([06], 0) as [Jun],
isnull([07], 0) as [July],
isnull([08], 0) as [Aug],
isnull([09], 0) as [Sep],
isnull([10], 0) as [Oct],
isnull([11], 0) as [Nov],
isnull([12], 0) as [Dec]
From
(SELECT
L.Line_item_Duty,
[Supplier] as [SUPPLIER],
DATEPART(MM,Entry_Summary_Date) as [MONTH]
from [ADHOC].[ATS_ESH] H
INNER JOIN ADHOC.ATS_ESL L
ON H.TRANS_SK = L.trans_Sk
WHERE
H.Importer = 'XXXXX'
AND Entry_Summary_Date > '9/1/2017')
as Data
PIVOT
(
Sum([Line_Item_Duty])
FOR
[Month]
IN ("01","02","03","04","05","06","07","08","09","10","11","12")
) AS JEFF
order by [01] Desc
September 20, 2018 at 3:51 pm
Solved, i just removed line 3! and now i get 0's instead of Null
thanks all
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply