Parent Child Hierarchy in SQL

Question

3.00/5 (2 votes)

See more:

I have the following data

Row	ID		Start_Date	End_Date	Group
1	0123456789	2003-01-01	2003-08-20	60002
2	0123456789	2003-08-20	2003-11-07	60002
3	0123456789	2003-11-07	2003-12-11	60002
4	0123456789	2003-12-11	2004-03-05	60002
5	0123456789	2004-03-05	2004-06-29	60002
6	0123456789	2004-06-29	2005-01-01	60002
7	0123456789	2005-01-01	2005-05-24	83002
8	0123456789	2005-05-24	2005-05-25	83002
9	0123456789	2005-05-25	2005-10-20	83002
10	0123456789	2005-10-20	2006-03-08	83002
11	0123456789	2006-03-08	2006-05-03	83002
12	0123456789	2006-05-03	2006-05-31	83002
13	0123456789	2006-05-31	2006-11-06	83002
14	0123456789	2006-11-06	2006-12-01	83002
15	0123456789	2006-12-01	2007-03-20	733002
16	0123456789	2007-03-20	2007-05-25	733002
17	0123456789	2007-05-25	2007-09-19	733002
18	0123456789	2007-09-19	2007-12-10	733002
19	0123456789	2007-12-10	2008-01-01	733002
20	0123456789	2008-01-01	2008-09-30	60002
21	0123456789	2008-09-30	2008-12-24	60002
22	0123456789	2008-12-24	2009-06-11	60002
23	0123456789	2009-06-11	2009-06-16	60002
24	0123456789	2009-06-16	2009-11-26	60002
25	0123456789	2009-11-26	2010-10-12	60002
26	0123456789	2010-10-12	2011-07-14	60002
27	0123456789	2011-07-14	2011-09-07	60002
28	0123456789	2011-09-07	2011-10-07	60002
29	0123456789	2011-10-07	2011-11-15	60002
30	0123456789	2011-11-15	2012-03-30	60002
31	0123456789	2012-03-30	2012-04-05	60002
32	0123456789	2012-04-05	2012-05-04	60002
33	0123456789	2012-05-04	2012-08-22	60002
34	0123456789	2012-08-22	NULL		60002

What I need is

ID		Start_Date	End_Date	Group
0123456789	2003-01-01	2005-01-01	60002
0123456789	2005-01-01	2006-12-01	83002
0123456789	2006-12-01	2008-01-01	733002
0123456789	2008-01-01	NULL		60002

Logic: 1st row End_Date needs to match 2nd Start_Date, where Group is the same, and so on.

If there is a break in the dates,
ie Row 26 was removed, it would need to return

ID		Start_Date	End_Date	Group
0123456789	2003-01-01	2005-01-01	60002
0123456789	2005-01-01	2006-12-01	83002
0123456789	2006-12-01	2008-01-01	733002
0123456789	2008-01-01	2010-10-12	60002
0123456789	2011-07-14	NULL		60002

How could this be achieve in MS SQL efficiently, as this would save post processing.

Posted 8-Jan-13 19:38pm

strOngHand

Add a Solution

3 solutions

Solution 1

Hi,

SQL

Select ID,Min(Start_date) Start_date,Max(End_Date) End_Date,Group 
From [Your Table Name]
Group BY ID,Group 
Order By Start_date

Posted 8-Jan-13 19:54pm

Suvabrata Roy

Comments

strOngHand 9-Jan-13 2:18am

No quite that simple as the above will produce

6305662629 2003-01-01 2012-08-22 60002
6305662629 2005-01-01 2006-12-01 83002
0123456789 2006-12-01 2008-01-01 733002

Suvabrata Roy 9-Jan-13 2:31am

Ok, I understand let me try

Solution 2

Hi,

I have change your column name Group to GroupId

Try This

SQL

Select FM.ID,FM.Start_Date,FM2.End_Date,FM.GroupId FROM 
(
Select Row_Number() Over(Order By ID,GroupId,IsNull(Start_Date,End_Date)) RowNum,ID, Start_Date, End_Date ,GroupId

FROM (

Select ID, Case When Type='SD' Then Date End Start_Date,
Case When Type='ED' Then Date End End_Date,
GroupId

From (

Select ID,Date,GroupId,Type
From (
Select Distinct ID,Start_Date Date,GroupId,'SD' Type From [Table Name] Where Start_Date is Not Null
Union
Select ID,End_Date Date,GroupId,'ED' Type From [Table Name] Where End_Date is Not Null)
BM

inner join 

(Select ID,Date,GroupId
From (
Select Distinct ID,Start_Date Date,GroupId,'SD' Type From [Table Name] Where Start_Date is Not Null
Union
Select ID,End_Date Date,GroupId,'ED' Type From [Table Name] Where End_Date is Not Null)
BST
Group By ID,Date,GroupId
Having(Count(*)=1)) BS On BM.ID=BS.ID And BM.Date=BS.Date And BS.GroupId=BM.GroupId

) IM

) AM
) FM
Left Join 
(Select Row_Number() Over(Order By ID,GroupId,IsNull(Start_Date,End_Date)) RowNum,ID, Start_Date, End_Date ,GroupId

FROM (

Select ID, Case When Type='SD' Then Date End Start_Date,
Case When Type='ED' Then Date End End_Date,
GroupId

From (

Select ID,Date,GroupId,Type
From (
Select Distinct ID,Start_Date Date,GroupId,'SD' Type From [Table Name] Where Start_Date is Not Null
Union
Select ID,End_Date Date,GroupId,'ED' Type From [Table Name] Where End_Date is Not Null)
BM

inner join 
(Select ID,Date,GroupId
From (
Select Distinct ID,Start_Date Date,GroupId,'SD' Type From [Table Name] Where Start_Date is Not Null
Union
Select ID,End_Date Date,GroupId,'ED' Type From [Table Name] Where End_Date is Not Null)
BST
Group By ID,Date,GroupId
Having(Count(*)=1)) BS On BM.ID=BS.ID And BM.Date=BS.Date And BS.GroupId=BM.GroupId

) IM

) BM ) FM2 ON FM.RowNum+1=FM2.RowNum and FM.Start_Date is Not Null

