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
`