2 record to 1

Question

Post reply

2 record to 1

clas

SSC Enthusiast

Points: 112
More actions
August 26, 2008 at 12:39 pm

#191823

I have a table ( #source ) with data:
Order By FamilyID, IndividualID, EventDate
IndividualID FamilyID EventDate EventType StartStopp
123 12 1851-02-03 HouseholdIN 1 (Start event)
123 12 1851-07-05 HouseholdOut -1 (Stop event)
123 12 1858-06-03 HouseholdIN 1
123 12 1860-08-03 HouseholdOut -1
587 12 1853-10-10 HouseholdIN 1
587 12 1855-09-01 HouseholdOut -1
888 52 1832-05-22 HouseholdIN 1
888 52 1835-07-03 HouseholdOut -1
Records with start and stopp ---> 1 record
First Record = Start , next record = Stop, next record = Start etc......
Insert IN #temp
IndividualID FamilyID StartDate StartEvent StoppDate StoppEvent
123 12 1852-02-03 HouseholdIN 1851-07-05 HouseholdOut
123 12 1858-06-03 HouseholdIN 1860-08-03 HouseholdOut
587 12 1853-10-10 HouseholdIN 1855-09-01 HouseholdOut
888 52 1832-05-22 HouseholdIN 1835-07-03 HouseholdOut
Thanks in advance !

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Ron McCullough SSC Guru Points: 63877 More actions · Answer 1

Clas -- I see a lot of data but NO QUESTION [/B]. Also your date value does not appear to be relevant.

If everything seems to be going well, you have obviously overlooked something.

Ron

Please help us, help you -before posting a question please read[/url]
Before posting a performance problem please read[/url]

Ray Herring SSCertifiable Points: 5533 More actions · Answer 2

I see what you are trying to do but there are several problems that stand in your way. The first one is that you don't have a primary key and the rows don't seem to be unique so there is no way to self join the table with consistent results.

It may be that Individual Id, Family Id, Event Date and Event Type make a primary key but that is not obivous in the data you provide.

You might try something like

Select

s1.IndividualId

,s1.FamilyId

,[StartDate] = s1.EventDate

,[StartEvent] = s1.EventType

,[StopEvent] = s2.EventType

,[StopDate] = s2.EventDate

from #Source as s1

Left Join #Source as s2

on s1.FamilyId = s2.FamilyId

and s1.IndividualId = s2.Individualid

and s1.EventDate < s2.EventDate

and s1.EventType <> s2.EventType

I think this will still give you some dupes since the left join can match a stop event against multiple start events. But perhaps this will give you a starting point.

If you really are selecting the data from some other source into a Temp Table then you should reassess the first select and see if you can find a way to produce the desired result in that step.

Good Luck.