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

 > 

  • Question

  • Question
    Sign in to vote
    0
    Sign in to vote

    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

  • Question
    Sign in to vote
    0
    Sign in to vote
    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