Union 

Select Distinct ID,Start_Date,End_Date,GroupId From [Table Name] Where End_Date is Null

Posted 8-Jan-13 23:56pm

Suvabrata Roy

Updated 8-Jan-13 23:59pm

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Aarti Meswania · Accepted Answer · 2013-01-09T01:18:00

this way...
please replace underlined part with your tablename

SQL

with t as
(
    select id,start_date, end_date,g,g1 from
    (
       select a.id,a.start_date,a.end_date,a.[group] as g,aa.[group] as g1  
       from TABEL_NAME as a
       left join TABEL_NAME as aa on a.start_date = aa.End_Date
    ) as a
    where g<>g1 or g1 is null
)

select distinct t.id,t.start_date,case when t.start_date > tt.Start_date then null else tt.start_date end as end_date,t.g as [Group]
from t
Left join t as tt on t.g = tt.g1

just to test...

SQL

with a as
(
    select 1 as Row,    '0123456789' as ID, '2003-01-01' as Start_Date, '2003-08-20' as End_Date, 60002 as [Group]
    union all
    select 2    ,'0123456789',  '2003-08-20','2003-11-07'   ,60002 union all
    select 3    ,'0123456789',  '2003-11-07','2003-12-11'   ,60002 union all
    select 4    ,'0123456789',  '2003-12-11','2004-03-05'   ,60002 union all
    select 5    ,'0123456789',  '2004-03-05','2004-06-29'   ,60002 union all
    select 6    ,'0123456789',  '2004-06-29','2005-01-01'   ,60002   union all
    select 7    ,'0123456789',  '2005-01-01','2005-05-24'   ,83002   union all
    select 8    ,'0123456789',  '2005-05-24','2005-05-25'   ,83002 union all
    select 9    ,'0123456789',  '2005-05-25','2005-10-20'   ,83002   union all
    select 10   ,'0123456789',  '2005-10-20','2006-03-08'   ,83002   union all
    select 11   ,'0123456789',  '2006-03-08','2006-05-03'   ,83002 union all
    select 12   ,'0123456789',  '2006-05-03','2006-05-31'   ,83002   union all
    select 13   ,'0123456789',  '2006-05-31','2006-11-06'   ,83002   union all
    select 14   ,'0123456789',  '2006-11-06','2006-12-01'   ,83002  union all
    select 15   ,'0123456789',  '2006-12-01','2007-03-20'   ,733002 union all
    select 16   ,'0123456789',  '2007-03-20','2007-05-25'   ,733002 union all
    select 17   ,'0123456789',  '2007-05-25','2007-09-19'   ,733002 union all
    select 18   ,'0123456789',  '2007-09-19','2007-12-10'   ,733002 union all
    select 19   ,'0123456789',  '2007-12-10','2008-01-01'   ,733002 union all
    select 20   ,'0123456789',  '2008-01-01','2008-09-30'   ,60002 union all
    select 21   ,'0123456789',  '2008-09-30','2008-12-24'   ,60002   union all
    select 22   ,'0123456789',  '2008-12-24','2009-06-11'   ,60002   union all
    select 23   ,'0123456789',  '2009-06-11','2009-06-16'   ,60002 union all
    select 24   ,'0123456789',  '2009-06-16','2009-11-26'   ,60002   union all
    select 25   ,'0123456789',  '2009-11-26','2010-10-12'   ,60002   union all
    select 26   ,'0123456789',  '2010-10-12','2011-07-14'   ,60002 union all
    select 27   ,'0123456789',  '2011-07-14','2011-09-07'   ,60002   union all
    select 28   ,'0123456789',  '2011-09-07','2011-10-07',  60002 union all
    select 29   ,'0123456789',  '2011-10-07','2011-11-15',  60002 union all
    select 30   ,'0123456789',  '2011-11-15','2012-03-30',  60002  union all
    select 31   ,'0123456789',  '2012-03-30','2012-04-05',  60002  union all
    select 32   ,'0123456789',  '2012-04-05','2012-05-04',  60002 union all
    select 33   ,'0123456789',  '2012-05-04','2012-08-22',  60002  union all
    select 34   ,'0123456789',  '2012-08-22',   NULL    ,   60002
)

select distinct t.id,t.start_date,case when t.start_date > tt.Start_date then null else tt.start_date end as end_date,t.g as [Group] from
(
    select id,start_date, end_date,g,g1 from
    (
    select a.id,a.start_date,a.end_date,a.[group] as g,aa.[group] as g1  from a
    left join a  as aa on a.start_date=aa.End_Date
    ) as a
    where g<>g1 or g1 is null
) as t
left join
(
    select id,start_date,end_date,g,g1 from
    (
    select a.id,a.start_date,a.end_date,a.[group] as g,aa.[group] as g1  from a
    left join a  as aa on a.start_date=aa.End_Date
    ) as a
    where g<>g1 or g1 is null
) as tt on t.g=tt.g1

Happy Coding!
:)