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 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy