SQL server query not showing daily date result
- by Andrew Jahn
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)
