-- 전월 재고 -- insert into dbt_list_sum ( created_on, list_token, h_id, id, c1, c2, c3, d1, d2, d3, d4, d5, c4, c5, order_by ) select unix_timestamp(), 'a', mx.id, mx.id, item_code, item_name, sub_name, bal_qty, 0, 0, 0, 0, igroup_name, igroup_code, item_code from ( select id, item_code, item_name, sub_name, igroup_id from dbr_item as itm where item_code between '!' and '駾' and isnt_stkio = '0' and is_material = '0' ) as mx inner join ( select item_id, bal_qty from dbr_bal_item where yyyy_mm = _prev_month('20230201') and storage_id = 1 ) as bal on mx.id = bal.item_id inner join ( select id, igroup_code, igroup_name from dbr_igroup where igroup_code between '!' and '駾' ) as igr on mx.igroup_id = igr.id order by item_code asc; -- 당월 수불 합계 -- insert into dbt_list_sum ( created_on, list_token, h_id, id, c1, c2, c3, d1, d2, d3, d4, d5, c4, c5, order_by ) select unix_timestamp(), 'a', mx.id, mx.id, item_code, item_name, sub_name, 0, sum(io_qty*cast((stock_status+1)/2 as int)), sum(io_qty*cast(-1*(stock_status-1)/2 as int)), 0, sum(io_qty*badstk_status), igroup_name, igroup_code, item_code from ( select id, item_code, item_name, sub_name, igroup_id from dbr_item as itm where item_code between '!' and '駾' and isnt_stkio = '0' and is_material = '0' ) as mx inner join ( select item_id, io_qty, stock_status, badstk_status from dbr_stock_io as stk inner join dbr_deal_type as deal on deal.id = stk.deal_type_id where io_date between '20230201' and '20230231' and storage_id = 1 ) as stk on mx.id = stk.item_id inner join ( select id, igroup_code, igroup_name from dbr_igroup where igroup_code between '!' and '駾' ) as igr on mx.igroup_id = igr.id group by stk.item_id order by item_code asc; -- dbt_list_type1 으로 집계 -- insert into dbt_list_type1 ( created_on, list_token, h_id, id, c1, c2, c3, d1, d2, d3, d4, d5, c4, c5, order_by) select min(unix_timestamp()), min('a'), min(id), min(id), min(c1), min(c2), min(c2), sum(d1), sum(d2), sum(d3), sum(d1) + sum(d2) - sum(d3), sum(d5), min(c4), min(c5), min(order_by) from dbt_list_sum as mx where list_token = 'a' group by mx.id having min(mx.id) is not null order by order_by asc; ======= ======= >>>>>>> 62bdebd054e19dd2c2ce53384ce4fe759d13dd29 insert into dbt_list_type1 ( created_on, list_token, h_id, id, c1, c2, c3, d1, d2, d3, d4, d5, c4, c5 ) select unix_timestamp(), 'a', mx.id, mx.id, item_code, item_name, sub_name, bal_qty, 0, 0, 0, 0, igroup_name, igroup_code <<<<<<< HEAD ======= from ( select id, item_code, item_name, sub_name, igroup_id from dbr_item as itm where itm.item_code between '!' and '駾' and ( isnt_stkio = '0' or is_material = '0' ) ) as mx inner join ( select item_id, bal_qty from dbr_bal_item where yyyy_mm = _prev_month('20230201') and storage_id = 1 ) as last_mbal on last_mbal.item_id = mx.id inner join ( select id, igroup_code, igroup_name from dbr_igroup as igr where igr.igroup_code between '!' and '駾' ) as igr on igr.id = mx.igroup_id order by item_code asc; -- 당월 수불합계 -- insert into dbt_list_type1 ( created_on, list_token, h_id, id, c1, c2, c3, d1, d2, d3, d4, d5, c4, c5 ) select unix_timestamp(), 'a', mx.id, mx.id, item_code, item_name, sub_name, 0, sum(io_qty*cast((stock_status+1)/2 as int)), sum(io_qty*cast(-1*(stock_status-1)/2 as int)), 0, sum(io_qty*badstk_status), igroup_name, igroup_code from ( select id, item_code, item_name, sub_name, igroup_id from dbr_item as itm where itm.item_code between '!' and '駾' and ( isnt_stkio = '0' or is_material = '0' ) ) as mx inner join ( select item_id, deal_type_id, io_qty from dbr_stock_io where io_date between '20230201' and '20230231' and storage_id = 1 ) as stk on stk.item_id = mx.id inner join ( select id, igroup_code, igroup_name from dbr_igroup as igr where igr.igroup_code between '!' and '駾' ) as igr on igr.id = mx.igroup_id inner join dbr_deal_type as deal on deal.id = stk.deal_type_id order by item_code asc; from ( select id, item_code, item_name, sub_name, igroup_id from dbr_item as itm where itm.item_code between '!' and '駾' and ( isnt_stkio = '0' or is_material = '0' ) ) as mx inner join ( select item_id, bal_qty from dbr_bal_item where yyyy_mm = _prev_month('20230201') and storage_id = 1 ) as last_mbal on last_mbal.item_id = mx.id inner join ( select id, igroup_code, igroup_name from dbr_igroup as igr where igr.igroup_code between '!' and '駾' ) as igr on igr.id = mx.igroup_id order by item_code asc; -- 당월 수불합계 -- insert into dbt_list_type1 ( created_on, list_token, h_id, id, c1, c2, c3, d1, d2, d3, d4, d5, c4, c5 ) select unix_timestamp(), 'a', mx.id, mx.id, item_code, item_name, sub_name, 0, sum(io_qty*cast((stock_status+1)/2 as int)), sum(io_qty*cast(-1*(stock_status-1)/2 as int)), 0, sum(io_qty*badstk_status), igroup_name, igroup_code from ( select id, item_code, item_name, sub_name, igroup_id from dbr_item as itm where itm.item_code between '!' and '駾' and ( isnt_stkio = '0' or is_material = '0' ) ) as mx inner join ( select item_id, deal_type_id, io_qty from dbr_stock_io where io_date between '20230201' and '20230231' and storage_id = 1 ) as stk on stk.item_id = mx.id inner join ( select id, igroup_code, igroup_name from dbr_igroup as igr where igr.igroup_code between '!' and '駾' ) as igr on igr.id = mx.igroup_id inner join dbr_deal_type as deal on deal.id = stk.deal_type_id order by item_code asc; >>>>>>> a12a75b5e2b8584ff6dcd2fe635e566613dfec2c