Date, period, spell

• clas

  SSC Enthusiast

  Points: 112

  More actions

  February 12, 2009 at 3:25 pm

  #205511

  I have information about total time (period) and periods of "absence" (from total period).

  How to create new period with "present" periods ?

  -- FamilyMember, only one period / Individual:

  create table #FamilyMembers

  (

  IndividualNo int,

  MemberInFamilyID int,

  MemberStartDate date,

  MemberEndDate date

  )

  insert into #FamilyMembers VALUES ('1234', '32', '1925-01-01' , '1945-05-02')

  insert into #FamilyMembers VALUES ('5678', '54', '1932-09-09' , '1955-04-04')

  -- Periodes when individual have absence from family:

  create table #AbsenceFromfamily

  (

  IndividualNo int,

  MemberInFamilyID int,

  AbsenceStartDate date,

  AbsenceEndDate date

  )

  -- Absence from family:

  insert into #AbsenceFromfamily VALUES ('1234' , '32', '1927-05-05' , '1928-02-02')

  insert into #AbsenceFromfamily VALUES ('1234' , '32', '1939-07-07' , '1942-06-08')

  insert into #AbsenceFromfamily VALUES ('5678' , '54', '1932-09-09' , '1937-02-02')

  insert into #AbsenceFromfamily VALUES ('5678' , '54', '1942-08-08' , '1948-05-02')

  insert into #AbsenceFromfamily VALUES ('5678' , '54', '1952-03-03' , '1955-04-04')

  -- Create New type of periods, present in family ( FamilyMember period minus absence):

  create table #PresentInfamily

  (

  IndividualNo int,

  MemberInFamilyID int,

  PresentStartDate date,

  PresentEndDate date

  )

  -- RESULT

  -- individual is present in family:

  -- 1234 , 32 , 1925-01-01 , 1927-05-04

  -- 1234 , 32 , 1928-02-03 , 1939-07-06

  -- 1234 , 32 , 1942-06-09 , 1945-05-02

  -- 5678 , 54 , 1937-02-03 , 1972-08-07

  -- 5678 , 54 , 1948-05-03 , 1952-03-02

  Thanks

  CA

• Kingston Dhasian

  SSCoach

  Points: 19799

  More actions

  February 13, 2009 at 11:15 am

  #943828

  INSERT INTO #PresentInfamily

  SELECT * FROM #FamilyMembers

  DECLARE@strSQLVARCHAR(MAX)

  SELECT@strSQL = COALESCE( @strSQL, '' )

  + ' INSERT INTO #PresentInfamily( IndividualNo, MemberInFamilyID, PresentStartDate, PresentEndDate ) '

  + ' SELECT IndividualNo, MemberInFamilyID, ''' + CAST( DATEADD( DAY, 1, AbsenceEndDate ) AS VARCHAR(20) ) + ''', PresentEndDate '

  + ' FROM #PresentInfamily '

  + ' WHERE IndividualNo = ' + CAST( IndividualNo AS VARCHAR(10) )

  + ' ANDMemberInFamilyID = ' + CAST( MemberInFamilyID AS VARCHAR(10) )

  + ' AND ''' + CAST( AbsenceStartDate AS VARCHAR(20) ) + ''' BETWEEN PresentStartDate AND PresentEndDate '

  + ' UPDATE #PresentInfamily '

  + ' SETPresentEndDate = ''' + CAST( DATEADD( DAY, -1, AbsenceStartDate ) AS VARCHAR(20) ) + ''' '

  + ' WHERE IndividualNo = ' + CAST( IndividualNo AS VARCHAR(10) )

  + ' ANDMemberInFamilyID = ' + CAST( MemberInFamilyID AS VARCHAR(10) )

  + ' AND ''' + CAST( AbsenceStartDate AS VARCHAR(20) ) + ''' BETWEEN PresentStartDate AND PresentEndDate '

  FROM#AbsenceFromfamily

  EXECUTE ( @strSQL )

  DELETE FROM #PresentInfamily WHERE PresentStartDate > PresentEndDate

  SELECT * FROM #PresentInfamily

  Just check if this works..

  ---

  Kingston Dhasian

  How to post data/code on a forum to get the best help - Jeff Moden
  http://www.sqlservercentral.com/articles/Best+Practices/61537/