0

I am using the query below to get Debit/Credit Balance 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

output

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 its invoice?

Second, when I have the Receipt Voucher with same date as the invoice how can I be sure it shows under the Invoice using order by type to get correct values for the balance?

The expected output - invoices ordered by date and its 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
0

2 Answers 2

3

The main thing you need is to generate the required order sequence using row_number().

seq = row_number() over 
(
  partition by t.CustID
  order by t.InvoiceID, 
           t.Date,
           CASE WHEN t.S_Type = 'Receipt Voucher' THEN 1 ELSE 2 END
 )

than use it for calculation of the cumulative balance

;

WITH cte
AS (
    SELECT  CustID,
        [InvoiceID],
        S_Type,
        DATE,
        Debit,
        Credit,
        seq = row_number() OVER (
            PARTITION BY CustID
            ORDER BY InvoiceID,
                DATE,
                CASE 
                    WHEN S_Type = 'Receipt Voucher'
                        THEN 1
                    ELSE 2
                    END
            )
    FROM Statement
    )
SELECT c.[InvoiceID],
    c.S_Type AS Type,
    c.DATE,
    .Debit,
    c.Credit,
    b.Balance
FROM cte c
CROSS APPLY (
    SELECT Balance = SUM(Debit) - SUM(Credit)
    FROM cte AS x
    WHERE x.CustID = c.CustID
        AND x.seq <= c.seq
    ) b
WHERE c.CustID = '48'
    AND DATE BETWEEN '2015-01-01'
        AND '2016-01-01'
ORDER BY seq
1
  • 1
    This answer does not seem to provide the wanted output. Jan 13, 2016 at 9:10
0

I am not near a computer with any database on it to try this idea; however looking at your "expected output", the invoice or sales number is an incrementing number (assuming invoice 208 is really a typo and should be invoice 108). Have you tried 'Order by InvoiceID ASC, Debit Desc'? It will keep payments for each invoice under the invoice. Another possibility is something like 'Order by invoiceID, if debit>0 then 0 else 1 endif, date'.

3
  • Based on the desired output posted, the OP doesn't just want to change the sorting for the output – they want the cumulative balance to follow the same sorting as well.
    – Andriy M
    Jan 14, 2016 at 8:27
  • I don't agree with @AndriyM; the running balance is based on "owed" (invoices) minus "payments" (receipts) to date as of each transaction. I'm kinda confused by sorting by cumulative balance?
    – GDD
    Jan 14, 2016 at 12:59
  • @GDD: Well, yes, absolutely, running totals/balances are typically calculated following the order of events as they were happening. The desired output in this case, however, clearly indicates a different order. It's probably easier to see starting with InvoiceID 76: +1000 (Balance=1000), -500 (Balance=1000-500=500), +500 (this is a new invoice now but Balance=1000, meaning it includes the previous balance of 500). I'm with you in that it's very unusual and I'm ready to assume that the OP may be confused, but still, that's the sorting specified so far.
    – Andriy M
    Jan 14, 2016 at 13:13

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.