sout_day frhouse_id tot
----------- --------------- -------------
20150901 002 1000
20150902 002 5000
20150903 002 3000
일자별 금액에서 정상판매금액과 반품판매 금액이 계산된 금액입니다.
* 일자별 작성 Query
select Z. sout_day, Z. frhouse_id, sum(Z. amt) as tot
from (
select sout_day, frhouse_id, sum(net_price) as amt ----> 정상금액입니다.
from tbl_saleout
where sout_day >='20150901'
and sout_day <='20150930'
and frhouse_id = '002'
and kind_id ='00' ----------------> 정상판매코드입니다
group by sout_day, frhouse_id
union
select sout_day, frhouse_id, sum(net_price * -1) as amt ---->반품금액입니다
from tbl_saleout
where sout_day >='20150901'
and sout_day <='20150930'
and frhouse_id = '002'
and kind_id ='05' -------------> 반품판매코드 입니다
group by sout_day, frhouse_id ) Z
where Z. sout_day >='20150901'
and Z. sout_day <='20150930'
and Z. frhouse_id ='002'
group by Z. sout_day, Z. frhouse_id
order by Z. sout_day
Comment 4
-
건우아빠
2015.10.28 12:39
-
슈토파이터
2015.10.28 13:24
답글 감사 합니다...한가지더 질문 드리겠습니다
월계를 해당 데이터 밑에 표시 하려면 어찌 하나요?
"예"
sout_ day frhouse_id amt
------------ -------------- -------------
20150901 002 2000
20150902 002 3000
월계 002 5000
-
건우아빠
2015.10.28 13:31
2000이라 월별 집계를 union 으로 붙이시면 됩니다.
select *
from (
select sout_day, frhouse_id, sum( case kind_id when '00' then net_price when '05' then net_price * -1 end ) as amt , 1 sort
from tbl_saleout
where sout_day >='20150901'
and sout_day <='20150930'
and frhouse_id = '002'
and kind_id in ( '00' ,'05' )
group by sout_day , frhouse_idunion all
select left(Z.sout_day,6) 월 , Z.frhouse_id, sum(Z.amt) as tot , 2 sort
from (
select sout_day, frhouse_id, sum( case kind_id when '00' then net_price when '05' then net_price * -1 end ) as amt .
from tbl_saleout
where sout_day >='20150901'
and sout_day <='20150930'
and frhouse_id = '002'
and kind_id in ( '00' ,'05' )
group by sout_day, frhouse_id
) Z
group by left(Z.sout_day,6) , Z.frhouse_id
) as rorder by frhouse_id , sort , sout_day
-
슈토파이터
2015.10.28 14:06
감사드립니다...행복하세요
select left(Z.sout_day,6) 월 , Z.frhouse_id, sum(Z.amt) as tot
from (
select sout_day, frhouse_id
, sum( case kind_id when '00' then net_price when '05' then net_price * -1 end ) as amt
from tbl_saleout
where sout_day >='20150901'
and sout_day <='20150930'
and frhouse_id = '002'
and kind_id in ( '00' ,'05' )
group by sout_day, frhouse_id
) Z
group by left(Z.sout_day,6) , Z.frhouse_id
order by left(Z.sout_day,6)