Calculate BMI from height / weight tables

Question

Explanation

After completing your answers and reading your interpretations of this question, I must add the following.

• I need to create the whole BMI story, not one value.
• Each value in both tables should be associated (if possible) with the corresponding value in another table.

Simple problem

When entering into PatientHeight, calculate the body mass index (BMI) with all entries in PatientWeight whose EntryDate is between the current PatientHeight EntryDate and the previous PatientHeight EntryDate. This is true if there are no EntryDates in PatientWeight and then "EntryDates" in PatientHeight. In this case, use the latest PatientHeight entry to calculate BMI.

For each entry in PatientHeight, calculate the body mass index (BMI) with all the corresponding relevant values ​​in PatientWeight.

Some logic:

• PatientHeight EntryDate is <= PatientWeight EntryDate when mating
• PatientHeight has a one-to-many relationship with PatientWeight
• PatientHeight should take into account the EntryDate of the previous PatientHeight and use it as the lower bound when matching EntryDates in PatientWeight

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

Note. This must be done using a stored procedure, and I cannot modify 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

PatientWeight PatientHeight, , - ?

:

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

​​ . , .

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 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 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

Answer 1

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

Answer 2

- ( ).

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

Answer 3

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

Answer 4

-

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 you need to show only the "current" data, the "current" -

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