Post reply

IF NULL then Zero (0)

  • 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

  • July 28, 2010 at 7:19 am

    #1199218

    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.

  • July 28, 2010 at 7:25 am

    #1199223

    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/

  • July 28, 2010 at 7:29 am

    #1199225

    Okay Thank you guys

  • July 28, 2010 at 7:32 am

    #1199228

    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)

  • January 24, 2017 at 5:32 am

    #1924596

    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)

  • January 24, 2017 at 6:26 am

    #1924608

    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

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

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

Cookies on SQLServerCentral

This website stores cookies on your computer.

These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.

To find out more about the cookies we use, see our Privacy Policy