IF NULL then Zero (0)

Question

Post reply

IF NULL then Zero (0)

EjSQLme

SSCrazy

Points: 2172
More actions
July 28, 2010 at 7:15 am

#225520

Hi,
Using the sample data below, How would I convert Null to zero (0) if the count is NULL with out creating a temp table?
CREATE TABLE #Table_Temp
(ProdID INT
,StatusID INT
,CreatedDate DATETIME)
INSERT INTO #Table_Temp
Select 2063,1,'2009-10-18' Union
Select 1018,2,'2009-05-10' Union
Select 3013,3,'2010-01-15' Union
Select 1753,1,'2009-09-01' Union
Select 3588,3,'2010-06-07' Union
Select 674,2,'2009-04-06' Union
Select 1037,3,'2009-05-29' Union
Select 1250,3,'2009-06-15' Union
Select 504,3,'2009-01-18' Union
Select 3321,3,'2010-03-24' Union
Select 1754,1,'2009-09-01' Union
Select 528,3,'2009-02-01' Union
Select 3712,3,'2010-07-01' Union
Select 3592,3,'2010-06-07' Union
Select 3446,1,'2010-05-03' Union
Select 3421,3,'2010-04-26' Union
Select 2962,2,'2010-01-09' Union
Select 507,3,'2009-01-23' Union
Select 3369,3,'2010-04-14' Union
Select 3233,3,'2010-03-10'
select * from #Table_Temp order by CreatedDate
select
'ActiveProd' = (select count(*)
from #Table_Temp TT1
where StatusID = 1
and dateadd(month, datediff(month, 0, tt1.CreatedDate),0) = dateadd(month, datediff(month, 0, tt.CreatedDate),0)
group by dateadd(month, datediff(month, 0, CreatedDate),0)),
'InactiveProd' = (select count(*)
from #Table_Temp TT1
where StatusID = 2
and dateadd(month, datediff(month, 0, tt1.CreatedDate),0) = dateadd(month, datediff(month, 0, tt.CreatedDate),0)
group by dateadd(month, datediff(month, 0, CreatedDate),0)),
'DiscontinutedProd' = (select count(*)
from #Table_Temp TT1
where StatusID = 3
and dateadd(month, datediff(month, 0, tt1.CreatedDate),0) = dateadd(month, datediff(month, 0, tt.CreatedDate),0)
group by dateadd(month, datediff(month, 0, CreatedDate),0)),
(convert(varchar(4),dateadd(month, datediff(month, 0, CreatedDate),0),100) + convert(varchar(4),year(dateadd(month, datediff(month, 0, CreatedDate),0)))) as ProdCountDT
from #Table_Temp TT
where dateadd(month, datediff(month, 0, CreatedDate),0) between getdate() - 365 and getdate()
group by dateadd(month, datediff(month, 0, CreatedDate),0)
order by dateadd(month, datediff(month, 0, CreatedDate),0)
drop table #Table_Temp

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

jvanderberg SSCertifiable Points: 6762 More actions · Answer 1

ISNULL() does exactly that:

ISNULL([Field],0)

This returns 0 if [Field] is NULL, otherwise it will return [Field].

But I prefer COALESCE() because it is a little more flexible:

COALESCE([Field],0)

The above code does exactly the same thing as ISNULL(). But COALESCE() can do a little bit more:

COALESCE([Field1],[Field2],[Field3],...,0)

COALESCE() scans through each parameter in order, returning the first non-null one. In this case I added 0 at the end to catch an situation where all of the fields are NULL.

Cadavre SSC-Forever Points: 41690 More actions · Answer 2

Isnull is your friend 🙂

SELECT 'ActiveProd' = Isnull((SELECT COUNT(*)

FROM #table_temp tt1

WHERE statusid = 1

AND Dateadd(MONTH, Datediff(MONTH, 0,

tt1.createddate), 0) =

Dateadd(MONTH, Datediff(MONTH, 0,

tt.createddate), 0)

GROUP BY Dateadd(MONTH, Datediff(MONTH, 0,

createddate),

0)), 0),

