问题描述
参考这个 .新的Sql Fiddle.
Referring back to this SO post
If there is a Grouping category "Category" which, for simplicity's sake, can be either X or Y - is it a trivial matter amending this script so that it will add in the missing dates for each of the categories ?
I assume the category will need adding into the CTE?
In other words if I have the following initial table:
...how do I get to the following:
Will upload my attempt shortly
I've called the initial table #x. I'm hoping to adapt a recursive CTE query like the following to include the field Category:
DECLARE @MinDate DATETIME; SET @MinDate = (SELECT Min(DATE) FROM #x) DECLARE @MaxDate DATETIME; SET @MaxDate = (SELECT Max(DATE) FROM #x) ;WITH times AS ( SELECT @MinDate dt , 1 depth UNION ALL SELECT DATEADD(d, depth, @MinDate) dt , 1 + depth as depth FROM times WHERE DATEADD(d, depth, @MinDate) <= @MaxDate ) SELECT * FROM TIMES t LEFT OUTER JOIN #X x ON t.dt = x.Date
Ok - I've tied including a CROSS JOIN but it expands things incorrectly:
SELECT DISTINCT Category INTO #Cat FROM #x DECLARE @MinDate DATETIME; SET @MinDate = (SELECT Min(DATE) FROM #x) DECLARE @MaxDate DATETIME; SET @MaxDate = (SELECT Max(DATE) FROM #x) ;WITH times AS ( SELECT Category , @MinDate dt , 1 depth FROM #Cat UNION ALL SELECT c.Category , DATEADD(d, depth, @MinDate) dt , 1 + depth as depth FROM times t CROSS JOIN #Cat c --ON c.Category IS NOT NULL WHERE DATEADD(d, depth, @MinDate) <= @MaxDate ) SELECT * FROM TIMES
This seems to have worked ok:
SELECT DISTINCT Category INTO #Cat FROM #x DECLARE @MinDate DATETIME; SET @MinDate = (SELECT Min(DATE) FROM #x) DECLARE @MaxDate DATETIME; SET @MaxDate = (SELECT Max(DATE) FROM #x) ;WITH times AS ( SELECT Category , @MinDate dt , 1 depth FROM #Cat UNION ALL SELECT Category , DATEADD(d, depth, @MinDate) dt , 1 + depth as depth FROM times t WHERE DATEADD(d, depth, @MinDate) <= @MaxDate ) SELECT * FROM TIMES
Here is a solution without a calendar table (which is a must in production). You might have date range in variables, or you might go for min() and max() from the_table.
EDIT: shorter version incorporating categories into date range generation
declare @startdate datetime = '2012-1-1' declare @enddate datetime = '2012-1-5' ; with dates([date], category) as ( select distinct @startdate, category from the_table union all select dateadd (day, 1, [date]), category from dates where [date] < @enddate ) select dates.date, dates.category, isnull(the_table.amount, 0) Amount from dates left join the_table on dates.date = the_table.date and dates.category = the_table.category order by dates.category, dates.date option (maxrecursion 0)
There is live test @ Sql Fiddle. New Sql Fiddle.