Scott - 1 year ago 78
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

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)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download