Scott Scott - 27 days ago 6
SQL Question

How to calculate percent change from irregular intervals from long form data

I have data in table P

id bmi contact_date
1 25 01/01/2015
1 26 06/15/2015
2 20 01/01/2014
3 21 03/12/2014
3 22 04/15/2015
3 NULL 09/12/2015
3 23 12/10/2015


What I would like to do using SQL is get the percent change in BMI between the two most recent values for each id (ie one row per id). If there are not two values, then I simply want for there to be a '0'.

id change
1 .04
2 0
3 .045

Answer

You can get the two most recent values using OLAP-functions and then it's just applying the percent calculation:

SELECT id,
  Coalesce((Cast(bmi AS DECIMAL(8,3)) /  -- most recent bmi
            Min(bmi)                     -- 2nd most recent bmi
            Over (PARTITION BY id
                  ORDER BY contact_date DESC
                  ROWS BETWEEN 1 Following AND 1 Following)) - 1
          , 0)
FROM tab t
WHERE bmi IS NOT NULL
QUALIFY -- this returns the most recent row 
   Row_Number()
   Over (PARTITION BY id 
         ORDER BY contact_date DESC) = 1

This assumes that bmi (body mass index) will never be zero, but in your comment you noted a division by zero error. You better remove zeros by switching to WHERE bmi > 0 (which also excludes NULLs).

Or change zeroes to NULL BY adding NULLIF like this:

Coalesce((Cast(bmi AS DECIMAL(8,3)) /  -- most recent bmi
          NullIf(Min(bmi)                     -- 2nd most recent bmi
                 Over (PARTITION BY id
                       ORDER BY contact_date DESC
                       ROWS BETWEEN 1 Following AND 1 Following),0)) - 1
        , 0)