Skip to content
Advertisement

Finding last and second last date and corresponding values

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.

User contributions licensed under: CC BY-SA
7 People found this is helpful
Advertisement