Mr John February 2016

Pivot with datediff

How would I remove the zeros and replace them with null values considering they are really not a zero value?

My working data

id       class     startdate      enddate    
 1        High        1/1/15       2/1/15      
 1         Low        5/1/15       6/1/15      
 1         Mid        6/1/15      6/10/15 
 2     Mid-low        6/1/15      6/10/15

My query below

 select y.id, y.startdate, y.enddate, ISNULL(y.High,'') AS HIGH, ISNULL(y.MID,'') AS MID, ISNULL(y.LOW,'') AS LOW, ISNULL(y.Mid-low,'') AS Mid-low, 
from
(
select id, class, datediff(day, startDate, endDate) as days
from @test
) x
pivot
(
max(days) for class in (high, low, med)
) y

This returns the set value below

  id     High       Mid    low    Mid-low
   1       30         9     30          0
   2        0         0      0         30

Answers


TT. February 2016

Use the NULLIF() function:

NULLIF returns the first expression if the two expressions are not equal. If the expressions are equal, NULLIF returns a null value of the type of the first expression.

select id,NULLIF(high,0) AS high,NULLIF(med,0) AS med,NULLIF(low,0) AS low
from
(
select id, class, datediff(day, startDate, endDate) as days
from @test
) x
pivot
(
max(days) for class in (high, low, med)
) y


Tab Alleman February 2016

Having to post this as an answer to show code. I am unable to reproduce OP's results.

This code:

DECLARE @Table TABLE (
  ColA char(1)
 ,ColB int
 );

 INSERT INTO @Table(ColA,ColB) VALUES ('A',NULL);

SELECT * FROM (
SELECT ColA,ColB
FROM @Table
)x
PIVOT (
SUM(ColB) FOR ColA IN (A,B)  --same result if SUM() is changed to MAX()
) y;

Results in:

A     B
NULL  NULL

So OP is definitely doing something more than his example illustrates to be getting zeros in his/her results.

Post Status

Asked in February 2016
Viewed 2,204 times
Voted 8
Answered 2 times

Search




Leave an answer