CODEWITHSUNDEEP
sql-server
MasterOfStupidQuestions
MasterOfStupidQuestions

Reputation: 925

Show 0 if null in query

I am running a sql query that is omitting the day if the return count is 0. I want my query to return the day and a 0 count if the the count is 0. Snare I have is that if 0 were sold for the day, the day is omitted from my return results.

SELECT ISNULL([day],0) As [day], COUNT(ISNULL(Sold,0)) As [Sold]
FROM productionInfo

Upvotes: 0

Views: 118

Answers (2)

Jeff Hornby
Jeff Hornby

Reputation: 13640

This will give you a dates table:

CREATE FUNCTION dbo.DatesTable (@startDate DATETIME, @endDate DATETIME) 
RETURNS @retTable TABLE (DateValue DATETIME)
AS
BEGIN
    DECLARE @currentDate DATETIME

    SET @currentDate = @startDate


    WHILE (DATEDIFF(dd, @currentDate, @endDate) >= 0)
    BEGIN
        INSERT INTO @retTable VALUES (@currentDate)

        SET @currentDate = DATEADD(dd, 1, @currentDate)
    END

    RETURN
END

Then your query will look like:

SELECT dt.DateValue AS [day], COUNT(Sold) AS [Sold] 
FROM dbo.DatesTable('2-1-2014', '2-10-2014') dt
    LEFT JOIN productionInfo pi ON pi.day = dt.DateValue
GROUP BY dt.DateValue

Upvotes: 0

mikurski
mikurski

Reputation: 1363

You're drawing information from a single table, productionInfo. If productionInfo has no rows with that date information (because there are no widgets sold on that date), how does it know what dates to use?

You might want to look at using a Numbers Table to get a row for each day of the month/year, then join that to productionInfo so you have a day value available, even if there was no production that day.

Upvotes: 2

Related Questions