joining two tables with min on date

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 2005 Forums

Transact-SQL (2005)

joining two tables with min on date

Author

Topic

mystreetgang
Starting Member

2 Posts

Posted - 2011-01-18 : 09:08:30

hi, this is my first post so hope you can help. i know a little SQL (self taught) but struggling with correlated sub queries.

here goes:

assume i have a table called ##spells
and within that there is a field called "patid" (not unique)
there is another column "admission day" and another "end day".

i have another table ##readmission
also with a patid field and a "readmission day" field which is basically the admission day (including time) but only where the admission is a readmission witin 20 days of a previous "end day".

i am looking to return all values from ##spells and only show the relevant fields from ##readmission where the patid is the same obviously and the "readmission day" is within 20 days of the previous "end day".
Something like where ##spells.patid = ##readmission.patid
and ##readmission.readdmissionday >= ##spells.endday+20
where ##readmission.readdmissionday >= min(##spells.endday)

The minimum is important as there may be multiple readmissions and i want to return only the next one.

hope i have explained properly, i look forward to hearing from you.
Scott.

bobmcclellan
Starting Member

46 Posts

Posted - 2011-01-18 : 13:55:55

you say patid is not unique... you need to have
PK columns if you want to use a left join.
Using CTE's you can build on this... but you will need to
establish the Primary and Foreign Keys to return the set
you are looking for.

with spells as ( select PatID, [Admission Day], [End Day], 'ed20' = dateadd(d,20,[End Day])
From ##spells )

, ra as ( select PatId, [readmission day], minEndDay = min([end day])
from ##readmission
group by PatID, [readmission day]
)

select s.*, r.*
from spells s
left join ra r on s.patID = ra.patID

hth,
..bob

mystreetgang
Starting Member

2 Posts

Posted - 2011-01-19 : 07:06:17

Thank you Bob, that is very helpful.

Regards

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources