July 13, 2009 at 8:41 am
I have a table of records showing the beginning and end years that an asset (in this case a mine) is owned by a company. I need to convert that to a beginning and ending date. My challenge is that the end year of one ownership may be the same as the start year of another ownership.
Mine_Number Start_year End_Year Company
0001 1900 1910 Company A
0001 1910 1915 Company B
0001 1915 2000 Company C
0002 1898 1956 Company A
0003 1925 1995 Company B
I need a strategy to convert this to;
Mine_Number Start_year End_Year Company
0001 1900/01/01 1910/06/31 Company A
0001 1910/07/01 1915/06/31 Company B
0001 1915/07/01 2000/12/31 Company C
0002 1898/01/01 1956/12/31 Company A
0003 1925/01/01 1995/12/31 Company B
I've attached a dataset (csv, tab delimited) if you want to give it a whirl.
July 13, 2009 at 10:40 am
here is a quick sample I put together. I could probably come up with something more elegant after I think about it some more.
Depending on the amount of data you might want to use a temp table with indexes.
CREATE TABLE #Temp ( Mine_Number varchar(4), Start_Year int, End_Year int, Company varchar(20) )
INSERT INTO #Temp VALUES ( '0001',1900,1910,'Company A' )
INSERT INTO #Temp VALUES ( '0003',1925,1995,'Company B' )
INSERT INTO #Temp VALUES ( '0001',1910,1915,'Company B' )
INSERT INTO #Temp VALUES ( '0002',1898,1956,'Company A' )
INSERT INTO #Temp VALUES ( '0001',1915,2000,'Company C' )
WITH Temp_CTE ( Mine_Number, Row_Id, Start_Year, End_Year, Company )
AS
( SELECT Mine_Number,
ROW_NUMBER() OVER ( PARTITION BY Mine_Number ORDER BY Mine_Number ASC, Start_Year ASC ) as Row_Id,
Start_Year,
End_Year,
Company
FROM #Temp
)
SELECT Cur.Mine_Number,
CAST(Cur.Start_Year as char(4)) + CASE WHEN Cur.Start_Year = Pri.End_Year THEN '/07/01' ELSE '/01/01' END as StartDate,
CAST(Cur.End_Year as char(4)) + CASE WHEN Cur.End_Year = Nxt.Start_Year THEN '/06/31' ELSE '/12/31' END as EndDate,
Cur.Company
FROM Temp_CTE Cur
LEFT JOIN Temp_CTE Nxt ON Cur.Mine_Number = Nxt.Mine_Number
AND Cur.Row_Id + 1 = Nxt.Row_Id
LEFT JOIN Temp_CTE Pri ON Cur.Mine_Number = Pri.Mine_Number
AND Cur.Row_Id - 1 = Pri.Row_Id
July 14, 2009 at 7:17 am
Yep that works! Thanks for the help.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply