sql - Calculate BMI From Height / Weight tables -

clarification

upon working through answers , reading interpretations of question, have following add.

• i need generate entire bmi history, not single value.
• every value in both tables needs paired (if possible) relevant value in other table.

simple problem

given entry in patientheight, compute bmi(body mass index) entries in patientweight entrydate falls between current patientheight entrydate , previous patientheight entrydate. true unless there entrydates in patientweight > entrydates in patientheight. in case, use latest patientheight entry compute bmi.

for every entry in patientheight, compute bmi(body mass index) appropriate corresponding values in patientweight.

some logic:

• patientheight's entrydate <= patientweight's entrydate when pairing
• patientheight has 1 many relationship patientweight
• patientheight must take account entrydate of previous patientheight , use lower boundary when matching entrydates in patientweight

i have function compute bmi, it's question of how best pair data 2 tables.

note: must done via stored procedure, , cannot change 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 iterating on every row in patientweight , querying applicable entries in patientheight , computing bmi, there sort of fancy join pair data correctly?

this 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 core of anyway. seems working far.

insert @patientweightret     select          *         (         select             transactionid,              encounterid,              entrydate,             isnull(convert(numeric(18,2),dbo.fnbmi(inches, pounds)), -1) bmi                 (             select distinct                 w.transactionid,                 w.patientid,                  w.entrydate,                 w.encounterid,                 w.pounds,                 ( -- every weight                     select top 1 --get first entry                         h.inches                                             @patientheight h -- patient height                                               h.entrydate <=  w.entrydate-- who's date less or equal weight date                         , w.entrydate >  -- , weight date greater (the previous height date)                         (                             isnull                             (                                 (                                     select top 1 -- first                                          entrydate -- date                                                                             @patientheight -- patientheight                                                                             entrydate < h.entrydate -- who's entry date less current height date                                     order entrydate desc, transactionid desc                                 )                             , '01/01/1800') -- if we're @ bottom, return old date                         )                     order h.entrydate desc, h.transactionid desc                 ) inches                             patientweight w                              patientid = @patientid                  , active = 1         ) tmp     ) tmp2             bmi != -1     order entrydate desc, transactionid desc 

select w.personid,        w.entrydate,        (            select top 1 h.inches                patientheight h                w.personid = h.personid                    , h.entrydate <= w.entrydate                order h.entrydate desc        ) inches        w.pounds     patientweight w