How to determine sequence for date events linked to same id

[newbie92]

Hi everyone

I have a table with thousands of records listing pt id, start date, end date, event id where records were incorrectly coded with different event ids for events that were actually one continuous event and that should therefore have an aggregate event id.

I need to group them by pt id and create a chronological sequence of events where for the same pt id the start date of the subsequent event equals the end date of the previous event (there could be some that stand alone, there could be pairs or up to a sequence of 5 or 6)

what would I need to write to generate that sequence (1st, 2nd, 3rd) and to then group them into a new field called true event id that groups all the previously different events that we've now established are part of a sequence?

many, many thanks in advance!Cheers

 

[gmmastros]

Can you please post some sample data? This will go a long way towards us helping you.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[gmmastros]

Also post your expected results based on the sample data you provide.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[newbie92]

So the end result I'm looking to get would be something like this ( and the starting point is the same but without the first 2 columns, which I'm trying to derive):

the columns below are: new event id; sequence; pt id; event start date; event end date event id

001 1 2244 01/09/2011 10/09/2011 001
001 2 2244 10/09/2011 13/09/2011 002
001 3 2244 13/09/2011 14/09/2011 003
002 1 2244 15/09/2011 20/09/2011 004
003 1 2165 02/09/2011 05/09/2011 005
003 2 2165 05/09/2011 10/09/2011 006

 

[drlex]

I had a similar problem with contiguous dates in employee attendance, so I've modified my code according to your need for the event id and the dates to be "unbroken".

Assuming you've SQL Server 2005+, here's a test using a combined table expression with some of your data:

create table #temp ([startdate] smalldatetime, [enddate] smalldatetime, eventId int)
Insert into #temp 
select '2011-09-01','2011-09-10',1
Union all select '2011-09-10','2011-09-13', 2
Union all select '2011-09-13','2011-09-14', 3
Union all select '2011-09-15','2011-09-20', 4
Union all select '2011-09-02','2011-09-05', 5
Union all select '2011-09-05','2011-09-10', 6

;with cte as 
(Select #temp.eventId as oldeventId, min(NewEventId) as NewEventId from #temp inner join 
(select eventId,  ROW_NUMBER() OVER (ORDER BY eventId Asc) AS NewEventId
             from   #temp T1
             where not exists 
             ( select startdate from #temp T2
               where T2.startdate = T1.enddate
               and T2.eventId = T1.eventId + 1)) T1
on T1.eventId >= #temp.eventId
group by #temp.eventId
)

select *, ROW_NUMBER() OVER (PARTITION BY NewEventId ORDER BY oldeventId Asc) AS sequence from cte

drop table #temp

soi là, soi carré