Create date range from 1 date column

Hi experts

We have a table which only contains one date field "ValidFrom" and is missing the field "ValidUntil", so I need to dynically create this column based on the following logic:

Product     Price     ValidFrom
A               $3          2011-01-01
A               $2          2012-01-01
A               $1          2013-01-01

Product     Price     ValidFrom        ValidUntil
A               $1          2011-01-01     2011-12-31
A               $2          2012-01-01     2012-12-31
A               $3          2013-01-01     Today plus 10 days

The dates are just examples, the dates can be any day of the year, there can be more than 3 date ranges and the logic of today plus 10 days has already been implemented. So the logic would always be the ValidUntil is always the day before the next ValidFrom date, except on the last range.

Any ideas
Who is Participating?
PortletPaulConnect With a Mentor Commented:
Use row_number() to generate a relationship from one date to the next.
These results:
    | PRODUCT | PRICE |        VALIDFROM |        VALIDUNTIL |
    |       A |     3 | January, 01 2011 | December, 31 2011 |
    |       A |     2 | January, 01 2012 | December, 31 2012 |
    |       A |     1 | January, 01 2013 |  October, 17 2013 |

Open in new window

Produced by this query:
               , Price
               , ValidFrom
               , row_number() over (partition BY Product ORDER BY ValidFrom ASC) AS rn
          FROM table1
      , t1.Price
      , t1.ValidFrom
      , isnull(dateadd(DAY,-1,t2.validfrom), dateadd(DAY,10,convert(date,getdate()))) AS ValidUntil
LEFT JOIN CTE AS t2 ON t1.Product = T2.Product
                   AND t1.rn = t2.rn-1
      , t1.ValidFrom

Open in new window

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
That's going to involve a lot of code since this isn't 2012 and can't take advantage of 2012's LEAD and LAG functions.

I wrote an article on T-SQL:  How to identify bad dates in a time series that can help you out, although in your case instead of having two columns, once the query RANKS the rows by product by ascending date, you'll need to grab the value from DATEADD(d, -1, (ValidFrom + 1) ) and assign to (row, ValidUntil).

Good luck.
Select Product, Price,
CASE year(ValidFrom)
     when year(getdate()) then
       // your logic to get Today plus 10 days here
       convert(varchar,'12/31/'+CAST(year(ValidFrom) as varchar(4), 101)
end As ValidUntil
from MyTableName
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

riffrackAuthor Commented:
Absolutely great, works like a treat. Thanks!
Excellent - thanks for the grading. Cheers, Paul.
riffrackAuthor Commented:
I received a message from a Senior Admin, that someone wanted to know why their answer wasn't accepted. I cannot see from the message who posted this, yet I would guess this request came from ashok111?

Thank you for providing an answer, however your suggestion would work if I always had annual date ranges. In my question I mentioned that "dates are just examples, the dates can be any day of the year", so your solution would not work for me.

The solution provided by Portlet Paul works for any date.
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
There's an expectation around here that askers respond to all experts that post until a comment is declared the answer.  <my opinion only> especially those that bother to post custom T-SQL. </..>

Thanks for following up.   Good luck with your project.
riffrackAuthor Commented:
Yes, I fully understand that expectation & appreciate every answer. <my opinion only>When various answers are posted within a very short period, then just responding to the most appropriate solution does make sense in my opinion. This also makes it more efficient for any future readers, yet should not discredit any others experts. The solution may have been close, but just not a match for the question.</..>
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Sounds like a plan.  Okay folks, show's over, go about your business.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.