Trying to SELECT the most recent Patient.Weight prior to an Order.OrderDate.

[Baesucks]

I have 3 tables.
Patients, Orders, PatientWeights

PatientWeights has 3 fields. PatientID, WeighDate, Weight.
Orders has 3 fields. OrderID, PatientID, OrderDate.
Patients has 3 fields. PatientID, LastName, FirstName.

I want to create query that lists each Order. The trick is I need the weight of the patient that was the most recent weight for that patient listed in the PatientWeights table that is NOT greater than the OrderDate for that Order.

So the fields I need in the result are...
Orders.OrderID
Orders.OrderDate
Patients.PatientID
Patients.LastName
Patients.FirstName
PatientWeights.Weight (of the most recent weight prior to the OrderDate).

I know how to get the most recent weight for each patient, I just can seem to get the most recent weight that is not greater than the order date for each patient/order. Any suggestions?

 

[imex]

Try:

select
    o.OrderID,
    o.OrderDate,
    o.PatientID,
    p.LastName, 
    p.FirstName,
    (select Weight from PatientWieghts as w
     where 
         w.PatientID = o.PatientID and
         w.WeightDate <= o.OrderDate) as Weight 
from Orders as o
inner join Patients as p
    on p.PatientID = o.PatientID

Hope this helps.

[URL unfurl="true"]http://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos[/url]

 

[drlex]

If you're only wanting the most recent weight prior to the order date, may I propose a small amendment to imex's code?

select
    o.OrderID,
    o.OrderDate,
    o.PatientID,
    p.LastName, 
    p.FirstName,
    (select [COLOR=#EF2929]Top 1 [/color]Weight from PatientWeights as w
     where 
         w.PatientID = o.PatientID and
         w.WeightDate <= o.OrderDate [COLOR=#EF2929]Order by w.WeightDate desc[/color]) as Weight 
from Orders as o
inner join Patients as p
    on p.PatientID = o.PatientID

soi là, soi carré

 

[TheBugSlayer]

Just a small detail: if the weight date is prior to the order date then the criteria should be
w.WeightDate < rderDate.

MCP SQL Server 2000, MCTS SQL Server 2005, MCTS SQL Server 2008 (DBD, DBA)

 

[Olaf Doschke]

LOL

Increasing precision. Very fine.

The last query with the last bug slayed is giving you what you need, you can decide yourself, if you want to use <= or <, the difference is neglectable, if your order data is a datetime.
I would rather not do a subselect for each single weight, but a subselect for all patients weights on the order dates, but query optimisation might give the same sql plan anyway.

Bye, Olaf.

 

[Olaf Doschke]

Another non technical aspect:

You correlate patient weights and their orders this way. You may assume patient weight data is much more frequent than orders are, but if that isn't the case, the difference between the order dates and the last patient weight before that order date would also be something you should put into your query result, eg datediff(hour, weightdate, orderdate). In case this is very high and the next weight after the order is much nearer to the order, that would perhaps be a better correlation value.

Bye, Olaf.