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

[kevinG73]

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

[Geek-Master]

This is an old post but wanted to take a crack at it.

Looks like you're trying to show the trend of the counter's value based on what your final result should look like. Basically, you want to see if the value increases from 0 to 100 and inversly if it decreases from 100 to 0. Using the LAG function is a good start but keep in mind it's only looking at the previous row's value regardless of the code_wm column unless you partition. Windowing functions can be tricky especially when you need to group. Here you need to group based on code_wm so that wm1, wm2, and wm3 are grouped accordingly to determine if you're trending up, down, or no movement between the counter dates. Comparing wm1 with wm2 will generate output that is useless to you again based on what you presented. You will need to add the PARTITION BY clause on the code_wm before the ORDER BY which should be the date so it's in order of time. This way you're grouping based on a key value and then sorting by date/time to get your trend.

The next thing we need to do is concatenate your LAG value with the current row's value. You can do this by casting or converting the LAG and value with a ' - ' in between. You may want to filter out beginning rows since the default of the LAG value will be 0 on all partitions, but not sure if you need them or not. It may be easier to use NULL or -1 as a default to filter those out in the final result.