December 22, 2010 at 12:46 pm
Hi All,
I have table as follows
FRomdate Todate Rate
2010-01-01 2010-03-31 100
2010-02-10 2010-02-15 150
2010-03-05 2010-03-10 200
i want output as follows
Frodate Todate Rate
2010-01-01 2010-02-09 100
2010-02-10 2010-02-15 150
2010-02-16 2010-03-04 100
2010-03-05 2010-03-10 200
2010-03-11 2010-03-31 100
thanks in advance
December 22, 2010 at 12:50 pm
You'll want to join the table to itself based on a start date being less than the end date. Right?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 22, 2010 at 12:54 pm
yes , sorry for wrong subject actually i mean to say that splitting the date in to multiple rows ...
thanks
December 28, 2010 at 6:21 am
Query the From and To dates into a single column, using a Union statement. I don't know the structure of your table, so can't help much with the details. This will probably be easiest to do in a CTE.
Once you have that, you want to query the main table, and then find the min() of the dates that's greater than each From date. Again, depending on your table structure, there may be more to this than a simple inline query, or may not.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 4 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply