riffrack
asked on
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
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
Select Product, Price,
CASE year(ValidFrom)
when year(getdate()) then
// your logic to get Today plus 10 days here
else
convert(varchar,'12/31/'+C
end As ValidUntil
from MyTableName
CASE year(ValidFrom)
when year(getdate()) then
// your logic to get Today plus 10 days here
else
convert(varchar,'12/31/'+C
end As ValidUntil
from MyTableName
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Absolutely great, works like a treat. Thanks!
Excellent - thanks for the grading. Cheers, Paul.
ASKER
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.
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.
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.
Thanks for following up. Good luck with your project.
ASKER
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.</..>
Sounds like a plan. Okay folks, show's over, go about your business.
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.