none
TSQL Puzzle/help

    Question

  • What is the easiset solution using TSQL(SQL 2012) to solve this?

    Table 1(A Type 2 dimension):

    Id            Group   Start_Date           End_Date

    10           EQ          2012-01-01          2012-01-15

    10           EQ          2012-01-16          2012-04-15

    10           EQ          2012-04-16          2012-07-12

    10           EQ          2012-07-13          9999-12-31

    Table 2:

    Id            Column_Name     CHANGE_VALUE           CHANGE_EFFECTIVE_DATE          CHANGE_EXPIRATION_DATE

    10           Group                   SEQ             2012-03-19                          2012-12-31

    Need to merge the change information in Table2 to Table1 as

    Id            Group   Start_Date           End_Date

    10           EQ         2012-01-01         2012-01-15

    10           EQ         2012-01-16         2012-03-18<--End_Date updated

    10           SEQ        2012-03-19         2012-04-15<--Inserted (Change Effective Row)

    10           SEQ        2012-04-16         2012-07-12<--Group, Start_Date updated

    10           SEQ        2012-04-16         2012-12-31<--Inserted (Change Expiration)

    10           EQ         2014-01-01         9999-12-31<--Group, Start_Date updated

    Friday, October 18, 2013 9:04 PM

Answers

  • Thank you  Allen. It works, but what if the CHANGE_EXPIRATION_DATE in Table 2 is NULL or '9999/12/31'. Could we plug in this scenario as well?

    Hi, we can add filters to get our result:

    declare @table1 table
    (
    Id int,
    [Group] char(5),
    [Start_Date] date,
    End_Date date
    )
    
    insert into @table1(ID,[Group],[Start_Date],End_Date) values(10,'EQ','2012-01-01','2012-01-15'),(10,'EQ','2012-01-16','2012-04-15'),(10,'EQ','2012-04-16','2012-07-12'),(10,'EQ','2012-07-13','9999-12-31');
    
    declare @table2 table
    (
    Id int,
    [Column_Name] char(5),
    [CHANGE_VALUE] char(5),
    [CHANGE_EFFECTIVE_DATE] date,
    CHANGE_EXPIRATION_DATE date
    )
    
    --insert into @table2 (id,[Column_Name],[CHANGE_VALUE],[CHANGE_EFFECTIVE_DATE],[CHANGE_EXPIRATION_DATE]) values(10,'Group','SEQ','2012-03-19','2012-12-31')
    insert into @table2 (id,[Column_Name],[CHANGE_VALUE],[CHANGE_EFFECTIVE_DATE],[CHANGE_EXPIRATION_DATE]) values(10,'Group','SEQ','2012-03-19','9999-12-31')
    --insert into @table2 (id,[Column_Name],[CHANGE_VALUE],[CHANGE_EFFECTIVE_DATE],[CHANGE_EXPIRATION_DATE]) values(10,'Group','SEQ','2012-03-19',NULL)
    
    ; with CTEStartDate as
    (
    select ID,[Group],[Start_Date],ROW_NUMBER() over(order by [Start_Date]) as RowNum
    from (
     select ID,[Group],[Start_Date]
     from @table1
     union
     select ID,NULL, CASE WHEN [CHANGE_EXPIRATION_DATE] <> '9999/12/31' AND [CHANGE_EXPIRATION_DATE] IS NOT NULL THEN 
     DATEADD(day,1,[CHANGE_EXPIRATION_DATE])
     WHEN [CHANGE_EXPIRATION_DATE] = '9999/12/31' OR  [CHANGE_EXPIRATION_DATE] is NULL
     THEN Null
     END AS [CHANGE_EXPIRATION_DATE]
     from @table2
     union 
     select ID,NULL,[CHANGE_EFFECTIVE_DATE]
     from @table2
     ) as temp
     WHERE [Start_Date] IS NOT NULL
    )
    ,CTEEndDate as
    (
     select ID,[Group],End_Date, ROW_NUMBER() over(order by End_Date) as RowNum
     from (select ID,[Group],End_Date
     from @table1
     union
     select ID,NULL,DATEADD(day,-1,[CHANGE_EFFECTIVE_DATE])
     from @table2
     union 
     select ID,Null,CASE WHEN [CHANGE_EXPIRATION_DATE] <> '9999/12/31' AND [CHANGE_EXPIRATION_DATE] IS NOT NULL THEN [CHANGE_EXPIRATION_DATE]
     END
     from @table2
     ) as temp
     WHERE End_Date IS NOT NULL
    )
    ,cte3 as
    (
    select c1.ID,ISNULL(c1.[group],c2.[Group]) as [Group],[Start_Date],end_date
    from CTEStartDate c1
    inner join CTEEndDate c2 
    on c1.id = c2.Id 
    and c1.RowNum = c2.RowNum
    )
    select cte3.ID, ISNULL(t2.CHANGE_VALUE,cte3.[group]) as [group],cte3.[Start_Date],cte3.End_Date
    from cte3 left outer join 
    @table2 t2
    on cte3.ID = t2.id
    and cte3.[Start_Date]>=t2.CHANGE_EFFECTIVE_DATE
    and cte3.End_Date<=t2.[CHANGE_EXPIRATION_DATE]

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    • Marked as answer by harharsar Monday, October 28, 2013 6:50 PM
    Sunday, October 27, 2013 10:47 AM
    Moderator
  • Hi harharsar,

    You can refer to the following codes:

    declare @table1 table
    (
    Id int,
    [Group] char(5),
    [Start_Date] date,
    End_Date date
    )
    
    insert into @table1(ID,[Group],[Start_Date],End_Date) values(10,'EQ','2012-01-01','2012-01-15'),(10,'EQ','2012-01-16','2012-04-15'),(10,'EQ','2012-04-16','2012-07-12'),(10,'EQ','2012-07-13','9999-12-31');
    
    declare @table2 table
    (
    Id int,
    [Column_Name] char(5),
    [CHANGE_VALUE] char(5),
    [CHANGE_EFFECTIVE_DATE] date,
    CHANGE_EXPIRATION_DATE date
    )
    
    insert into @table2 (id,[Column_Name],[CHANGE_VALUE],[CHANGE_EFFECTIVE_DATE],[CHANGE_EXPIRATION_DATE]) values(10,'Group','SEQ','2012-03-19','2012-12-31')
    
    ; with CTEStartDate as
    (
    select ID,[Group],[Start_Date],ROW_NUMBER() over(order by [Start_Date]) as RowNum
    from (
     select ID,[Group],[Start_Date]
     from @table1
     union
     select ID,NULL,DATEADD(day,1,[CHANGE_EXPIRATION_DATE])
     from @table2
     union 
     select ID,NULL,[CHANGE_EFFECTIVE_DATE]
     from @table2
     ) as temp
    )
    ,CTEEndDate as
    (
     select ID,[Group],End_Date, ROW_NUMBER() over(order by End_Date) as RowNum
     from (select ID,[Group],End_Date
     from @table1
     union
     select ID,NULL,DATEADD(day,-1,[CHANGE_EFFECTIVE_DATE])
     from @table2
     union 
     select ID,Null,[CHANGE_EXPIRATION_DATE]
     from @table2
     ) as temp
    )
    ,cte3 as
    (
    select c1.ID,ISNULL(c1.[group],c2.[Group]) as [Group],[Start_Date],end_date
    from CTEStartDate c1
    inner join CTEEndDate c2 
    on c1.id = c2.Id 
    and c1.RowNum = c2.RowNum
    )
    select cte3.ID, ISNULL(t2.CHANGE_VALUE,cte3.[group]) as [group],cte3.[Start_Date],cte3.End_Date
    from cte3 left outer join 
    @table2 t2
    on cte3.ID = t2.id
    and cte3.[Start_Date]>=t2.CHANGE_EFFECTIVE_DATE
    and cte3.End_Date<=t2.[CHANGE_EXPIRATION_DATE]

    Best Regards,
    Allen Li

    If you have any feedback on our support, please click here.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.


    Monday, October 21, 2013 8:08 AM
    Moderator

