How to add the missing months name to the sql table in sequence by checking the previous month name?-CodePudding

I want to add the missing months to the table. If the current row having missing month then it should check the previous row and the name of the month in that row and add the next month in the current row. For eg: the current month is null, it should check the month name in previous row, if the previous row having January then the current month should replace null with February, for eg. if the current month is null, it should check the month name in previous row having August then next null month name should be replaced with September.

Code for the creating table:

CREATE TABLE IF NOT EXISTS missing_months (
    `Cust_id` INT,
    `Month` VARCHAR(9) CHARACTER SET utf8,
    `Sales_value` INT
);
INSERT INTO missing_months VALUES
    (1,'Janurary',224),
    (2,'February',224),
    (3,NULL,239),
    (4,'April',205),
    (5,NULL,218),
    (6,'June',201),
    (7,NULL,205),
    (8,'August',246),
    (9,NULL,218),
    (10,NULL,211),
    (11,'November',223),
    (12,'December',211);

output is:

 Cust_id    Month     Sales_value
    1       Janurary    224
    2       February    224
    3       null        239
    4       April       205
    5       null        218
    6       June        201
    7       null        205
    8       August      246
    9       null        218
    10      null        211
    11      November    223
    12      December    211

BUT I WANT THE OUTPUT LIKE:

   Cust_id  Month       Sales_value
    1       Janurary    224
    2       Febrauary   224
    3       March       239
    4       April       205
    5       May         218
    6       June        201
    7       July        205
    8       August      246
    9       September   218
    10      October     211
    11      November    223
    12      December    211

CodePudding user response：

update missing_months m
join missing_months prev on prev.Cust_id=m.Cust_id-1
set m.Month=date_format(str_to_date(concat(prev.Month,'-1970-01'),'%M-%Y-%d')   interval 1 month,'%M')
where m.Month is null
order by m.Cust_id

But relying on an identifier field for order is bad; if your data is ordered, you should have some other column indicating what the order is.

CodePudding user response：

select Cust_id, monthname(STR_TO_DATE(rn, '%m')) as Month_Name, 
Sales_value 
from
        (Select Cust_id, Month, row_number() over() as rn,
        Sales_value 
        from missing_month) x;