best to insert dates between start and end dates and generates new start / end dates? RRS feed

  • Question

  • Hi,

    I have to load a dimension in a data warehouse.

    and I have some tables which contains start and end dates in the source system which will be used as the slow changing dates in the DW.

    But I have to add dates into the existing ranges coming from another table.

    Like this, I have an employee table which contains start / end dates when the employee's address change.

    I have another table which contains the phone number and the date when the phone become valid.

    at the end I have to produce 1 row per address per employee + 1 row when the phone number changes.

    Address Table:

    Emp1 / 2010-01-01 / 2011-05-05 / NY

    Emp1 / 2011-05-06 / 9999-12-31 / Boston

    Phone Table:

    Emp1 / 2012-07/07 / 55555555555

    Expected result:

    Emp1 / 2010-01-01 / 2011-05-05 / NY / no phone #

    Emp1 / 2011-05-06 / 2012-07-07 / Boston / no phone #

    Emp1 / 2012-07-08 / 9999-12-31 / Boston / 555555555

    So I'm looking for a query which can produce the expected result.

    Currently I'm using SQL 2008, but I expect to move to 2012 soon.

    does the window feature of 2012 will be the best way to get the results in 1 single and fast query?

    or is it possible to make it in 2008 without something complicated?

    Tuesday, June 4, 2013 4:05 PM


  • Try this
    CREATE  TABLE #Employee
       EMPID INT
       ,StartDt DATE
       ,EndDt DATE
       ,Location VARCHAR(10)
    CREATE TABLE #EmpPhone
       EmpID INT
       ,PhDate DATE
       ,PhNbr INT
     INSERT INTO #Employee
      SELECT 1, GETDATE(), '99991231', 'Boston'
      INSERT INTO #EmpPhone
      SELECT 1, GETDATE(), 123456789
    CREATE TABLE DimEmployee
       EmpID INT
       ,StartDt Date
       ,EndDt Date
       ,Location VARCHAR(10)
       ,PhoneNbr VARCHAR(30)
          ,ISNULL((SELECT PhDate FROM #EmpPhone B WHERE A.EMPID = B.EmpID AND A.EndDt > GETDATE()),A.EndDt) AS EndDt
          ,'no Phone' AS PhNbr
    FROM #Employee A
          ,CAST(B.PhNbr AS VARCHAR(30))
    FROM #Employee A
    LEFT JOIN #EmpPhone B
    where A.EndDt > GETDATE()

    • Proposed as answer by Kalman Toth Wednesday, June 12, 2013 12:42 AM
    • Marked as answer by Allen Li - MSFT Wednesday, June 12, 2013 9:29 AM
    Tuesday, June 4, 2013 4:33 PM