Calculate BMI From Height / Weight tables

Clarification

Upon working through your answers and reading your interpretations of this question, I have the following to add.

• I need to generate the entire BMI history, not a single value.
• Every value in both tables needs to be paired (if possible) to a relevant value in the other table.

Simple Problem

Given an entry in PatientHeight, compute the BMI(Body Mass Index) with all entries in PatientWeight whose EntryDate falls between the current PatientHeight EntryDate and the previous PatientHeight EntryDate. This is true unless there are EntryDates in PatientWeight that are > then any EntryDates in PatientHeight. In this case, use the latest PatientHeight entry to compute the BMI.

For every entry in PatientHeight, compute the BMI(Body Mass Index) with all the appropriate corresponding values in PatientWeight.

Some Logic:

• PatientHeight's EntryDate is <= PatientWeight's EntryDate when pairing
• PatientHeight has a one to many relationship with PatientWeight
• PatientHeight must take into account the EntryDate of the Previous PatientHeight and use it as a lower boundary when matching EntryDates in PatientWeight

I have a function to compute BMI, it's just a question of how best to pair the data from the two tables.

Note: This must be done via a stored procedure, and I cannot change the tables

PatientHeight
PersonID
EntryDate
Inches

9783 | 01/01/2010 | 75in 
9783 | 01/01/2009 | 74in

PatientWeight
PersonID
EntryDate
Pounds

9783 | 01/01/2011 | 179lbs
9783 | 01/01/2010 | 175lbs
9783 | 12/01/2010 | 174lbs
9783 | 11/01/2010 | 178lbs
9783 | 01/01/2009 | 174lbs
9783 | 12/01/2009 | 174lbs
9783 | 11/01/2009 | 178lbs

So

Aside from iterating over every row in PatientWeight and querying for applicable Entries in PatientHeight and then computing BMI, is there any sort of fancy join to pair up the data correctly?

This would be ideal:

9783 | 01/01/2011 | 75in | 178lbs
9783 | 01/01/2010 | 75in | 175lbs
9783 | 12/01/2010 | 75in | 174lbs
9783 | 11/01/2010 | 75in | 178lbs
9783 | 01/01/2009 | 74in | 174lbs
9783 | 12/01/2009 | 74in | 174lbs
9783 | 11/01/2009 | 74in | 178lbs

My final Query

Here's the core of it anyway. Seems to be working so far.

Insert Into @PatientWeightRet
    Select 
        *
    From
    (
        Select
            TransactionID, 
            EncounterID, 
            EntryDate,
            ISNULL(CONVERT(NUMERIC(18,2),dbo.fnBmi(Inches, Pounds)), -1) AS BMI
        From
        (
            Select Distinct
                W.TransactionID,
                W.PatientID, 
                W.EntryDate,
                W.EncounterID,
                W.Pounds,
                ( -- For Every Weight
                    Select Top 1 --Get the first Entry
                        H.Inches
                    From
                        @PatientHeight AS H -- From Patient Height 
                    Where 
                        H.EntryDate <=  W.EntryDate-- Who's Date is less than or equal to the Weight Date
                        AND W.EntryDate >  -- and the Weight Date is greater than (the previous height date)
                        (
                            ISNULL
                            (
                                (
                                    Select Top 1 -- the first 
                                        EntryDate -- date
                                    From
                                        @PatientHeight -- from patientHeight
                                    Where
                                        EntryDate < H.EntryDate -- who's entry date is less than the current height date
                                    Order BY EntryDate Desc, TransactionID DESC
                                )
                            , '01/01/1800') -- if we're at the bottom, return really old date
                        )
                    Order By H.EntryDate Desc, H.TransactionID DESC
 开发者_运维知识库               ) AS Inches
            From
                PatientWeight AS W
            Where 
                PatientID = @PatientID 
                AND Active = 1
        ) tmp
    ) tmp2
    Where
        BMI != -1
    Order By EntryDate DESC, TransactionID DESC

---

SELECT W.PersonID,
       W.EntryDate,
       (
           SELECT TOP 1 H.Inches
               FROM PatientHeight AS H
               WHERE W.PersonID = H.PersonId
                   AND H.EntryDate <= W.EntryDate
               ORDER BY H.EntryDate DESC
       ) AS Inches
       W.Pounds
    FROM PatientWeight AS W

---

Something like the following should do the trick (not tested).

SELECT P.PaitenId
,      W.EntryDate
,      P.Inches
,      W.Pounds
FROM (
  SELECT p.PatientId
  ,      p.EntryDate AS EntryDate
  ,      MIN(p2.EntryDate) as NextEntryDate
  FROM PatientHeight p
  LEFT JOIN PatientHeight p2
  ON p.PatientID = p2.PatientID
  AND p2.EntryDate > p.EntryDate
  GROUP BY p.PatientId
  , p.EntryDate
) P
JOIN PaitentWeight W
ON P.PatientId = W.PatientId
AND W.EntryDate BETWEEN P.EntryDate AND P.NextEntryDate

---

SELECT
  w.PersonID,
  w.EntryDate,
  Inches = MIN(h.Inches)
  w.Pounds
FROM PatientWeight w
  LEFT JOIN PatientHeight h
    ON w.PersonID = h.PersonID AND w.EntryDate >= h.EntryDate

---

Something like this

select
      curr.personid, curr.entrydate, wgt.entrydate WeightDate,
      dbo.CalcBMI(curr.Inches, wgt.Pounds) as BMI
from
     (Select top 1 * from PatientHeight
      where personid= @personid
      order by entrydate desc) curr
outer apply
     (select top 1 * from PatientHeight
      where personid= curr.personid
        and entrydate < curr.entrydate
      order by entrydate desc) prev
join
      PatientWeight wgt
  on (wgt.entrydate > prev.entrydate or prev.entrydate is null)
      and wgt.personid = curr.personid

My reading of the question suggests that only the "current" data needs to be shown, "current" being

All entries in PatientWeight whose EntryDate falls between the current PatientHeight EntryDate and the previous PatientHeight EntryDate