I need some help in splitting an eccesive value into following rows-CodePudding

I need some help in this logic: I have a table created like this:

I need an SQL where I calculate a New ImpegnoSlot based on this logic:

each row cannot have a column impegno higher than settings.MaxImpegno (in this example will be 3), so I need a select where the eccessive quantity is added to the following rows up to 3, like this (not working)

SELECT p.IdSlot, 
   p.Orario, 
   p.ImpegnoSlot,
   IF(p.NewImpegno < s.MaxImpegnoForno, p.ImpegnoSlot, p.NewImpegno) as ImpegnoNew FROM (SELECT es.*,               
           ( es.ImpegnoSlot   
             (SELECT IFNULL(SUM(es2.ImpegnoSlot - s.MaxImpegnoForno), 0)
                FROM elenco_slot es2,
                     settings s
               WHERE es2.Orario < es.Orario
                 AND (es2.ImpegnoSlot - s.MaxImpegnoForno) > 0
              ORDER BY es2.orario)
           ) as NewImpegno
     FROM elenco_slot es
    WHERE 1 = 1
    ORDER BY es.orario) p,
   settings s ORDER BY p.Orario

What I need is this:

Can anybody help? thank you

EDIT 1:

I'm providing table data as text:

1   15:00:00    0.00
2   15:15:00    0.00
3   15:30:00    0.00
4   15:45:00    0.00
5   16:00:00    0.00
6   16:15:00    0.00
7   16:30:00    0.00
8   16:45:00    0.00
9   17:00:00    6.00
10  17:15:00    1.00
11  17:30:00    1.00
12  17:45:00    0.00
13  18:00:00    0.00
14  18:15:00    0.00
15  18:30:00    0.00
16  18:45:00    0.00
17  19:00:00    0.00
18  19:15:00    0.00
19  19:30:00    0.00
20  19:45:00    0.00
21  20:00:00    0.00
23  20:15:00    0.00
24  20:30:00    0.00
25  20:45:00    0.00
26  21:00:00    0.00
27  21:15:00    0.00
28  21:30:00    0.00
29  21:45:00    0.00
30  22:00:00    0.00
31  22:15:00    0.00
32  22:30:00    0.00
33  22:45:00    0.00
34  23:00:00    0.00
35  23:15:00    0.00
36  23:30:00    0.00
37  23:45:00    0.00

And also expected result as text

4   15:45:00    0.00    0.00
5   16:00:00    0.00    0.00
6   16:15:00    0.00    0.00
7   16:30:00    0.00    0.00
8   16:45:00    0.00    0.00
9   17:00:00    6.00    6.00
10  17:15:00    1.00    3.00
11  17:30:00    1.00    2.00
12  17:45:00    0.00    0.00
13  18:00:00    0.00    0.00
14  18:15:00    0.00    0.00
15  18:30:00    0.00    0.00
16  18:45:00    0.00    0.00
17  19:00:00    0.00    0.00
18  19:15:00    0.00    0.00
19  19:30:00    0.00    0.00
20  19:45:00    0.00    0.00
21  20:00:00    0.00    0.00
23  20:15:00    0.00    0.00
24  20:30:00    0.00    0.00
25  20:45:00    0.00    0.00
26  21:00:00    0.00    0.00
27  21:15:00    0.00    0.00
28  21:30:00    0.00    0.00
29  21:45:00    0.00    0.00
30  22:00:00    0.00    0.00
31  22:15:00    0.00    0.00
32  22:30:00    0.00    0.00
33  22:45:00    0.00    0.00
34  23:00:00    0.00    0.00
35  23:15:00    0.00    0.00
36  23:30:00    0.00    0.00
37  23:45:00    0.00    0.00

CodePudding user response：

This method uses a variable and should work in all versions of mySQL.
Following the request I have added a second version with a parameter for the max value. See after the first version.

create table settings(
id int not null primary key auto_increment,
imp int);
insert into settings (imp)
values
(0),(0),(6),(1),(1),(0);

✓

✓

set @c = 0;
select
  id,
  imp raw_value,
  case when imp   @c > 2 
    then 3 else imp   @c end smoothed_value,
  @c := case 
    when (imp   @c) > 2 
      then imp   @c - 3
    else 0 end calculation
FROM settings

✓

id | raw_value | smoothed_value | calculation
-: | --------: | -------------: | ----------:
 1 |         0 |              0 |           0
 2 |         0 |              0 |           0
 3 |         6 |              3 |           3
 4 |         1 |              3 |           1
 5 |         1 |              2 |           0
 6 |         0 |              0 |           0

db<>fiddle here

create table settings(
id int not null primary key auto_increment,
imp int);
insert into settings (imp)
values
(0),(0),(36),(1),(8),(0),(0),(0);

✓

✓

set @max = 10;
set @c = 0;
select
  id,
  imp raw_value,
  case when imp   @c > @max 
    then @max else imp   @c end smoothed_value,
  @c := case 
    when (imp   @c) > @max 
      then imp   @c - @max
    else 0 end carry_over_to_next_line
FROM settings

✓

✓

id | raw_value | smoothed_value | carry_over_to_next_line
-: | --------: | -------------: | ----------------------:
 1 |         0 |              0 |                       0
 2 |         0 |              0 |                       0
 3 |        36 |             10 |                      26
 4 |         1 |             10 |                      17
 5 |         8 |             10 |                      15
 6 |         0 |             10 |                       5
 7 |         0 |              5 |                       0
 8 |         0 |              0 |                       0

db<>fiddle here