Row_number find last two before certain 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)

Row_number find last two before certain date

Author

Topic

oasis1
Starting Member

35 Posts

Posted - 2010-06-30 : 03:46:28

I have two tables. One has lab results and the other Patient info. The lab results can have zero to many records the key is patient ID.

The second table has patient ID and delivery date. patient ID is unique.

Let's say patient A has 10 HBA1C tests. She had a delivery date on 6/1/09. Since then she has had 4 HBA1C test and 6 prior. I need the two before 6/1/09. I know I could do this in a cursor but would rather not. Is there a way to do this with the Row_number function?

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2010-06-30 : 03:55:39

It would be easier to help with table structure, sample data and wanted output.

No, you're never too old to Yak'n'Roll if you're too young to die.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2010-06-30 : 04:21:13

SELECT FirstTable.*, ot.*
FROM FirstTable
CROSS APPLY (SELECT TOP(2) * FROM OtherTable WHERE OtherTable.PatientID = FirstTable.PatientID AND OtherTable.Date <= FirstTable.Date ORDER BY FirstTable.Date DESC) AS ot

N 56°04'39.26"
E 12°55'05.63"

oasis1
Starting Member

35 Posts

Posted - 2010-06-30 : 12:10:22

sample data would be

# of rows table 1 89000
Table 1
PAT_ID Lab_Test Result_Date
1234 HBA1C 1/1/2009
1234 HBA1C 12/1/2008
1234 HBA1C 7/1/2008
1234 HBA1C 2/7/2008
1234 HBA1C 11/7/2007
1234 HBA1C 9/1/2007
1234 HBA1C 5/15/2007

#rows table 2 6000

Table 2
PAT_ID Delivery_date
1234 9/1/2008

I need to see rows results
PAT_ID Deliver_Date Result_Date
1234 9/1/2008 7/1/2008
1234 9/1/2008 2/7/2008

namman
Constraint Violating Yak Guru

285 Posts

Posted - 2010-06-30 : 13:20:59

select top (2) t1.PAT_ID, t2.Delivery_date, t1.Result_Date
from table1 t1 join table2 t2
where t1.PAT_ID=t2.PAT_ID and t1.Result_Date <= t2.Delivery_date
order by t1.Result_Date desc

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources