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.
| Author |
Topic |
|
TimChenAllen
Starting Member
45 Posts |
 Posted - 2004-05-13 : 10:03:39
|
Hi All,I have a situation like this: we have a history table of patient insurance plans:PATIENT PLAN_NAME LOAD_DATE ------- --------- ---------Joe DEF 2/11/2004 Joe ABC 1/5/2004 Sam DEF 1/15/2003 Sam ABC 1/12/2003 We want to produce the last active PLAN_NAME for a patient. In this case, both Joe and Sam have plan DEF as their most recent plan.Currently we are doing this with a subselect:select Patient, Plan_Name, Load_Date from plan o where Load_Date = (select max(Load_Date) from plan i where i.Patient = o.Patient) But this gets very slow, because instead of having two patients, we have a million.I seem to remember that there is a trick for this sort of thing using a table with the range of all expected dates (I can't remember what this kind of table is called either!) [UPDATE: Tally Table]Can someone help here? Thanks in advance.--Timothy Chen AllenDBA, East Jefferson General Hospital, New Orleans[url]http://www.timallen.org[/url] |
|
|
mohdowais
Sheikh of Yak Knowledge
1456 Posts |
Posted - 2004-05-13 : 10:34:31
|
Hey tim...when did you leave spain and move to the US?  You could join to a derived table like so:SELECT Patient, Plan_name, Load_date FROM plan p INNER JOIN (SELECT Patient, MAX(Load_date) as Last_Date FROM plan GROUP BY Patient) o ON p.Patient = o.Patient AND p.Load_date = o.Last_Date This will likely outrun the subquery for larger recordsets. I am wondering how you handle ties, i.e. what if a patient has two plans with the same date?OS |
 |
|
|
TimChenAllen
Starting Member
45 Posts |
Posted - 2004-05-13 : 10:50:19
|
quote:
Originally posted by mohdowais
Hey tim...when did you leave spain and move to the US?
Hi mohdowais!I moved back to the USA in January. I'm living in New Orleans-- life's nice in the Big Easy.--Timothy Chen AllenDBA, East Jefferson General Hospital, New Orleans[url]http://www.timallen.org[/url] |
|
|
|
|
|
|
|
|
Find most recent record in history table