# 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

• 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
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
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 Monday, October 28, 2013 6:50 PM
Sunday, October 27, 2013 10:47 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
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
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 .

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

### 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

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
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
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 .

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
• 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.

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
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
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 Monday, October 28, 2013 6:50 PM
Sunday, October 27, 2013 10:47 AM
• 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