Computing sequential ranges of dates

• CanuckBuck

  Hall of Fame

  Points: 3915

  More actions

  July 13, 2009 at 8:41 am

  #212311

  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.

• Joel Ewald

  SSCertifiable

  Points: 6120

  More actions

  July 13, 2009 at 10:40 am

  #1024037

  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

• CanuckBuck

  Hall of Fame

  Points: 3915

  More actions

  July 14, 2009 at 7:17 am

  #1024384

  Yep that works! Thanks for the help.