중간에 빨간줄 윗부분에 입금,지출에 들어가야 될 녀석들(동그라미표시)이 아래쪽으로 밀려나 전일잔고에 들어가 버렸습니다;;
금일 금액인
1,500,000은 입금에 들어가야되고
-1,000,0000은 출금에 들어가야됩니다.
쿼리는 요렇게 짜봤습니다~
A.IPGM_COST에 금일 입금액이
A.CHUL_COST에 금일 출금액이 들어가야 됩니다..ㅠㅠ
select A.IPCH_DATE, A.COST_GUBN, A.IPCH_COST, A.IPGM_COST, A.CHUL_COST, 0 as COST_QNTY, A.MEMO_XXXX
--일자 구분 전일잔고 입금 출금 잔액 비고
from (
---------------------------------------전일 이전잔고
select '00000000' as IPCH_DATE, COST_GUBN, sum(isnull(A.IPGM_COST,0)) as IPCH_COST, 0 as IPGM_COST,
--날자 구분 전일잔고+입금
0 as CHUL_COST, ''as MEMO_XXXX
출금 메모
from(
--잔액
select A.COST_GUBN, sum(isnull(A.COST_QNTY, 0)) as IPGM_COST
--구분 --잔액
from COST_INFO as A
group by A.COST_GUBN
--잔액끝
union all
--전일 입금액-
select A.COST_GUBN, sum(isnull(A.IPGM_COST, 0)) as IPGM_COST
--구분 전일입금액
from IPCH_INFO as A
where A.IPCH_DATE < '20140104'
group by A.COST_GUBN
--전일 입금 끝-
union all
--전일 출금액-
select A.COST_GUBN, sum(isnull(A.CHUL_COST * (-1), 0)) as IPGM_COST
--구분 전일출금액
from IPCH_INFO as A
where A.IPCH_DATE < '20140104'
group by A.COST_GUBN
--전일 출금 끝-
) as A
group by A.COST_GUBN
having sum(isnull(IPGM_COST,0)) <> 0
--------------------------------------전일 이전잔고 끝
union all
--당일 입금액--
select A.IPCH_DATE, A.COST_GUBN, sum(isnull(A.IPGM_COST,0)) as IPGM_COST, 0 As IPCH_COST,
0 as CHUL_COST, A.MEMO_XXXX
--입금날 구분 당일입금금액
출금액 메모
from IPCH_INFO as A
where A.IPCH_DATE = '20140104'
group by A.IPCH_DATE, A.COST_GUBN, A.MEMO_XXXX
--당일 입금 끝--
union all
--당일 출금액--
select A.IPCH_DATE, A.COST_GUBN, sum(isnull(A.CHUL_COST * (-1),0)) as CHUL_COST, 0 As IPCH_COST,
--출고날 구분 당일출금금액
0 as IPGM_COST, A.MEMO_XXXX
입금액 메모
from IPCH_INFO as A
where A.IPCH_DATE = '20140104'
group by A.IPCH_DATE, A.COST_GUBN, A.MEMO_xxxx
--당일 출금 끝--
)as A
글쓰기