title: Calculate BMI From Height / Weight tables
body: <p><strong>Clarification</strong></p>
  
  <p>Upon working through your answers and reading your interpretations of this question, I have the following to add.</p>
  
  <ul>
  <li>I need to generate the entire BMI history, not a single value.</li>
  <li>Every value in both tables needs to be paired (if possible) to a relevant value in the other table. </li>
  </ul>
  
  <p><strong>Simple Problem</strong></p>
  
  <p><strike>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.</strike> </p>
  
  <p>For every entry in PatientHeight, compute the BMI(Body Mass Index) with all the appropriate corresponding values in PatientWeight. </p>
  
  <p>Some Logic:</p>
  
  <ul>
  <li>PatientHeight's EntryDate is &lt;= PatientWeight's EntryDate when pairing</li>
  <li>PatientHeight has a one to many relationship with PatientWeight  </li>
  <li>PatientHeight must take into account the EntryDate of the Previous PatientHeight and use it as a lower boundary when matching EntryDates in PatientWeight</li>
  </ul>
  
  <p>I have a function to compute BMI, it's just a question of how best to pair the data from the two tables.</p>
  
  <p><em>Note: This must be done via a stored procedure, and I cannot change the tables</em></p>
  
  <pre>
  <b>PatientHeight</b>
  PersonID
  EntryDate
  Inches
  
  9783 | 01/01/2010 | 75in 
  9783 | 01/01/2009 | 74in
  
  </pre>
  
  <pre>
  <b>PatientWeight</b>
  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
  </pre>
  
  <p><strong>So</strong></p>
  
  <p>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?</p>
  
  <p>This would be ideal:</p>
  
  <pre>
  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
  </pre>
  
  <p><strong>My final Query</strong></p>
  
  <p>Here's the core of it anyway. Seems to be working so far. </p>
  
  <pre><code>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 &lt;=  W.EntryDate-- Who's Date is less than or equal to the Weight Date
                          AND W.EntryDate &gt;  -- 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 &lt; 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
  </code></pre>