Sql-server – Sql getting debit, credit and balance issue

running-totalssql serversql-server-2008

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

Best Answer

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
Related Question