CODEWITHSUNDEEP
sql-server
DonMax
DonMax

Reputation: 960

How to efficiently loop through using Sql query

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

Upvotes: 0

Views: 80

Answers (3)

JSR
JSR

Reputation: 6386

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)

Upvotes: 2

Giorgi Nakeuri
Giorgi Nakeuri

Reputation: 35780

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.

Upvotes: 1

Razzle Dazzle
Razzle Dazzle

Reputation: 521

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

Upvotes: 0

Related Questions