While loop inside select statement

Question

I need to write select statement where in it will iterate through a specific logic while selecting records itself.

Start_Date  End_Date
2011-01-01   2011-04-01

If I have above record then I need to write to output based on number of months difference between 2 dates. (On every iteration need to increment start_date by one month so that difference will be only months)

Final Output:

Start_Date  End_Date  Date_Col
2011-01-01  2011-04-01 2011-01-01
2011-01-01  2011-04-01 2011-02-01
2011-01-01  2011-04-01 2011-03-01
2011-01-01  2011-04-01 2011-04-01

Which dbms are you using? Recursive WITH support? – jarlh Dec 13 '17 at 14:53 — jarlh, Dec 13 '17 at 14:53
In Postgres you can `generate_series()` – Dec 13 '17 at 14:57 — , Dec 13 '17 at 14:57
database is SQL Server – prameela rani Dec 14 '17 at 10:57 — prameela rani, Dec 14 '17 at 10:57

score 0 · Answer 1 · answered Dec 14 '17 at 12:15

Here is a solution

DECLARE @Start_Date DATETIME = '2011-01-01',
        @End_Date DATETIME = '2011-04-01';
WITH DateArray
AS
(SELECT @Start_Date AS DateElement
  UNION ALL
  SELECT DATEADD (month, 1, DateElement) FROM DateArray WHERE DateElement < @End_Date
)
SELECT CONVERT(VARCHAR(10), @Start_Date,120) Start_Date,
       CONVERT(VARCHAR(10), @End_Date,120) End_Date,
       CONVERT(VARCHAR(10), DateElement,120) Date_Col
  FROM DateArray;

Output is

Start_Date  End_Date    Date_Col
2011-01-01  2011-04-01  2011-01-01
2011-01-01  2011-04-01  2011-02-01
2011-01-01  2011-04-01  2011-03-01
2011-01-01  2011-04-01  2011-04-01

score 0 · Answer 2 · answered Dec 14 '17 at 12:34

I would prefer a numbers table solution over a recursive cte. There are a couple of benefits to this approach - the main benefit is performance.

If you don't already have a numbers table, you should probably read Jeff Moden's The "Numbers" or "Tally" Table: What it is and how it replaces a loop, and also this SO post on creating a numbers table in SQL Server.

So, once you created the numbers table, it's a simple join:

SELECT Start_Date, 
       End_Date, 
       DATEADD(MONTH, Number, Start_Date) As Date_Col
FROM YourTable
JOIN Numbers ON Number <= DATEDIFF(MONTH, Start_Date, End_Date)

You can see a live demo on rextester.

While loop inside select statement

2 Answers2