How to efficiently loop through using Sql query

Question

As I have From and To date. Something like below,

BeginDate                     End Date

1989-01-01 00:00:00.000       2015-12-31 00:00:00.000

I need to loop through until i get the list of all the Date's between those 2 (Begin & End Date's) records. I need to know what will be the efficient way of doing this. I have no clue on how to do this. Any help to this will be highly appreciated.

Thanks

You can't loop efficiently in SQL - it's not designed for that. Instead you work with sets. What you could do is either have a date table, and use the column values there to restrict the range, or a numbers table and use `DateAdd` (and restrict the range in a similar way). If you have neither of those in your DB, you could generate one on the fly with a CTE. — Bridge, Feb 02 '15 at 16:40
possible duplicate of [Explode Dates Between Dates, check and adjust parameter](http://stackoverflow.com/questions/15497767/explode-dates-between-dates-check-and-adjust-parameter) — Bridge, Feb 02 '15 at 16:41

score 2 · Accepted Answer · answered Feb 02 '15 at 16:55

This method uses a generated numbers table and is probably faster than looping.

DECLARE @BeginDate DATETIME = '19890101';
DECLARE @EndDate DATETIME = '20151231';

WITH 
    E1(N) AS ( SELECT 1 FROM ( VALUES (1),(1),(1),(1),(1),(1),(1),(1),(1),(1)) DT(N)),
    E2(N) AS ( SELECT 1 FROM E1 A, E1 B),
    E4(N) AS ( SELECT 1 FROM E2 A, E2 B),
    Numbers(N) AS
    (
        SELECT ROW_NUMBER() OVER ( ORDER BY ( SELECT NULL)) - 1 FROM E4
    )
SELECT 
    N,
    DATEADD(D, N, @BeginDate) AS TheDate
FROM Numbers
WHERE N <= DATEDIFF(D, @BeginDate, @EndDate)

Giorgi Nakeuri · Answer 2 · 2015-02-02T16:55:13.790

You can do this with WHILE loop:

DECLARE @sdt DATE = '1989-01-01'
DECLARE @edt DATE = '2015-12-31'

WHILE @sdt <= @edt
BEGIN
    PRINT @sdt
    SET @sdt = DATEADD(dd, 1, @sdt )
END

Or with recursive CTE:

DECLARE @sdt DATE = '1989-01-01'
DECLARE @edt DATE = '2015-12-31';

WITH    cte
            AS ( SELECT   @sdt AS sdt
                UNION ALL
                SELECT   DATEADD(dd, 1, sdt)
                FROM     cte
                WHERE    DATEADD(dd, 1, sdt) <= @edt
                )
    SELECT  *
    FROM    cte
OPTION  ( MAXRECURSION 10000 )

There is also tally table method as in link provided by @Bridge

Actually the answer is tally tables. But if there is not a big interval the difference will be insignificant.

score 0 · Answer 3 · answered Feb 02 '15 at 16:44

Something like this should work for your purposes:

DECLARE @sd date = '1989-01-01 00:00:00.000'
, @ed date = '2015-12-31 00:00:00.000'

DECLARE @tt TABLE(
    [Date] date
)


WHILE(@sd <= @ed) --Loop which checks each iteration if the date has reached the end
BEGIN
    INSERT INTO @tt
    SELECT @sd AS Date

    SET @sd = DATEADD(dd,1,@sd) --This willl increment the date so you actually advance the loop
END

SELECT * FROM @tt

How to efficiently loop through using Sql query

3 Answers3