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

Transact-SQL (2005)

Question

Author

Topic

helixpoint
Constraint Violating Yak Guru

291 Posts

Posted - 2007-08-14 : 10:24:48

I have 2 tables

DestinationHistory
PatientID
DestinationID

Destination
DestinationID
DestinationName

In the DestinationHistory table there are the are multiple records for each patient. I want to get a count of each destination, but I only want to include the latest one for each patient. There is no timestamp. This gets all the values

SELECT COUNT(*) AS Expr1, Destination.DestinationName
FROM DestinationHistory INNER JOIN
Destination ON DestinationHistory.DestinationID = Destination.DestinationID
GROUP BY Destination.DestinationName

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2007-08-14 : 10:39:17

How do you know what the latest destination for each patient is?

Jim

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2007-08-14 : 10:44:57

Base on the assumption that the max of DestinationID is the latest destination for each patient

SELECT D.DestinationName, COUNT(*) AS Expr1
FROM   Destination d INNER JOIN
       (
           SELECT PatienID, DestinationID = MAX(DestinationID)
           FROM   DestinationHistory
           GROUP BY PatienID
       ) h ON d.DestinationID = h.DestinationID

KH
[spoiler]Time is always against us[/spoiler]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources