I searched for many solutions on SO and elsewhere but couldn't quite understand how to write a query for my problem.
Anyway my query looks like below
SELECT * FROM
(
SELECT Id, Date, Name, Amount,
CASE
WHEN DATEDIFF(DAY,Date,GETDATE()) <=0
THEN 'Current'
WHEN DATEDIFF(DAY,Date,GETDATE()) <30
THEN 'Due30'
WHEN DATEDIFF(DAY,Date,GETDATE()) <60
THEN 'Due60'
ELSE 'Due90'
END AS [Age]
FROM Statement
WHERE (Amount <> 0)
) AS S
PIVOT
(
SUM(Amount)
FOR[Age] IN ([Current],[Due30],[Due60],[Due90])
) P
and the result looks like this
Id Date Name Current Due30 Due60 Due90
----------- ---------- --------------------------------------------
1 2016-04-03 Alan NULL NULL NULL 110.00
2 2016-05-02 TC NULL NULL 30.00 NULL
where should i insert IsNull condition to be able to remove the null in the result and add a zero there.
I tried inserting IsNull in the pivot query but we all know that is not meant to work
You have to add it repetitively in the final SELECT, when you replace the SELECT * (which should only exist in ad-hoc queries or EXISTS tests) with the column list:
SELECT
Id,
Date,
Name,
COALESCE([Current],0) as [Current],
COALESCE(Due30,0) as Due30,
COALESCE(Due60,0) as Due60,
COALESCE(Due90,0) as Due90
FROM
(
SELECT Id, Date, Name, Amount,
CASE
WHEN DATEDIFF(DAY,Date,GETDATE()) <=0
THEN 'Current'
WHEN DATEDIFF(DAY,Date,GETDATE()) <30
THEN 'Due30'
WHEN DATEDIFF(DAY,Date,GETDATE()) <60
THEN 'Due60'
ELSE 'Due90'
END AS [Age]
FROM Statement
WHERE (Amount <> 0)
) AS S
PIVOT
(
SUM(Amount)
FOR[Age] IN ([Current],[Due30],[Due60],[Due90])
) P
I've also used COALESCE since it's generally the preferred option (ANSI standard, extends to more than two arguments, applies normal type precedence rules) instead of ISNULL.