question

stevearcher avatar image
stevearcher asked

Loop to populate TEMP able

I need a while loop SQL code that will build a table based on the follow. I have a current table that has the following three fields (CustomerNum, StartDate, EndDate) **example data** 1234, 1/20/2014, 12/31/2020 9876, 2/6/2011, 10/15/2020 I need it loop each CustomerNum and load a table with a year for each CustomerNum until the Enddate is reached. So example of the results would be 1234,1/20/2015 1234,1/20/2016 1234,1/20/2017 1234,1/20/2018 1234,1/20/2019 1234,1/20/2020 9876,2/6/2012 9876,2/6/2013 9876,2/6/2014 .... Hope that I explained it well - thanks
looping
10 |1200 characters needed characters left characters exceeded

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

1 Answer

· Write an Answer
Kev Riley avatar image
Kev Riley answered
This is a good use for a tally table (derived here as a cte) declare @YourTable table (CustomerNum int, StartDate datetime, EndDate datetime) insert into @YourTable select 1234, '20 Jan 2014', '31 dec 2020' insert into @YourTable select 9876, '6 Feb 2011', '15 Oct 2020' ;with cte_tally as ( select top 100 ROW_NUMBER() over(order by sc1.object_id) N from sys.columns sc1, sys.columns sc2 ) select CustomerNum, dateadd(year, N, StartDate) as NewDate from @YourTable join cte_tally on dateadd(year, N, StartDate)
10 |1200 characters needed characters left characters exceeded

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

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

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