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. 🙂
(source: Wikipedia)