Recursive date in a sql view

DhanashreePrasun 61 Reputation points
2022-02-10T12:20:59.9+00:00

Hello,

I have a table with the following fields:

LineId FromDate ToDate Amount
1 01.01.2021 10.01.2021 100
2 05.07.2021 07.07.2021 300

and so on

I want a recursive query which will help me create a view with following data:

Lineid FromDate Amount
1 01.01.2021 10
1 02.01.2021 10
1 03.01.2021 10
1 04.01.2021 10
1 05.01.2021 10
1 06.01.2021 10
1 07.01.2021 10
1 08.01.2021 10
1 09.01.2021 10
1 10.01.2021 10
2 05.07.2021 100
2 06.07.2021 100
2 07.07.2021 100

I cant write a stored procedure. It has to be done using a view only.

Appreciate your help in this
Thanks in advance.

Transact-SQL
Transact-SQL
A Microsoft extension to the ANSI SQL language that includes procedural programming, local variables, and various support functions.
4,535 questions
{count} votes

2 answers

Sort by: Most helpful
  1. Naomi 7,361 Reputation points
    2022-02-10T15:30:42.05+00:00

    These types of queries are easily solved if your have a permanent Calendar or Numbers tables in your database. You can, of course, create it on the fly as well, but having it already handy may help.

    Quick Google search on the topic
    https://www.mssqltips.com/sqlservertip/4054/creating-a-date-dimension-or-calendar-table-in-sql-server

    0 comments No comments

  2. Tom Cooper 8,461 Reputation points
    2022-02-10T16:38:08.233+00:00

    As Naomi said, a calendar table is helpful for this kind of query, but if you don't have one, you can create a view using a recursive query. For example

    Set DateFormat DMY;
    
    Create Table SampleTable(LineId int, FromDate date, ToDate date, Amount int);
    Insert SampleTable(LineId, FromDate, ToDate, Amount) Values
    (1, '01.01.2021', '10.01.2021', 100),
    (2, '05.07.2021', '07.07.2021', 300);
    Select * From SampleTable;
    
    go
    Create View SampleView As
    With cte As
    (Select LineId, FromDate, ToDate, Amount/(DateDiff(day, FromDate, ToDate) + 1) As Amount
    From SampleTable
    Union All
    Select LineId, DateAdd(day, 1, FromDate) As FromDate, ToDate, Amount
    From cte
    Where FromDate < ToDate)
    Select LineId, Convert(char(10), FromDate, 104) As FromDate, Amount 
    From cte;
    go
    
    -- Test view
    Select LineId, FromDate, Amount
    From SampleView
    Order By LineId, FromDate;
    
    
    go
    Drop View SampleView;
    go
    Drop Table SampleTable;
    

    Tom

    0 comments No comments