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  SQL-query instead of loop in C# on a webpage

Author  Topic  Clas Starting Member 33 Posts Posted - 2012-06-19 : 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 !!