CODEWITHSUNDEEP
sqlsql-serverpivotcaseisnull
Preshma Linet Pereira
Preshma Linet Pereira

Reputation: 350

Sql ISNULL condition in Sql Pivot and Sql case

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

Upvotes: 3

Views: 297

Answers (2)

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239646

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.

Upvotes: 3

Devart
Devart

Reputation: 121902

SELECT Id
     , [Date]
     , Name
     , [Current] = SUM(CASE WHEN val <= 0  THEN Amount ELSE 0 END)
     , Due30 = SUM(CASE WHEN val < 30 THEN Amount ELSE 0 END)
     , Due60 = SUM(CASE WHEN val < 60  THEN Amount ELSE 0 END)
     , Due90 = SUM(CASE WHEN val >= 60  THEN Amount ELSE 0 END)
FROM dbo.[Statement] t
CROSS APPLY (
    SELECT val = DATEDIFF(DAY, [Date], GETDATE())
) s
WHERE Amount <> 0
GROUP BY Id, [Date], Name

Upvotes: 0

Related Questions