useful-qry/z-매입집계표.sql

-- 전월 재고 --
-- 당월 수불 합계 --
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;
>>>>>>> 62bdebd054e19dd2c2ce53384ce4fe759d13dd29

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