Consider the following schema (fiddle):
CREATE TABLE meters ( id int, description varchar(10) ); CREATE TABLE readings ( id int, meterid int, date date, value int ); INSERT INTO readings (id, meterid, date, value) VALUES (1, 4, '20081231', 500), (2, 4, '20090203', 550), (3, 1, '20090303', 300), (4, 2, '20090303', 244), (5, 4, '20090303', 600), (6, 1, '20090403', 399), (7, 2, '20090403', 288), (8, 3, '20090403', 555); INSERT INTO meters (id, description) VALUES (1, 'this'), (2, 'is'), (3, 'not'), (4, 'really'), (5, 'relevant');
For each meter.id
I need to find the latest reading date, value, and the value difference vs previous reading.
For the sample data my output would look like this (plus some other columns from meters
):
meterid | latest | value | delta value |
---|---|---|---|
1 | 20090403 | 399 | 99 |
2 | 20090403 | 288 | 44 |
3 | 20090403 | 555 | null |
4 | 20090303 | 600 | 50 |
5 | null | null | null |
I figured I could first create a query with the relevant info and then join with that, but I struggle with achieving that
I’ve tried to adapt this method but for each id I get 2 rows instead of one
SELECT p.meterid, [1] AS [LastDate], [2] AS [BeforeLastDate] FROM (SELECT TOP (2) WITH ties *, RowN = ROW_NUMBER() OVER (PARTITION BY r.meterid ORDER BY date DESC) FROM readings AS r ORDER BY (ROW_NUMBER() OVER (PARTITION BY r.meterid ORDER BY date DESC) - 1) / 2 + 1) a PIVOT (MAX(date) FOR RowN IN ([1], [2])) p ORDER BY p.meterId
I’m looking for ideas how to solve the double row issue, or if that’s a dead end how to get my desired output
Advertisement
Answer
If I understand correctly, you can just use window functions:
select m.id, r.date, r.value, r.value - prev_value from meters m left join (select r.*, lag(value) over (partition by meterid order by date) as prev_value, row_number() over (partition by meterid order by date desc) as seqnum from readings r ) r on r.meterid = m.id and seqnum = 1 order by m.id;
No aggregation is necessary. Here is a db<>fiddle.