|
Clas
Starting Member
33 Posts |
 Posted - 06/19/2012 : 06:22:44
|
Individuals are moving around in different households.
I want to create events with start and end dates.
One method is to sort and select the next row as end date. The problem is if there are errors then it becomes a real mess.
The following method is better because the result of errors is overlapping spells which can be easily found.
(CalNo = calendarNo, Calendar-table with calendarno and corresponding date)
(EntryExit , 1 = moving in, -1 = moving out)
1.
SELECT IndividualNo
FROM EventSequence
ORDER BY IndividualNo
2. foreach IndividualNo/StartEvent select startdate :
SELECT EventID AS Start_EventID, IndividualNo, HouseholdNo AS Start_HouseholdNo, EntryExit, CalNo AS Start_CalNo, nr
FROM EventSequence
WHERE (IndividualNo = @IndividualNo) AND (EntryExit = 1)
ORDER BY Start_HouseholdNo, Start_CalNo
4. select corresponding enddate :
SELECT TOP (1) IndividualNo, HouseholdNo, EntryExit, CalNo AS End_CalNo
FROM EventSequence
GROUP BY IndividualNo, HouseholdNo, EntryExit, CalNo
HAVING (EntryExit = - 1) AND (IndividualNo = @IndividualNo) AND (HouseholdNo = @StartHouseholdNo) AND (CalNo > @Start_CalNo)
ORDER BY End_CalNo
5. update
UPDATE [EventSequence] SET [END_CalNo] = @END_CalNo, [End_HouseholdNo] = @End_HouseholdNo WHERE (([nr] = @Original_nr))
NEXT IndividualNo/StartEvent .............
create table #EventSequence
(
nr int,
EventID int,
IndividualNo int,
HouseholdNo int,
EntryExit int,
CalNo int,
End_Calno int
)
Insert INTO #EventSequence
values
(1123397,14596,101038003,1038,1,98220,null),
(1123398,14603,101038003,1038,-1,100259,null),
(1123399,16142,101038003,10053,1,100259,null),
(1123400,16187,101038003,10053,-1,100624,null),
(1123401,14598,101038003,1038,1,101355,null),
(1123402,14610,101038003,1038,-1,101657,null),
(1123403,14597,101038004,1038,1,98220,null),
(1123404,14608,101038004,1038,-1,101657,null)
RESULT:
nr,EventID, IndividualNo, HouseholdNo, EntryExit, CalNo. End_Calno
1123397 14596 101038003, 1038 1 98220 100259
1123398 14603 101038003 1038 -1 100259
1123399 16142 101038003 10053 1 100259 100624
1123400 16187 101038003 10053 -1 100624
1123401 14598 101038003 1038 1 101355 101657
1123402 14610 101038003 1038 -1 101657
1123403 14597 101038004 1038 1 98220 101657
1123404 14608 101038004 1038 -1 101657
Thanks in advanced !!
|
|
SQL-query instead of loop in C# on a webpage
