SQL server query not showing daily date result

Question

I have a simple user production report where daily quotas are tracked. The sql returns a weeks worth of data for all users and for each day it tracks their totals. The problem is if they are out for a day and have a zero production for that day then the result query just skips that day and leaves it out. I want to return days even if the table has no entries for the person on that day.

table:
user date 
andy 3/22/10
andy 3/22/10
andy 3/23/10
andy 3/24/10
andy 3/26/10


result:
andy
3/22/10    2
3/23/10    1
3/24/10    1
3/25/10    0
3/26/10    1

So my question is how do I get the query to return that 3/25/10 date with a count of 0.

(current query I'm using):

SELECT A.USUS_ID as Processor, CONVERT(VARCHAR(10),A.CLST_STS_DTM,101) as Date,
COUNT(A.CLCL_ID) as DailyTotal

FROM CMC_CLST_STATUS A
WHERE A.CLST_STS_DTM >= (@Day) AND
DATEADD(d, 5, @Day) > A.CLST_STS_DTM

GROUP BY A.USUS_ID, CONVERT(VARCHAR(10),A.CLST_STS_DTM,101)
ORDER BY A.USUS_ID, CONVERT(VARCHAR(10),A.CLST_STS_DTM,101)

Answer 1

You pretty much need to make a join of your table with a "table" (function generated? not in 2005, though, i think) that contains ALL dates between start and end date. No other way - SQL will not show stuff that is not there.

Answer 2

You need to have data for that date otherwise sql cannot produce it.

The most common way around such a problem is to have a Date Table that contains ALL the dates. Then you can join on this which will allow you to get a zero entry for that date.

You can either keep this table in your DB permanently, or generate it when you run your query. Which way is best depends on usage, etc.

EDIT: A CTE method to create your Date Table

DECLARE @minDate DATETIME;
DECLARE @maxDate DATETIME;

SET @minDate = '2010-03-01 00:00:00.000';
SET @maxDate = '2010-03-10 00:00:00.000';

WITH DateRange ([date])
AS
(
    SELECT @minDate
    UNION ALL
    SELECT
        DATEADD(DAY, 1, [date])
    FROM
        DateRange
    WHERE
        DATEADD(DAY, 1, [date]) <= @maxDate
)

SELECT
    *
FROM
    DateRange

EDIT 2: I haven't checked this through, but it should work with your database.

DECLARE @day DATETIME

SET @day = CAST(FLOOR(CAST(GETDATE() AS float)) AS DATETIME);

WITH DateRange ([Date])
AS
(
    SELECT @day
    UNION ALL
    SELECT
        DATEADD(DAY, 1, [Date])
    FROM
        DateRange
    WHERE
        DATEADD(DAY, 1, [Date]) <= DATEADD(DAY, 5, @day)
)
SELECT
    A.USUS_ID as Processor,
    DateRange.[Date] as Date,
    COUNT(A.CLCL_ID) as DailyTotal 
FROM
    CMC_CLST_STATUS A
INNER JOIN
    DateRange
ON
    A.CLST_STS_DTM >= DateRange.[Date]
AND
    A.CLST_STS_DTM < DATEADD(DAY, 1, DateRange.[Date])
GROUP BY
    A.USUS_ID,
    DateRange.[Date]
ORDER BY
    A.USUS_ID,
    DateRange.[Date]

Answer 3

Is this from within a stored procedure? If so, create a temporary table with the dates of interest, and then use that with suitable joins and aggregation to get the data you are looking for...