Recursive date in a sql view

Question

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.

Answer 1

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

Answer 2

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