Generate the Dates between two Dates in sql using while loop

Question

question

Prameela asked Dec 3, '18

Generate the Dates between two Dates in sql using while loop

Below is my requirement.

I have a base table as below

create table dbo.test_problem
( MonthNumber int, MonthBeginningDate date, MonthEndDate date, YearNumber int, Quarter char(2))

insert into dbo.test_problem
select 201601,'12/23/2015','1/22/2016',2016,'Q1'
union all
select 201602,'1/23/2016','2/22/2016',2016,'Q1'
union all
select 201603,'2/23/2016','3/24/2016',2016,'Q1'

Wanted to insert dates between MonthBeginningDate and MonthEndDate into below table "PDate" column

create table test.test_solution
( MonthNumber int, MonthBeginningDate date, MonthEndDate date, YearNumber int, Quarter char(2),Pdate date)

My output should look like below

MonthNumberMonthBeginningDateMonthEndDateYearNumberQuarterPdate20160112/23/20151/22/20162016Q112/23/201520160112/23/20151/22/20162016Q112/24/201520160112/23/20151/22/20162016Q112/25/201520160112/23/20151/22/20162016Q112/26/201520160112/23/20151/22/20162016Q112/27/201520160112/23/20151/22/20162016Q112/28/201520160112/23/20151/22/20162016Q112/29/201520160112/23/20151/22/20162016Q112/30/201520160112/23/20151/22/20162016Q112/31/201520160112/23/20151/22/20162016Q11/1/201620160112/23/20151/22/20162016Q11/2/201620160112/23/20151/22/20162016Q11/3/201620160112/23/20151/22/20162016Q11/4/2016

Note: I have provided only few "Pdate" values.

For example: MonthBeginningDate -12/23/2015 and MonthEndDate - 01/22/2016

Then "PDate" should be

12/23/2015,12/24/2015,12/25/2015,,12/26/2015,,12/27/2015,12/28/2015,2/29/2015,12/30/2015,,12/31/2015,1/1/2016,1/2/2016,1/3/2016,1/4/2016,
1/5/2016,1/6/2016,1/7/2016,1/8/2016,,1/9/2016,1/10/2016,1/11/2016,1/12/2016,1/13/2016,1/14/2016,1/15/2016,1/16/2016,1/17/2016,1/18/2016,
1/19/2016,1/20/2016,1/21/2016,1/22/2016

I have implemented the above with Cursors and it is working fine in Azure SQL DB. But I need to implement this in Azure SQLDW, which is not supporting cursors.

So wanted to achieve this with While loop.

Anyone help is greatly appreciated.

sql server

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Answer 1 · 2018-12-03T14:01:11Z

Kev Riley answered Dec 3, '18

No need to use any kind of loop, you can do this purely in a set-based approach.

I use a tally table of numbers from 1 to 10000 to generate all the dates between the earliest MonthStartDate and the latest MonthEndDate - these are then all the dates we are interested in.

Simply join this set back to the original data, limited by the month start and end dates, and you have your result data set

;with cte_tally as 
(
select top 10000
    ROW_NUMBER() over(order by sc1.column_id) N
from
    sys.columns sc1,sys.columns sc2
)
, alldates as
(
select 
	N,
	dateadd(day,N-1,(select min(MonthBeginningDate) from test_problem)) as [date]
from cte_tally
where dateadd(day,N-1,(select min(MonthBeginningDate) from test_problem)) 
	between (select min(MonthBeginningDate) from test_problem) and (select max(MonthEndDate) from test_problem)

)


select
	MonthNumber, MonthBeginningDate, MonthEndDate, YearNumber, Quarter, alldates.date as Pdate
from test_problem
join alldates on alldates.date between test_problem.MonthBeginningDate and MonthEndDate

10 |1200

Attachments: Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

question