'Separate a row into equal interval of date and also separate the value

create a new column as end date with intervals from the given input and separate the amount value in equals

table provided

id stdate amount
C1 1/1/2020 90
C2 2/2/2020 100
C3 3/3/2020 60

expected table

id stdate enddt amt
C1 1/1/2020 1/3/2020 30
C1 1/2/2020 1/3/2020 30
C1 1/3/2020 1/3/2020 30
C2 2/2/2020 2/2/2020 100
C3 3/3/2020 3/5/2020 20
C3 3/4/2020 3/5/2020 20
C3 3/5/2020 3/5/2020 20

can you guys explain this output

mysqlsql


Solution 1:[1]

I have made a stored procedure to do this. Just specify the id and the end date when you call it , and it will insert into the new table. A warning is generated if the end date is older than the start date. Here is the code which I wrote in workbench:

delimiter //
drop table if exists n_table//
create table n_table (id varchar(5), stdate date, enddt date, amt int)//
drop procedure if exists divide_row//
CREATE  PROCEDURE divide_row (o_id varchar(5),end_date date)
BEGIN
declare counter int default 0;
declare d_diff int;
declare c_id varchar(5);
declare c_stdate date;
declare c_amount int;

select id into c_id  from o_table where id=o_id;
select stdate into c_stdate  from o_table where id=o_id;
select amount into c_amount  from o_table where id=o_id;

set d_diff=datediff(end_date,c_stdate);
if d_diff<0 then
    select 'The end_date specified must not be older than the start_date !!' as warning;

else
    lp : loop
    if counter>d_diff then
    leave lp;
    end if;

    insert n_table values (c_id, adddate(c_stdate,counter),end_date, c_amount/(d_diff+1));
    set counter=counter+1;
    end loop lp;
end if;

END //

Please comment on this.

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 blabla_bingo

Related Questions