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:
x
ENCOUNTER_ID | KG | DATE_RECORDED | CALENDAR_DT-----------------------------------------------100 10 2019-01-01 2019-01-01NULL NULL NULL 2019-01-02100 12 2019-01-03 2019-01-03NULL NULL NULL 2019-01-04NULL NULL NULL 2019-01-05NULL NULL NULL 2019-01-06100 13 2019-01-07 2019-01-07NULL NULL NULL 2019-01-08 Desired Results:
ENCOUNTER_ID | KG | DATE_RECORDED | CALENDAR_DT-----------------------------------------------100 10 2019-01-01 2019-01-01NULL 10 NULL 2019-01-02100 12 2019-01-03 2019-01-03NULL 12 NULL 2019-01-04NULL 12 NULL 2019-01-05NULL 12 NULL 2019-01-06100 13 2019-01-07 2019-01-07NULL 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.