I’m trying to write a query that is sort of like a running total but not really. I want to get the previous weight (kg) and keep outputting that for each day until another weight (kg) is recorded then continue to output that until next weight recorded. Below is example of what I’m trying to accomplish (see KG column).
Current results:
ENCOUNTER_ID | KG | DATE_RECORDED | CALENDAR_DT ----------------------------------------------- 100 10 2019-01-01 2019-01-01 NULL NULL NULL 2019-01-02 100 12 2019-01-03 2019-01-03 NULL NULL NULL 2019-01-04 NULL NULL NULL 2019-01-05 NULL NULL NULL 2019-01-06 100 13 2019-01-07 2019-01-07 NULL NULL NULL 2019-01-08
Desired Results:
ENCOUNTER_ID | KG | DATE_RECORDED | CALENDAR_DT ----------------------------------------------- 100 10 2019-01-01 2019-01-01 NULL 10 NULL 2019-01-02 100 12 2019-01-03 2019-01-03 NULL 12 NULL 2019-01-04 NULL 12 NULL 2019-01-05 NULL 12 NULL 2019-01-06 100 13 2019-01-07 2019-01-07 NULL 13 NULL 2019-01-08
Advertisement
Answer
In standard SQL, you would use lag() with the ignore nulls option:
select t.*,
lag(kg ignore nulls) over (order by calendar_dt)
from t;
Not all databases support ignore nulls. But it is standard SQL and you haven’t specified the database you are using.