February 17, 2005 at 5:41 pm
I have a table which looks like this:
EffectiveDate Code1 Code2
20041201 0012 0001
20050101 0013 0001
20050201 0013 0002
Either a change in Code1 or Code2 will trigger a new record with a new
effective date.
I need to transform the above table into a table which looks like this:
Startdate EndDate Code1 Code2
20041201 20041231 0012 0001
20050101 20050131 0013 0001
20050201 0 0013 0002
The end date of any record will be 1 day prior to the startdate of the next
record or 0 if it is the last change. Any help?
February 17, 2005 at 6:04 pm
if you know that the next date is always one month from that date then you can use DATEADD function to add 1 month.
If not and you just need to join to next row, first create a temp table like so:
SELECT IDENTITY( INT, 1, 1 ) as id
, w.*
INTO #temp
FROM ( SELECT TOP 100 PERCENT *
FROM tblname
ORDER BY datecol
) w
then self join it using id:
select a.datecol, dateadd(dd, -1, b.datecol ), a.col2, a.col3...
INTO newtable
FROM #temp a
JOIN #temp b ON a.id = b.id - 1
You'll need to make it an outer join if you want to include the last row and wrap an isnull around the dateadd.
If your date columns are VARCHAR and not date you'll need to wrap converts around them to use the date add and then convert it back to varchar.
This is a onetime process though, and this is the fastest executing. Another method would be to do a join between the table and a subselect which gets min value of next highest date for each row...
February 17, 2005 at 6:25 pm
Careful! SELECT INTO a #temp table with an IDENTITY does not guarantee the ID's will be ordered in the sequence you expect.
February 17, 2005 at 6:48 pm
You should be able to do this in one statement, e.g.:
Select a.EffectiveDate As StartDate,
(Select Min(x.EffectiveDate) FROM MyTable x WHERE x.EffectiveDate > a.EffectiveDate) As EndDate, a.Code1, a.Code2
FROM MyTable a
Obviously, to create a new table this way, you would just include the "INSERT INTO NewTable " (or equivalent) statement.
February 18, 2005 at 4:56 am
Hai,
Please try the follwoing statement
Select a.EffectiveDate As StartDate,
(select top 1 replace(convert(varchar(10),dateadd(dd,-1,EffectiveDate),126),'-','')
from xdate x WHERE x.EffectiveDate > a.EffectiveDate order by EffectiveDate) as EndDate,Code1, Code2
from xdate a
You need to substitute the null for a zero
Cheers
Helen
--------------------------------
Are you a born again. He is Jehova Jirah unto me
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply