SQLTeam.com | Weblogs | Forums

Getting debit cedit and balance issue

tsql
sql2012
sql2008
sql2008r2

#1

I am using the below query to the following information from table statement

`SELECT t.[InvoiceID], t.S_Type as Type,
t.Date, t.Debit, t.Credit, b.Balance
FROM Statement as t CROSS apply
(SELECT Balance = SUM(Debit) - SUM(Credit)
FROM Statement as x
WHERE (x.date < t.date or
x.date = t.date
) AND
x.CustID = t.CustID
) b
WHERE t.CustID ='48' and date between '2015-01-01' and '2016-01-01'
ORDER BY t.date

out put

`InvoiceID   Type                Date    Debit   Credit  Balance
51         Service Invoice  2015-08-29  500.00  0.00    500.00
51         Receipt Voucher  2015-09-07  0.00    500.00  0.00
76         Service Invoice  2015-09-28  1000.00 0.00    1500.00
208        Sales Invoice    2015-09-28  500.00  0.00    1500.00
119        Sales Invoice    2015-10-31  500.00  0.00    2000.00
76         Receipt Voucher  2015-11-21  0.00    500.00  500.00
208        Receipt Voucher  2015-11-21  0.00    500.00  500.00
119        Receipt Voucher  2015-11-21  0.00    500.00  500.00
165        Service Invoice  2015-12-01  500.00  0.00    1000.00
165        Receipt Voucher  2015-12-22  0.00    500.00  500.00
224        Service Invoice  2015-12-31  500.00  0.00    1000.00`

First how can i get each Receipt Voucher under it invoice

Second when i have Receipt Voucher with same date of invoice how i
can be sure it gets under Invoice using order by type to get balance
correct values

the expected out put
order invoices by date and get it Receipt Voucher under it
InvoiceID Type Date Debit Credit Balance 51 Service Invoice 2015-08-29 500.00 0.00 500.00 51 Receipt Voucher 2015-09-07 0.00 500.00 0.00 76 Service Invoice 2015-09-28 1000.00 0.00 1000.00 76 Receipt Voucher 2015-11-21 0.00 500.00 500.00 208 Sales Invoice 2015-09-28 500.00 0.00 1000.00 208 Receipt Voucher 2015-11-21 0.00 500.00 500.00 119 Sales Invoice 2015-10-31 500.00 0.00 1000.00 119 Receipt Voucher 2015-11-21 0.00 500.00 500.00 165 Service Invoice 2015-12-01 500.00 0.00 1000.00 165 Receipt Voucher 2015-12-22 0.00 500.00 500.00 224 Service Invoice 2015-12-31 500.00 0.00 1000.00
`


#2

didn't you already got the answer over at stackexchange ?

unless that is not you and this is a homework / assignment question ?