CODEWITHSUNDEEP
sqlsql-servercount
chenks
chenks

Reputation: 139

SQL COUNT that includes 0 values

have a query that lists that amount of jobs for each day over a 7 day period. works fine but it doesn't include 0 results.

what do i need to do get have it include 0 results.

select date_received, count(*)
from calls with (nolock)
where contract = 'BLAH'
and date_received between DATEADD(day,-8,GETDATE()) AND GETDATE()-1
group by date_Received
order by date_received

this query produces results for 6 days, the 7th day has 0 calls, but that day to be included.

Upvotes: 0

Views: 502

Answers (1)

Lamak
Lamak

Reputation: 70638

If you have a calendar table, you can do:

SELECT  A.[Date] date_received,
        COUNT(*) N
FROM dbo.Calendar A
LEFT JOIN ( SELECT *
            FROM dbo.calls
            WHERE contract = 'BLAH') B
    ON A.[Date] = B.date_received
WHERE A.[Date] >= DATEADD(DAY,-8,CONVERT(DATE,GETDATE()) 
AND A.[Date] <= DATEADD(DAY,-1,CONVERT(DATE,GETDATE()))
GROUP BY A.[Date]

If not, you can use a CTE for you calendar table:

;WITH Calendar AS
(
    SELECT DATEADD(DAY,-1*number,CONVERT(DATE,GETDATE())) [Date]
    FROM master..spt_values
    WHERE type = 'P'
    AND number BETWEEN 1 AND 8
)
SELECT  A.[Date] date_received,
        COUNT(*) N
FROM Calendar A
LEFT JOIN ( SELECT *
            FROM dbo.calls
            WHERE contract = 'BLAH') B
    ON A.[Date] = B.date_received
GROUP BY A.[Date]

Upvotes: 3

Related Questions