MySQL minus previous row with conditions

Ethan McGhin 0
Viewed 32

In the table below, I'd like to minus the previous rows date from the current rows date as long as the MPAN columns match, also if the result was to be over 365 I'd like it to max as 365.

We're using MySQL and PHP to try and calculate these and we could do with some guidance on how to achieve it.

Source

MPAN Current_Supplier EAC EAC_EFD Days Applicable Value
ID1 TGPL 96.7 22/05/2021 0 0.26
ID1 TGPL 28009.9 26/11/2021 0 148.74
ID1 TGPL 30771.2 23/05/2022 0 16.61
ID2 TGPL 191.9 22/05/2021 0 0.53
ID2 TGPL 217.9 26/08/2021 0 0.8
ID2 TGPL 118.1 26/11/2021 0 -0.56
ID2 TGPL 38 25/02/2022 0 -0.35
ID2 TGPL 18.2 23/05/2023 0 0.3

Desired result

MPAN Current_Supplier EAC EAC_EFD Days Applicable Value
ID1 TGPL 96.7 22/05/2021 365 0.26
ID1 TGPL 28009.9 26/11/2021 188 148.74
ID1 TGPL 30771.2 23/05/2022 178 16.61
ID2 TGPL 191.9 22/05/2021 365 0.53
ID2 TGPL 217.9 26/08/2021 96 0.8
ID2 TGPL 118.1 26/11/2021 92 -0.56
ID2 TGPL 38 25/02/2022 91 -0.35
ID2 TGPL 18.2 23/05/2023 365 0.3

Thanks.

window-functions mysql
1 Answers

When you use MySQL 8 then you can use window function LAG returned value from previous row:

select 
    id, d,
    LEAST(
      COALESCE(
        DATEDIFF(d, lag(d) over (partition by id order by d)), 365
      ), 
    365)
from tbl;

For prevent numbers greater then 365 use LEAST function and COALESCE for prevent NULL values

SQL online test

Slava Rozhnev0
Related