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?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.