locked
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

Answers

  • 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, DATEADD(YYYY,-3,GETDATE()), DATEADD(YYYY, -2, GETDATE()), 'NY'
     UNION
      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)
    )
    
    SELECT EmpID
          ,StartDt
          ,ISNULL((SELECT PhDate FROM #EmpPhone B WHERE A.EMPID = B.EmpID AND A.EndDt > GETDATE()),A.EndDt) AS EndDt
          ,Location
          ,'no Phone' AS PhNbr
    FROM #Employee A
    
    UNION
    SELECT A.EMPID
          ,DATEADD(DD,1,B.PhDate)
          ,A.EndDt
          ,A.Location
          ,CAST(B.PhNbr AS VARCHAR(30))
    FROM #Employee A
    LEFT JOIN #EmpPhone B
    ON A.EMPID = B.EMPID
    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