How to Efficiently Generate a Sequence of Dates within a Given Range?

Generate date sequence within the specified range

Question:

How to efficiently get all dates in a specified range and store them in a cursor?

Solution:

Use date table

Date tables provide a simple and straightforward way to retrieve dates. If available, use a query like:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  Date
FROM    dbo.Calendar
WHERE   Date >= @MinDate
AND     Date < @MaxDate;

Dynamicly generate sequence

If you don’t have a date table, you can use the following query to generate a date sequence:

DECLARE @MinDate DATE = '20140101',
        @MaxDate DATE = '20140106';

SELECT  TOP (DATEDIFF(DAY, @MinDate, @MaxDate) + 1)
        Date = DATEADD(DAY, ROW_NUMBER() OVER(ORDER BY a.object_id) - 1, @MinDate)
FROM    sys.all_objects a
        CROSS JOIN sys.all_objects b;

Avoid using cursors

To improve performance, consider using a collection-based alternative in place of cursors. For example, to retrieve quantities for a specific item and date:

DECLARE @ToDate DATE = '20140428';

SELECT  TOP 1 date, it_cd, qty 
FROM    T
WHERE   it_cd = 'i-1'
AND     Date <= @ToDate
ORDER BY Date DESC;

The above is the detailed content of How to Efficiently Generate a Sequence of Dates within a Given Range?. For more information, please follow other related articles on the PHP Chinese website!