Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

IF NULL then Zero (0) Expand / Collapse
Author
Message
Posted Wednesday, July 28, 2010 7:15 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 4, 2012 9:00 PM
Points: 102, Visits: 431
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

Post #960014
Posted Wednesday, July 28, 2010 7:19 AM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Monday, December 8, 2014 6:29 AM
Points: 422, Visits: 746
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.
Post #960017
Posted Wednesday, July 28, 2010 7:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, December 17, 2014 1:30 AM
Points: 2,386, Visits: 7,622
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)




Not a DBA, just trying to learn

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/



If you litter your database queries with nolock query hints, are you aware of the side effects?
Try reading a few of these links...

(*) Missing rows with nolock
(*) Allocation order scans with nolock
(*) Consistency issues with nolock
(*) Transient Corruption Errors in SQL Server error log caused by nolock
(*) Dirty reads, read errors, reading rows twice and missing rows with nolock


LinkedIn | Blog coming soon (for sufficiently large values of "soon" )!
Post #960023
Posted Wednesday, July 28, 2010 7:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, May 4, 2012 9:00 PM
Points: 102, Visits: 431
Okay Thank you guys
Post #960025
Posted Wednesday, July 28, 2010 7:32 AM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Thursday, December 18, 2014 10:09 PM
Points: 1,602, Visits: 6,673
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)

Post #960029
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse