Adding a further group into a recursive CTE

Question

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:

enter image description here

...how do I get to the following:

enter image description here

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

score 1 · Answer 1 · answered Jun 14 '12 at 10:41

Something like this should do the trick:

declare @curDate datetime, @maxDate datetime
declare @count tinyint

select @curDate = convert(datetime, '20120101', 112), @maxDate = getdate()
select @count = 0

while @curDate < @maxDate
begin
  select @count = count(1) from tablename where Category = 'X' and convert(varchar(8), Date, 112) = convert(varchar(8), @curDate, 112)

  if @count > 0
  begin
    insert into tablename
    select 'X', @curDate, 0
  end

  select @curDate = dateadd(dd, 1, @curDate)
end

+1 aF for a different approach but through trial and error I've sussed it - will add code to OP as you might spot a problem (apart from MaxRecussions) — whytheq, Jun 14 '12 at 11:09

Nikola Markovinović · Accepted Answer · 2012-06-14T11:40:33.080

1

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.

edited Jun 14 '12 at 11:40

answered Jun 14 '12 at 11:07

Nikola Markovinović

18,963
5
46
51

1

+1 for the Fiddle site as I've never seen this before! in the OP - the last query has a cross join in the WITH clause; is this a valid alternative – whytheq Jun 14 '12 at 11:14
@whytheq Please check my revised answer. I had put category in initial CTE generation, as you need it in parent query. I'll check your fiddle and return to you. – Nikola Markovinović Jun 14 '12 at 11:41
@whytheq Yes, Sql Fiddle did this to me too. It seems that variables are generally ok, but not if you try to `select` anything into them. For testing purposes it is enough to enter fixed values, so it is not a deal breaker. – Nikola Markovinović Jun 14 '12 at 11:48
do I get any points for putting the cross join in the correct place before you did?! – whytheq Jun 14 '12 at 11:51
@whytheq - the reason your fiddle wasn't working with those variables is because of the way the query terminator was set. Compare with this : http://sqlfiddle.com/#!3/df6751/17 . The query separator (default of ';') determines which statements get batched together to be executed. Variables only exist for the life of the batch, and since the batch ended immediately after you declared them, they were no longer available in subsequent statements. (BTW - SQL Fiddle is my site). I'll try to find better ways to highlight / explain this on the site for future reference. Thanks! – Jake Feasel Jun 14 '12 at 17:12
@JakeFeasel Thanks Jake, I was also confused about that. Figured out it was related to batch, but didn't know where to go from there. N.B. I'm delighted promotor of your site :-) – Nikola Markovinović Jun 14 '12 at 18:39
@JakeFeasel great site (makes my head hurt even starting to think what you've gone through to get that set up). I had a feeling it was something to do with the semi-colon in ";WITH". I'll be ok next time I use your site – whytheq Jun 15 '12 at 07:21

Adding a further group into a recursive CTE

2 Answers2

Linked