CODEWITHSUNDEEP
sqlsql-serversql-server-2012
steelerose
steelerose

Reputation: 153

SQL LAG function with JOIN - must pull data from previous row even if data is null

I have three tables:

1) Calendar

2) Reading_Type

3) Readings

Only one entry is allowed for a given Reading_Type_ID and Reading_Date, but not all dates have a reading.

I need a query that returns all data from Readings, plus yesterday's reading for that Reading_Type_ID.

If the previous day (yesterday) has no reading, Yesterday_Reading should be 0 (it should not just pull the reading from the previous row in the table if that row is > 1 day prior).

What I have so far:

SELECT
      r.Reading_ID
    , r.Reading_Type_ID
    , r.Reading
    , r.Reading_Date
    , LAG(r.Reading, 1, 0)
        OVER (PARTITION BY r.Reading_Type_ID ORDER BY r.Reading_Date)
        AS Yesterday_Reading
FROM
    Calendar c
LEFT JOIN
    Readings r ON c.Date = r.Reading_Date

This LAG function pulls from the last row (by date) in the Readings table, not strictly from "yesterday". I've tried some permutations (ORDER BY c.Date, adding rows from c to the SELECT clause, etc), but I don't know how to fix this.

Upvotes: 0

Views: 4726

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269543

The problem is that you need the full calendar period for each Reading_Type_ID. You can do this using a CROSS JOIN:

SELECT r.Reading_ID, rr.Reading_Type_ID, r.Reading, r.Reading_Date,
       LAG(r.Reading, 1, 0) OVER (PARTITION BY rr.Reading_Type_ID ORDER BY r.Reading_Date
                                 ) as Yesterday_Reading
FROM Calendar c CROSS JOIN
     (SELECT DISTINCT r.Reading_Type_ID FROM Readings r) rr LEFT JOIN
     Readings r
     ON c.Date = r.Reading_Date AND r.Reading_Type_ID = rr.Reading_Type_ID;

Upvotes: 1

Related Questions