How to get list of sequential dates in MS SQL Transact SQL and cursors alternative

Let’s imagine that we have table where we store some service prices and that this price can vary regarding period of the year.

You would then have something like this in your table:

$50 from January 1st to May 31th
$70 from June 1st to October 31th
$60 from November 1st to December 31th

What we want is to get all daily prices for given date region. So, if we give range from May 15th to 15th June we want to get this:

May 15th $50
May 16th $50
May 17th $50
May 18th $50
May 19th $50
May 20th $50
May 21th $50
May 22th $50
May 23th $50
May 24th $50
May 25th $50
May 26th $50
May 27th $50
May 28th $50
May 29th $50
May 30th $50
May 31th $50
June 1st $70
June 2nd $70
June 3rd $70
June 4th $70
June 5th $70
June 6th $70
June 7th $70
June 8th $70
June 9th $70
June 10th $70
June 11th $70
June 12th $70
June 13th $70
June 14th $70
June 15th $70

In following piece of code is explained how to do it, plus there is a nice bonus how you can avoid using slow cursors for iterating through SQL records.

DECLARE @FromDate datetime, @UntilDate datetime, @Date datetime, @Id int
SET @FromDate = '2014-05-15'
--Trick to make date independant from year (always use the current year)
SET @FromDate = CAST(CAST(YEAR(GETUTCDATE()) AS varchar) + '-' + CAST(MONTH(@FromDate) AS varchar) + '-' + CAST(DAY(@FromDate) AS varchar) AS DATETIME)
SET @UntilDate = '2014-06-15'
--Trick to make date independant from year (always use the current year)
SET @UntilDate = CAST(CAST(YEAR(GETUTCDATE()) AS varchar) + '-' + CAST(MONTH(@UntilDate) AS varchar) + '-' + CAST(DAY(@UntilDate) AS varchar) AS DATETIME)

--Declare your temporary table for sequential date list with price
DECLARE @results TABLE
(
  aId int IDENTITY(1, 1),
  aDate datetime,
  aPrice decimal(15,2)
)

--Fill temporary table with sequential dates
;WITH T(date)
AS
(
  SELECT @FromDate
  UNION ALL
  SELECT DATEADD(day, 1, T.date) FROM T WHERE T.date < @UntilDate
)
INSERT INTO @results (aDate)
SELECT date FROM T OPTION (MAXRECURSION 32767)

--This temp table will be used for iterating through the records (do not use slow cursors)
DECLARE @temp TABLE
(
  bId int,
  bDate datetime
)

INSERT INTO @temp
SELECT  aId,
        aDate
FROM    @results

WHILE EXISTS(SELECT * FROM @temp)
BEGIN
  SELECT TOP 1 @Id = bId FROM @temp
  SELECT @Date = aDate FROM @results WHERE aId = @Id

  --Do you logic here (find specific row record from SQL table using @Date) and get price
  UPDATE @results SET
    aPrice = Price
  FROM YourTable
  WHERE IntervalStart <= @Date AND @Date <= IntervalEnd

  --"Decrease" counter
  DELETE FROM @temp WHERE bId = @Id
END

SELECT   aDate AS [Date],
        aPrice AS Price
FROM    @results

Note 1

If you want to make yearly independate date comparisons in MS SQL then you should create new temporary table where you would copy interval records (with prices) from YourTable and then update these record (IntervalStart and IntervalEnd dates) by changing their year date component to current year (see at the top for the syntax)

Note 2

If @FromDate and @UntilDate have different years, i.e. @FromDate = ‘2013-12-15’ and @UntilDate = ‘2014-01-15’ then, when doing sequential dates (days) you should call the described transforming function (search WITH keywoard) twice: first from @FromDate until the end of the year ‘2013-12-31’ and then from the first day of the year ‘2014-01-01’ until the @UntilDate

Check. Mate. 🙂

chess-samuel-rachevsky

(source: Wikipedia)

You may also like