AnswerBun.com

SQL Server: Rewrite recursive CTE to substitute for option maxrecursion in a View

I ran into the issue that we cannot use underneath query inside a view, but only in a table. Unfortunately we deal with the situation that we don’t have tables as an option for this project.

I am curious whether someone knows in what direction I should look for to substitute for underneath logic:

What I try to do is create a record for each day within a date range, by doing the following query:

WITH CTE_PerDay AS (
    SELECT 
         TableDateRange.objectId
        ,TableDateRange.amount
        ,TableDateRange.beginDate
        ,COALESCE(TableDateRange.endDate, '2099-12-31') AS endDate
    FROM TableDateRange
    UNION ALL
    SELECT
         CTE_PerDay.objectId
        ,CTE_PerDay.amount
        ,DATEADD(DAY, 1, CTE_PerDay.beginDate) AS beginDate
        ,CTE_PerDay.endDate
    FROM CTE_PerDay 
    WHERE GETDATE() > DATEADD(DAY, 1, CTE_PerDay.beginDate)

)
SELECT * FROM CTE_PerDay
OPTION (maxrecursion 0)

Sample Dataset TableDataRange

ObjectId Amount beginDate endDate
1 500 2020-01-03
2 35 2015-05-31 2019-10-01
3 200 2017-03-15 2020-06-02
CREATE TABLE TableDateRange
(
     ObjectId   varchar(300),
     Amount     int,
     beginDate  date,
     endDate    date
);

INSERT INTO TableDateRange ( ObjectId , Amount , beginDate , endDate )
VALUES
    ('1', 500, '2020-01-03', NULL),
    ('2', 35, '2015-05-31', '2019-10-01'),
    ('3', 200, '2017-03-15', '2020-06-02');

So the query runs fine, however in a view I can’t use the OPTION functionality, and without it I get the error ‘The statement terminated. The maximum recursion 100 has been exhausted before statement completion.’
Any suggestions?

Stack Overflow Asked by titatovenaar on January 8, 2021

2 Answers

2 Answers

You could use a tally: that's a set-based solution, that performs better than recursion when the number of iterations increases - and it is supported in views.

Here is an approach:

select t.objectid, t.amount, dateadd(day, x.n, t.begindate) as dt
from (
    select row_number() over (order by (select null)) - 1
    from (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) a(n)
    cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) b(n)
    cross join (values(0),(0),(0),(0),(0),(0),(0),(0),(0),(0)) c(n)
) x(n)
inner join tabledatarange t
    on dateadd(day, x.n, t.begindate) <= case 
        when enddate <= convert(date, getdate()) then enddate
        else convert(date, getdate())
    end

The tally generates all numbers between 0 and 999 (you can easily expand it by adding cross joins). We use it to "multiply" the rows of the original table and generate the date range.

I attempted to rewrite the part the handles the end date. I understand that you don't want future dates, so that's what the condition in the on clause does.

For this sample data:

ObjectId | Amount | beginDate  | endDate   
-------: | -----: | :--------- | :---------
       1 |    500 | 2020-12-28 | null      
       2 |     35 | 2019-09-26 | 2019-10-01
       3 |    200 | 2020-05-28 | 2020-06-02

The query returns:

objectid | amount | dt        
-------: | -----: | :---------
       1 |    500 | 2020-12-28
       1 |    500 | 2020-12-29
       1 |    500 | 2020-12-30
       1 |    500 | 2020-12-31
       2 |     35 | 2019-09-26
       2 |     35 | 2019-09-27
       2 |     35 | 2019-09-28
       2 |     35 | 2019-09-29
       2 |     35 | 2019-09-30
       2 |     35 | 2019-10-01
       3 |    200 | 2020-05-28
       3 |    200 | 2020-05-29
       3 |    200 | 2020-05-30
       3 |    200 | 2020-05-31
       3 |    200 | 2020-06-01
       3 |    200 | 2020-06-02

Demo on DB Fiddle

Correct answer by GMB on January 8, 2021

The answer to the question you asked is: Add OPTION to the outer query. A view is just a subquery and cannot therefore contain a query level hint.

The question that you didn't ask is: is this actually the best method for a calendar table?

And the answer is: no. Recursing 28854 times in a CTE is very bad for performance.

Much better is either to have a calendar table on disk, or to use Itzik Ben-Gan's tally table (I would probably do this in a Table Valued Function):

CREATE FUNCTION dbo.GetDates
    ( @startDate as DateTime, @endDate as DateTime )
RETURNS TABLE WITH SCHEMABINDING
AS RETURN
(
WITH
  L0 AS ( SELECT 1 AS c FROM (VALUES(1),(1)) AS D(c) ),
  L1 AS ( SELECT 1 AS c FROM L0 AS A CROSS JOIN L0 AS B ),
  L2 AS ( SELECT 1 AS c FROM L1 AS A CROSS JOIN L1 AS B ),
  L3 AS ( SELECT 1 AS c FROM L2 AS A CROSS JOIN L2 AS B ),
  L4 AS ( SELECT 1 AS c FROM L3 AS A CROSS JOIN L3 AS B ),
  L5 AS ( SELECT 1 AS c FROM L4 AS A CROSS JOIN L4 AS B ),
  Nums AS ( SELECT ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS rownum
            FROM L5 )

SELECT TOP (DATEDIFF(day, @startDate, @endDate) + 1)
    DATEADD(day, rownum, '1999-12-31')
FROM Nums
);

Answered by Charlieface on January 8, 2021

Add your own answers!

Related Questions

How do I debug SwiftUI AttributeGraph cycle warnings?

5  Asked on December 30, 2021 by sindre-sorhus

       

RTD client in Python

1  Asked on December 30, 2021 by hunk

     

How to remove mongo specific fields from result (NodeJS, Mongoose)

5  Asked on December 30, 2021 by ararat-harutyunyan

     

Problem with ionic framework How can I solve?

0  Asked on December 30, 2021 by scar-fuentes

         

Random Forest Output

0  Asked on December 30, 2021 by charles-biwer

     

How to shift range of cells to unify values in rows

2  Asked on December 30, 2021 by jozef

 

Ask a Question

Get help from others!

© 2022 AnswerBun.com. All rights reserved.