Posted on by kevinG73

perform an operation on database using yesterday’s and today’s value

I have a database where there is an table wm for water meters :

    +------+---------------+
    | Code |     name      |
    +------+---------------+
    | wm1  | water meter 1 |
    | wm2  | water meter 2 |
    | wm3  | water meter 3 |
    +------+---------------+

and another table counters where there are counters value :

    +------+---------+-------+------------+
    | Code | Code_wm | value | created_at |
    +------+---------+-------+------------+
    |    1 | wm1     |   100 | 2020-10-18 |
    |    2 | wm1     |   0   | 2020-10-19 |
    |    3 | wm2     |   0   | 2020-10-18 |
    |    4 | wm2     |   100 | 2020-10-19 |
    |    5 | wm3     |   0   | 2020-10-18 |
    |    6 | wm3     |   100 | 2020-10-19 |
    +------+---------+-------+------------+

i want get this result :

    | code_wm | result |   Date     |
    +---------+--------+------------+
    | wm1     | 0-100  | 2020-10-19 |
    | wm2     | 100-0  | 2020-10-19 |
    | wm3     | 100-0  | 2020-10-19 |
    +---------+--------+------------+

but when i try :

SELECT code_wm , LAG(value,1,0) OVER ( ORDER BY code_wm) as result
FROM counters

i don't get the correct result : https://www.db-fiddle.com/f/7TuSTaukG336tqnTNDg4em/0