sql - 从高度/体重表计算 BMI

Question

澄清

在研究了您的答案并阅读了您对此问题的解释后，我要补充以下内容。

• 我需要生成整个 BMI 历史记录，而不是单个值。
• 两个表中的每个值都需要与另一个表中的相关值配对(如果可能)。

简单问题

给定 PatientHeight 中的一个条目，使用 PatientWeight 中其 EntryDate 介于当前 PatientHeight EntryDate 和前一个 PatientHeight EntryDate 之间的所有条目计算 BMI( body 质量指数)。除非 PatientWeight 中的 EntryDates > 然后 PatientHeight 中的任何 EntryDates，否则这是正确的。在本例中，使用最新的 PatientHeight 条目来计算 BMI。

对于 PatientHeight 中的每个条目，使用 PatientWeight 中所有适当的对应值计算 BMI(体重指数)。

一些逻辑:

• PatientHeight 的 EntryDate <= 配对时 PatientWeight 的 EntryDate
• PatientHeight 与 PatientWeight 存在一对多关系
• PatientHeight 必须考虑前一个 PatientHeight 的 EntryDate，并在匹配 PatientWeight 中的 EntryDate 时将其用作下限

我有一个计算 BMI 的函数，这只是如何最好地将两个表中的数据配对的问题。

注意:这必须通过存储过程完成，并且我无法更改表

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

PatientWeightPersonIDEntryDatePounds9783 | 01/01/2011 | 179lbs9783 | 01/01/2010 | 175lbs9783 | 12/01/2010 | 174lbs9783 | 11/01/2010 | 178lbs9783 | 01/01/2009 | 174lbs9783 | 12/01/2009 | 174lbs9783 | 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 | 178lbs9783 | 01/01/2010 | 75in | 175lbs9783 | 12/01/2010 | 75in | 174lbs9783 | 11/01/2010 | 75in | 178lbs9783 | 01/01/2009 | 74in | 174lbs9783 | 12/01/2009 | 74in | 174lbs9783 | 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

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