January 7, 2006 at 1:08 pm
I have a table with two date values as result of a SQL command:
DateFrom and DateTo (ordered by DateFrom).
Sometimes the DateTo-value of a record is equal to the DateFrom -value of the next record. And then I would like to knot these records to one new record.
E.g. I have (4 records yyyy-mm-dd):
DateFrom DateTo
2006-04-28 2006-05-07
2006-05-12 2006-05-19
2006-05-19 2006-05-26
2006-06-02 2006-06-09
I would like as result:
DateFrom DateTo
2006-04-28 2006-05-07
2006-05-12 2006-05-26
2006-06-02 2006-06-09
Is this possible? And if yes, how? Any hint is welcome.
January 8, 2006 at 3:15 am
Try this :
select d1.DateFrom as Date_From, d2.DateTo as Date_To
from#dates d1 inner join #dates d2
on d1.DateTo = d2.DateFrom
union all
select d.DateFrom, d.DateTo
from#dates d
wherenot exists (select *
from#dates d_1 inner join #dates d_2
on d_1.DateTo = d_2.DateFrom
whered.DateFrom= d_1.DateTo
ord.DateFrom= d_1.DateFrom)
order by Date_From
January 9, 2006 at 10:03 am
Thank you KH, thank you very much.
I'll try your sql (I will also try to understand it).
What does the #dates mean? I never used a construction like that.
I will look in BOL what it means.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply