SQL server query not showing daily date result
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开发者_如何学Go.
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)
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]
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...
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.
精彩评论