February 12, 2009 at 3:25 pm
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
February 13, 2009 at 11:15 am
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..
How to post data/code on a forum to get the best help - Jeff Moden
http://www.sqlservercentral.com/articles/Best+Practices/61537/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply