Notes

FY changes of KM and MMAL pos


1. save sequential id in comments

update sales
set comments = concat(comments, ' ', sequential_id)
where fy = 2020

2. update sequential id

UPDATE sales
SET sequential_id = tbl.new_seq
FROM sales as s
LEFT OUTER JOIN
(
select fy2019.id as sale_id, fy2019.fy, fy2019.item_category_id, fy2019.original_seq, max_seq.max_seq, (max_seq.max_seq + fy2019.original_seq) as new_seq from
(
select item_category_id, max(sequential_id) as max_seq from sales where fy = 2018 group by item_category_id
) as max_seq
right join
(
select id, fy, item_category_id, sequential_id as original_seq from sales where fy = 2020
) as fy2019
on
fy2019.item_category_id = max_seq.item_category_id
order by fy2019.item_category_id, fy2019.original_seq
) as tbl
ON s.item_category_id = tbl.item_category_id
WHERE s.fy = 2020
and sales.id = tbl.sale_id

// 3. update fy

update sales
set fy = 2018
where fy = 2020

// 4. code changes