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