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;