MySQL minus previous row with conditions-CodePudding

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	Value
ID1	TGPL	96.7	22/05/2021	0.26
ID1	TGPL	28009.9	26/11/2021	148.74
ID1	TGPL	30771.2	23/05/2022	16.61
ID2	TGPL	191.9	22/05/2021	0.53
ID2	TGPL	217.9	26/08/2021	0.8
ID2	TGPL	118.1	26/11/2021	-0.56
ID2	TGPL	38	25/02/2022	-0.35
ID2	TGPL	18.2	23/05/2023	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.

CodePudding user response：

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