'InactiveProd' = Isnull((SELECT COUNT(*)

FROM #table_temp tt1

WHERE statusid = 2

AND Dateadd(MONTH, Datediff(MONTH, 0,

tt1.createddate), 0) =

Dateadd(MONTH, Datediff(MONTH, 0,

tt.createddate), 0)

GROUP BY Dateadd(MONTH, Datediff(MONTH, 0,

createddate

), 0)), 0),

'DiscontinutedProd' = Isnull((SELECT COUNT(*)

FROM #table_temp tt1

WHERE statusid = 3

AND Dateadd(MONTH, Datediff(MONTH, 0

,

tt1.createddate), 0) =

Dateadd(MONTH, Datediff(MONTH, 0,

tt.createddate), 0)

GROUP BY Dateadd(MONTH, Datediff(MONTH, 0,

createddate), 0)), 0),

( CONVERT(VARCHAR(4), Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0), 100) +

CONVERT(VARCHAR(4), YEAR(Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0)))

) AS prodcountdt

FROM #table_temp tt

WHERE Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0) BETWEEN

Getdate() - 365 AND Getdate()

GROUP BY Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0)

ORDER BY Dateadd(MONTH, Datediff(MONTH, 0, createddate), 0)

Forever trying to learn
My blog - http://www.cadavre.co.uk/
For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

EjSQLme SSCrazy Points: 2172 More actions · Answer 3

EjSQLme

SSCrazy

Points: 2172

July 28, 2010 at 7:29 am

#1199225

Okay Thank you guys

matzer SSCrazy Eights Points: 9727 More actions · Answer 4

And to simplify things ...

select

'ActiveProd' = COUNT(CASE WHEN StatusID = 1 THEN 1 END),

'InactiveProd' = COUNT(CASE WHEN StatusID = 2 THEN 1 END),

'DiscontinutedProd' = COUNT(CASE WHEN StatusID = 3 THEN 1 END),

(convert(varchar(4),dateadd(month, datediff(month, 0, CreatedDate),0),100) + convert(varchar(4),year(dateadd(month, datediff(month, 0, CreatedDate),0)))) as ProdCountDT

from #Table_Temp TT

where dateadd(month, datediff(month, 0, CreatedDate),0) between getdate() - 365 and getdate()

group by dateadd(month, datediff(month, 0, CreatedDate),0)

order by dateadd(month, datediff(month, 0, CreatedDate),0)

mimi 41746 Valued Member Points: 57 More actions · Answer 5

select
'ActiveProd' = COUNT(CASE WHEN StatusID = 1 THEN 1 END),
'InactiveProd' = COUNT(CASE WHEN StatusID = 2 THEN 1 END),
'DiscontinutedProd' = COUNT(CASE WHEN StatusID = 3 THEN 1 END),
(convert(varchar(4),dateadd(month, datediff(month, 0, CreatedDate),0),100) + convert(varchar(4),year(dateadd(month, datediff(month, 0, CreatedDate),0)))) as ProdCountDT
from #Table_Temp TT
where dateadd(month, datediff(month, 0, CreatedDate),0) between getdate() - 365 and getdate()
group by dateadd(month, datediff(month, 0, CreatedDate),0)
order by dateadd(month, datediff(month, 0, CreatedDate),0)

Luis Cazares SSC Guru Points: 183698 More actions · Answer 6

To simplify even more:
SELECT ActiveProd = COUNT( CASE WHEN StatusID = 1 THEN ProdID END), InactiveProd = COUNT( CASE WHEN StatusID = 2 THEN ProdID END), DiscontinutedProd = COUNT( CASE WHEN StatusID = 3 THEN ProdID END), ProdCountDT = RIGHT( CONVERT(varchar(11),DATEADD(MM, DATEDIFF(MM, 0, CreatedDate),0), 106),8) FROM #Table_Temp TT --WHERE dateadd(month, datediff(month, 0, CreatedDate),0) BETWEEN getdate() - 365 and getdate() GROUP BY DATEDIFF(MM, 0, CreatedDate) ORDER BY ProdCountDT;

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2