joined up - SQL Server Forums

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

SQL Server 2000 Forums

Transact-SQL (2000)

joined up

Author

Topic

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-10-01 : 06:38:16

I have two tables with sequential events that are related to each other and to individuals and I want to associate the next event in time that hasn't been associated with another event. So for example I have

tbl_eventA
fld_EventID Person ID fld_EventDate ...
1 a1 20030101
3 a1 20040101
4 a2 19870305
7 a2 19600303

tbl_eventB
fld_EventID Person ID fld_EventDate ...
11 a1 20030201
30 a1 20040102
45 a2 20020325
76 a2 20031203
80 a2 20040405

There will always be a single event B for each event A and the event b will follow the event A in time , i.e. event B is always the first event after event A that is not matched already. so the results I would expect are

fld_EventAID fld_eventBID PersonID fld_EventADate fld_EventBDate
1 11 a1 20030101 20030201
3 30 a1 20040101 20040102
4 45 a2 19870305 20031203
7 76 a2 19600303 20020325

Is a set based solution possible?

Many thanks in advance

steve

To alcohol ! The cause of - and solution to - all of life's problems

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-01 : 06:57:41

[sponsored by JiveTranslator]
mah fust thought would be sump'n likes dat, dig dis:

select t1.*, t2.*
from tbl_eventA t1
inner join tbl_eventB t2 on (t1.PersonId = t2.PersonId) and (t1.fld_EventADate < t2.fld_EventBDate)
where not exists (your match condition)

Go with the flow & have fun! Else fight the flow

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-01 : 07:03:25

select a.fld_EventID, b.fld_EventID, a.fld_EventDate , b.fld_EventDate from tbl_eventA a join tbl_eventB b on a.PersonId=b.PersonId and b.fld_EventDate = (select top 1 fld_EventDate from tbl_eventB where fld_EventDate>a.fld_EventDate order by tbl_eventB)

try

Shurgenz
Yak Posting Veteran

51 Posts

Posted - 2004-10-01 : 07:06:10

2spirit1

not correct. It must be only one fld_EventBDate corresponded to fld_EventADate

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2004-10-01 : 07:21:22

yeah i know that, but i think that could be fixed in the where (...).

Go with the flow & have fun! Else fight the flow

elwoos
Master Smack Fu Yak Hacker

2052 Posts

Posted - 2004-10-01 : 08:09:52

Thanks for the prompt responses guys I think that may well solve the problem. There is another variant to this which is more complex but I'll have a look at this first to see if I can work it out

Or should I say:-

Thanks 4 da prompt responses guys I think dat may well solve da damn
bug-up-da-ass. Dair be anotha' variant t' dis which be mo' complex but I
gots'ta have some peek at dis fust t' spot if I kin work it out

steve

To alcohol ! The cause of - and solution to - all of life's problems

jen
Master Smack Fu Yak Hacker

4110 Posts

Posted - 2004-10-01 : 10:50:24

quote:
Originally posted by elwoos

Thanks for the prompt responses guys I think that may well solve the problem. There is another variant to this which is more complex but I'll have a look at this first to see if I can work it out

Or should I say:-

Thanks 4 da prompt responses guys I think dat may well solve da damn
bug-up-da-ass. Dair be anotha' variant t' dis which be mo' complex but I
gots'ta have some peek at dis fust t' spot if I kin work it out

steve

To alcohol ! The cause of - and solution to - all of life's problems

the things that alcohol do...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources