|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2014-04-21 : 06:40:15
|
Haven't tested this but should be close.
;with cte1 as
(
select VoucherNo = ReceiptNo ,
VoucherDate = ReceiptDate ,
CreditAmount = 0.00 ,
DebitAmount = Amount
from Receipt
union all
select VoucherNo = InvoiceNo ,
VoucherDate = InvoiceDate ,
CreditAmount = Amount ,
DebitAmount = 0.00
from Invoice
),
cte2 as
(select *, seq = row_number() over (order by VoucherDate) from cte1)
cte3 as
(
select VoucherNo, VoucherDate, CreditAmount, DebitAmount,
Balance = CreditAmount - DebitAmount, seq = 1
from cte2 where seq = 1
union all
select cte2.VoucherNo, cte2.VoucherDate, cte2.CreditAmount, cte2.DebitAmount,
Balance = cte3.Balance + cte2.CreditAmount - cte2.DebitAmount, seq = cte3.seq + 1
from cte3,cte2 where cte3.seq + 1 = cte2.seq
)
select VoucherNo, VoucherDate, CreditAmount, DebitAmount,
Balance = case when Balance < 0 then Balance * -1 else Balance end ,
case when Balance < 0 then 'DR' else 'CR' end
from cte3
order by seq
==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy. |
 |
|
Create union qry