Select row with most recent date per location and increment recent date by 1 for each row by locatio-CodePudding

I have a table of location which has 'Date column'. I have to find recent date by each group of locationID for e.g. locationID 1 has most recent date '31 May 2022'. After finding recent date from the group of locationID I have to add 14 days in that recent date and store it in NewDate column. and add 1 in that new date for other row for that group of locationID.

My table is:

id  locationID    Date        NewDate   
1       1       31 May 2022
2       1       16 May 2022
3       1       28 Apr 2021
4       2       29 Mar 2022
5       2       22 Feb 2022
6       3       14 Jun 2022
7       3       27 Oct 2021
8       4       01 Feb 2022
9       4       04 May 2022
10      4       14 Jun 2021
11      5       01 Jun 2022
12      5       29 May 2022
13      5       20 Sep 2022
14      5       11 Aug 2022
15      5       03 Aug 2022

Answer should be as below:

For e.g. for locationID = 1

   id     locationID       Date             NewDate   
    1       1         31 May 2022       14 Jun 2022    // Recent Date   14 Days - 31 May   14 Days
    2       1         16 May 2022       15 Jun 2022    // Recent Date   15 Days - 31 May   15 Days
    3       1         28 Apr 2021       16 Jun 2022    // Recent Date   16 Days - 31 May   16 Days

I have come across few similar post and found recent date like this:

SELECT L.*
FROM Locations L 
INNER JOIN 
    (SELECT locationID, MAX(Date) AS MAXdate
     FROM Locations 
     GROUP BY locationID) groupedL
     ON L.locationID = groupedL.locationID
     AND L.Date = groupedL.MAXdate

using above code I am able to find recent date per location but how do I add and increment required days and store it to NewDate column ? I am new to MariaDB, please suggest similar post link, any reference documents or blogs. Should I make some function to perform this logic and call the function to store required dates in NewDate column? I am not sure please suggest. Thank you.

RESULT SHOULD LOOK LIKE BELOW:

id     locationID       Date             NewDate   
    1       1         31 May 2022       14 Jun 2022    // Recent Date for locationid 1   14 Days - 31 May   14 Days
    2       1         16 May 2022       15 Jun 2022    // Recent Date for locationid 1   15 Days - 31 May   15 Days
    3       1         28 Apr 2021       16 Jun 2022    // Recent Date for locationid 1   16 Days - 31 May   16 Days
    4       2         29 Mar 2022       12 APR 2022    // Recent Date for locationid 2   14 Days
    5       2         22 Feb 2022       13 APR 2022    // Recent Date for locationid 2   15 Days
    6       3         14 Jun 2022       28 JUN 2022    // Recent Date for locationid 3   14 Days
    7       3         27 Oct 2021       29 JUN 2022    // Recent Date for locationid 3   15 Days
    8       4         01 Feb 2022       18 MAY 2022    // Recent Date for locationid 4   14 Days
    9       4         04 May 2022       19 MAY 2022    // Recent Date for locationid 4   15 Days
    10      4         14 Jun 2021       20 MAY 2022    // Recent Date for locationid 4   16 Days
    11      5         01 Jun 2022       04 OCT 2022    // Recent Date for locationid 5   14 Days
    12      5         29 May 2022       05 OCT 2022    // Recent Date for locationid 5   15 Days
    13      5         20 Sep 2022       06 OCT 2022    // Recent Date for locationid 5   16 Days
    14      5         11 Aug 2022       07 OCT 2022    // Recent Date for locationid 5   17 Days
    15      5         03 Aug 2022       08 OCT 2022    // Recent Date for locationid 5   18 Days

CodePudding user response：

You can use a cte:

with cte as (
   select l1.*, l2.m, (select sum(l4.id < l1.id and l4.locationid = l1.locationid) from locations l4) inc from locations l1 
   join (select l3.locationid, max(l3.dt) m from locations l3 group by l3.locationid) l2 on l1.locationid = l2.locationid
)
select c.id, c.locationid, c.dt, c.m   interval 14   c.inc day from cte c

CodePudding user response：

You could use analytic window functions:

select Id, locationId, Date, 
    Date_Add(First_Value(date) over(partition by locationId order by date desc), 
      interval (13   row_number() over(partition by locationId order by date desc)) day
    ) NewDate
from t
order by LocationId, NewDate;

See DB<>Fiddle example