All replies

  • Hi Logic is not clear..I did not get how highlighted rows formed.

    10           EQ         2012-01-01         2012-01-15

    10           EQ         2012-01-16         2012-03-18<--End_Date updated

    10           SEQ        2012-03-19         2012-04-15<--Inserted (Change Effective Row)

    10           SEQ        2012-04-16         2012-07-12<--Group, Start_Date updated

    10           SEQ        2012-04-16         2012-12-31<--Inserted (Change Expiration)

    10           EQ         2014-01-01         9999-12-31<--Group, Start_Date updated


    Vinay Valeti| If you think my suggestion is useful, please rate it as helpful. If it has helped you to resolve the problem, please Mark it as Answer

    Friday, October 18, 2013 9:39 PM
  • Sorry, I see a typo for dates in the required table. Updated the table below, hope it makes it clear now.

    Id            Group   Start_Date           End_Date

    10           EQ         2012-01-01         2012-01-15

    10           EQ         2012-01-16         2012-03-18<--End_Date updated

    10           SEQ        2012-03-19         2012-04-15<--Inserted (Change Effective Row)

    10           SEQ        2012-04-16         2012-07-12<--Group, Start_Date updated

    10           SEQ        2012-07-13         2012-12-31<--Inserted (Change Expiration)

    10           EQ         2013-01-01         9999-12-31<--Group, Start_Date updated

    More information:

    Table 2 says attribute "Group" should be "SEQ" for the time period "2012-03-19" to "2012-12-31".

    10           SEQ        2012-04-16         2012-07-12<--Group, Start_Date updated(This is the existing entry in the type 2 dimension and this entry had come in past because there were some changes detected in other attributes in the table. But now, for this row, the group needs to be updated as SEQ instead of EQ as per the change information came in Table 2. So this is an update in the historical entry)

    10           SEQ        2012-07-13         2012-12-31<--Inserted (Change Expiration)(This entry came in because the change table says that group should be SEQ only till 2012-12-31 and then it should be changed back to EQ from 2013-01-01.)

    Saturday, October 19, 2013 4:44 AM
  • Hi harharsar,

    You can refer to the following codes:

    declare @table1 table
    (
    Id int,
    [Group] char(5),
    [Start_Date] date,
    End_Date date
    )
    
    insert into @table1(ID,[Group],[Start_Date],End_Date) values(10,'EQ','2012-01-01','2012-01-15'),(10,'EQ','2012-01-16','2012-04-15'),(10,'EQ','2012-04-16','2012-07-12'),(10,'EQ','2012-07-13','9999-12-31');
    
    declare @table2 table
    (
    Id int,
    [Column_Name] char(5),
    [CHANGE_VALUE] char(5),
    [CHANGE_EFFECTIVE_DATE] date,
    CHANGE_EXPIRATION_DATE date
    )
    
    insert into @table2 (id,[Column_Name],[CHANGE_VALUE],[CHANGE_EFFECTIVE_DATE],[CHANGE_EXPIRATION_DATE]) values(10,'Group','SEQ','2012-03-19','2012-12-31')
    
    ; with CTEStartDate as
    (
    select ID,[Group],[Start_Date],ROW_NUMBER() over(order by [Start_Date]) as RowNum
    from (
     select ID,[Group],[Start_Date]
     from @table1
     union
     select ID,NULL,DATEADD(day,1,[CHANGE_EXPIRATION_DATE])
     from @table2
     union 
     select ID,NULL,[CHANGE_EFFECTIVE_DATE]
     from @table2
     ) as temp
    )
    ,CTEEndDate as
    (
     select ID,[Group],End_Date, ROW_NUMBER() over(order by End_Date) as RowNum
     from (select ID,[Group],End_Date
     from @table1
     union
     select ID,NULL,DATEADD(day,-1,[CHANGE_EFFECTIVE_DATE])
     from @table2
     union 
     select ID,Null,[CHANGE_EXPIRATION_DATE]
     from @table2
     ) as temp
    )
    ,cte3 as
    (
    select c1.ID,ISNULL(c1.[group],c2.[Group]) as [Group],[Start_Date],end_date
    from CTEStartDate c1
    inner join CTEEndDate c2 
    on c1.id = c2.Id 
    and c1.RowNum = c2.RowNum
    )
    select cte3.ID, ISNULL(t2.CHANGE_VALUE,cte3.[group]) as [group],cte3.[Start_Date],cte3.End_Date
    from cte3 left outer join 
    @table2 t2
    on cte3.ID = t2.id
    and cte3.[Start_Date]>=t2.CHANGE_EFFECTIVE_DATE
    and cte3.End_Date<=t2.[CHANGE_EXPIRATION_DATE]

    Best Regards,
    Allen Li

    If you have any feedback on our support, please click here.


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.


    Monday, October 21, 2013 8:08 AM
    Moderator
  • Thank you  Allen. It works, but what if the CHANGE_EXPIRATION_DATE in Table 2 is NULL or '9999/12/31'. Could we plug in this scenario as well?
    Monday, October 21, 2013 3:29 PM
  • also you can use T-SQL merge to load scd type 2 dims.

    http://www.made2mentor.com/2013/08/how-to-load-slowly-changing-dimensions-using-t-sql-merge/


    Thanks, hsbal

    Monday, October 21, 2013 4:45 PM
  • Hi hsbal, T-SQL merge won't fit into the scenario. What we are trying to achieve is updates on existing historical records in a Type 2 with solutions for gaps (check for "gaps and islands"). 

    Does anybody have a solution using the new SQL 2012 analytical functions like LEAD() and LAG()?

    Tuesday, October 22, 2013 1:29 PM
  • Thank you  Allen. It works, but what if the CHANGE_EXPIRATION_DATE in Table 2 is NULL or '9999/12/31'. Could we plug in this scenario as well?

    Hi, we can add filters to get our result:

    declare @table1 table
    (
    Id int,
    [Group] char(5),
    [Start_Date] date,
    End_Date date
    )
    
    insert into @table1(ID,[Group],[Start_Date],End_Date) values(10,'EQ','2012-01-01','2012-01-15'),(10,'EQ','2012-01-16','2012-04-15'),(10,'EQ','2012-04-16','2012-07-12'),(10,'EQ','2012-07-13','9999-12-31');
    
    declare @table2 table
    (
    Id int,
    [Column_Name] char(5),
    [CHANGE_VALUE] char(5),
    [CHANGE_EFFECTIVE_DATE] date,
    CHANGE_EXPIRATION_DATE date
    )
    
    --insert into @table2 (id,[Column_Name],[CHANGE_VALUE],[CHANGE_EFFECTIVE_DATE],[CHANGE_EXPIRATION_DATE]) values(10,'Group','SEQ','2012-03-19','2012-12-31')
    insert into @table2 (id,[Column_Name],[CHANGE_VALUE],[CHANGE_EFFECTIVE_DATE],[CHANGE_EXPIRATION_DATE]) values(10,'Group','SEQ','2012-03-19','9999-12-31')
    --insert into @table2 (id,[Column_Name],[CHANGE_VALUE],[CHANGE_EFFECTIVE_DATE],[CHANGE_EXPIRATION_DATE]) values(10,'Group','SEQ','2012-03-19',NULL)
    
    ; with CTEStartDate as
    (
    select ID,[Group],[Start_Date],ROW_NUMBER() over(order by [Start_Date]) as RowNum
    from (
     select ID,[Group],[Start_Date]
     from @table1
     union
     select ID,NULL, CASE WHEN [CHANGE_EXPIRATION_DATE] <> '9999/12/31' AND [CHANGE_EXPIRATION_DATE] IS NOT NULL THEN 
     DATEADD(day,1,[CHANGE_EXPIRATION_DATE])
     WHEN [CHANGE_EXPIRATION_DATE] = '9999/12/31' OR  [CHANGE_EXPIRATION_DATE] is NULL
     THEN Null
     END AS [CHANGE_EXPIRATION_DATE]
     from @table2
     union 
     select ID,NULL,[CHANGE_EFFECTIVE_DATE]
     from @table2
     ) as temp
     WHERE [Start_Date] IS NOT NULL
    )
    ,CTEEndDate as
    (
     select ID,[Group],End_Date, ROW_NUMBER() over(order by End_Date) as RowNum
     from (select ID,[Group],End_Date
     from @table1
     union
     select ID,NULL,DATEADD(day,-1,[CHANGE_EFFECTIVE_DATE])
     from @table2
     union 
     select ID,Null,CASE WHEN [CHANGE_EXPIRATION_DATE] <> '9999/12/31' AND [CHANGE_EXPIRATION_DATE] IS NOT NULL THEN [CHANGE_EXPIRATION_DATE]
     END
     from @table2
     ) as temp
     WHERE End_Date IS NOT NULL
    )
    ,cte3 as
    (
    select c1.ID,ISNULL(c1.[group],c2.[Group]) as [Group],[Start_Date],end_date
    from CTEStartDate c1
    inner join CTEEndDate c2 
    on c1.id = c2.Id 
    and c1.RowNum = c2.RowNum
    )
    select cte3.ID, ISNULL(t2.CHANGE_VALUE,cte3.[group]) as [group],cte3.[Start_Date],cte3.End_Date
    from cte3 left outer join 
    @table2 t2
    on cte3.ID = t2.id
    and cte3.[Start_Date]>=t2.CHANGE_EFFECTIVE_DATE
    and cte3.End_Date<=t2.[CHANGE_EXPIRATION_DATE]

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time.
    Thanks for helping make community forums a great place.

    • Marked as answer by harharsar Monday, October 28, 2013 6:50 PM
    Sunday, October 27, 2013 10:47 AM
    Moderator
  • Allen,

    There is still a minor bug. If we provide a CHANGE_EFFECTIVE_DATE which is already a Start_Date, the gaps are not filling in correct. Any help on this would be highle appreciated.

    TIA!

    Thursday, November 07, 2013 7:26 PM