Date/Period - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Date/Period

Author

Topic

Clas
Starting Member

33 Posts

Posted - 2011-03-02 : 07:22:12

In one family, there must be a "family head". If this person dies, a new head must be created.
Is there a good way to do this?

Individuals in same FamilyNo, Lowest RoleNo is head of family.

SOURCE:
Indiv MinDate MaxDate FamilyNo RoleNo
123 1820-01-01 1840-07-01 3 1 (husband)
456 1820-01-01 1845-01-01 3 2 (wife)
789 1825-08-01 1845-01-01 3 3 (child first)

321 1840-01-01 1842-05-05 4 1 (husband)
654 1840-01-01 1866-07-15 4 2 (wife)
987 1841-05-05 1865-01-01 4 3 (first child)
999 1842-10-10 1872-02-02 4 4 (second child)

RESULT:
FamilyHead StartDate EndDate FamilyNo
123 1820-01-01 1840-07-01 3
456 1840-07-01 1845-01-01 3

321 1840-01-01 1842-05-05 4
654 1842-05-05 1866-07-15 4
999 1866-07-15 1872-02-02 4 (Indiv 987 first child is not familyhead - is away !)

create table #source
(
Indiv int,
MinDate date,
MaxDate date,
FamilyNo int,
RoleNo int
)

INSERT INTO #SOURCE
SELECT '123','1820-01-01','1840-07-01','3','1'
UNION ALL SELECT '456','1820-01-01','1845-01-01','3','2'
UNION ALL SELECT '789','1825-08-01','1845-01-01','3','3'
UNION ALL SELECT '321','1840-01-01','1842-05-05','4','1'
UNION ALL SELECT '654','1840-01-01','1866-07-15','4','2'
UNION ALL SELECT '987','1841-05-05','1865-01-01','4','3'
UNION ALL SELECT '999','1842-10-10','1872-02-02','4','4'

create table #RESULT
(
FamilyHead int,
StartDate date,
EndDate date,
FamilyNo int
)

Thanks in advance for any help!

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-02 : 10:35:08

If you are using SQL 2005 or higher, the following query should do it for you.

with A as
(
   select
      s.*,
      t.descendantRoleNo,
      row_number() over (partition by FamilyNo order by RoleNo) as SeqNum
   from
      #source s
      outer apply
      ( select top 1 t.RoleNo as descendantRoleNo from #source t 
         where t.MaxDate > s.MaxDate and t.FamilyNo = s.FamilyNo and t.RoleNo > s.RoleNo
      ) t
),
CTE as
(
   select
      s.Indiv as FamilyHead,
      s.MinDate as StartDate,
      s.MaxDate as EndDate,
      s.FamilyNo,
      s.RoleNo,
      s.descendantRoleNo
   from
      A s
   where
      s.seqNum = 1

   union all
   select
      s.Indiv as FamilyHead,
      s.MinDate  t.MaxDate as StartDate,
      s.MaxDate as EndDate,
      s.FamilyNo,
      s.RoleNo,
      s.descendantRoleNo
   from
      A s
      inner join CTE t on t.descendantRoleNo = s.RoleNo and s.FamilyNo = t.FamilyNo
)
insert into #RESULT
select FamilyHead, StartDate, EndDate, FamilyNo from CTE order by FamilyNo, RoleNo

Edit: Now that I think about it, I may be using a hammer to kill a fly. It can be done in much fewer lines of code, which, as they say in text books, "is left as an exercise to the reader" :--)

Clas
Starting Member

33 Posts

Posted - 2011-03-03 : 02:35:48

In this case FamilyHead 456 sholuld have StartDate:1840-07-01 (previous head enddate) , not 1820-01-01
FamilyHead StartDate EndDate FamilyNo
123 1820-01-01 1840-07-01 3
456 1820-01-01 1845-01-01 3

Like this:
FamilyHead StartDate EndDate FamilyNo
123 1820-01-01 1840-07-01 3
456 1840-07-01 1845-01-01 3

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-03-03 : 08:50:54

see edits in my original reply.

Clas
Starting Member

33 Posts

Posted - 2011-03-03 : 09:36:51

Starting with my exercise ':-)
Thanks for your help by the complicated part!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources