join two tables on Min(datediff()) from two tables

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

SQL Server Development (2000)

join two tables on Min(datediff()) from two tables

Author

Topic

cjcclee
Starting Member

33 Posts

Posted - 2006-09-27 : 10:28:33

I have two tables A, B.I want to join two tables based on ssn and Min(datediff( )) from two columns in tables A and B.
for example, A table have ssn,applicationtime, B table have ssn, Admissiontime. Same person may have many applicationtime and admissiontime. I want to join two tables to get each person's each time applicationtime and admissiontime. I think use Min(datediff(day,applicationtime,admissiontime)).
how can I wrote the query to get each person's each time applicationtime and admission time.

Thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-27 : 10:47:29

[code]select a.ssn datediff(day, a.applicationtime, b.admissiontime)
from tablea a inner join tableb b
on a.ssn = b.ssn
[/code]

cjcclee
Starting Member

33 Posts

Posted - 2006-09-27 : 11:03:23

thanks for your reply.
your query get many duplicate record if only join on a.ssn=b.ssn
for example
table A table B

ssn applicationtime ssn admissiontime dischargetime
1234 1/1/2005 1234 1/9/2005 1/23/2005
1234 2/1/2006 1234 2/7/2006 3/31/2006

the query I want is

ssn applicationtime admission discharge
1234 1/1/2005 1/9/2005 1/23/2005
1234 2/1/2006 2/7/2006 3/31/2006

I do not want 1234 1/1/2005 2/7/2006 3/31/2006
1234 2/1/2006 1/9/2005 1/23/2005

join condition should be ssn and Min(datediff(day,applicationtime,admissiontime))

Can anyone help me to write this query??
thanks!

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-09-27 : 11:19:02

Do you have any other column that can relate a row in tablea to tableb beside the ssn ?

if not, try this


declare @tablea table
(
	ssn 		int,
	applicationtime datetime
)

declare @tableb table
(
	ssn 		int,
	admissiontime 	datetime,
	dischargetime	datetime
)
insert into @tablea
select	1234, '1/1/2005'	union all
select	1234, '2/1/2006'

insert into @tableb
select	1234, '1/9/2005', '1/23/2005'	union all
select	1234, '2/7/2006', '3/31/2006'

select	a.ssn, a.applicationtime, b.admissiontime, b.dischargetime
from
(
	select	ssn, applicationtime,
		row_no = (select count(*) from @tablea x where x.ssn = a.ssn and x.applicationtime <= a.applicationtime)
	from	@tablea a
) a inner join
(
	select	ssn, admissiontime, dischargetime,
		row_no = (select count(*) from @tableb x where x.ssn = b.ssn and x.admissiontime <= b.admissiontime)
	from	@tableb b
) b
on	a.ssn		= b.ssn
and	a.row_no	= b.row_no

cjcclee
Starting Member

33 Posts

Posted - 2006-09-27 : 15:45:16

Thanks so much for your help.

I stuck here for long time. You help me out. Thanks a bunch!

thanks again! I appreciated

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources