Q:

SQL - Calculate 2 columns and view result to another to column

Hi I have this query where I can get my customer balance to be use as reporting later. But I am kinda stuck on this part where the 2 columns should calculate and view the result to another column the harder part is they are on the different row. So here's my query 
SELECT ROW_NUMBER() OVER( ORDER BY DR.id )                  AS 'NO.' ,          
       CONVERT(VARCHAR(13), CAST(DR.doc_date AS DATE), 100)  AS 'DOC DATE',
       v.which AS 'DOC TYPE',
        CASE WHEN 
            v.which IN ('NET TOTAL', 'DELL/COL/CHARGE') THEN V.val
            END AS DEBIT,
        CASE WHEN 
            v.which IN ('CDW', 'PAYMENT') THEN V.val
            END AS CREDIT,
        CAST(v.val AS float) + CAST(v.val AS float)  AS 'BALANCE'

FROM [dbo].[doc_customer] DC 
LEFT JOIN [dbo].[doc_rent] DR ON DR.doc_sourced_customer_id = DC.id 
LEFT JOIN [dbo].[slip_rent] SR ON SR.doc_sourced_doc_rent_id = DR.id 
LEFT JOIN [dbo].[slip_rent_payment] SP ON SP.doc_sourced_rent_id = DR.id OUTER APPLY
     (VALUES ('NET TOTAL', SR.net_total),
             ('DELL/COL/CHARGE', SR.dell_col_charge),
             ('CDW', SR.CDW),
             ('PAYMENT', sp.amount)
     ) v(which, val)
WHERE DC.id = '1-1---1-1-1---1--1~1' 
AND DR.deleted = 0
AND DR.void = 0
AND v.val IS NOT NULL 
AND v.val <> '' ;

As u can see in my query I can get the DEBIT and CREDIT value but my problem is 
on how to get total of their calculation.

My Desired Output
  | NO. | DOC DATE    | DOC TYPE  | DEBIT | CREDIT | BALANCE 
-------------------------------------------------------------
1 | 1   | Mar 28 2017 | NET TOTAL | 341   | NULL   | 
2 | 2   | Mar 28 2017 | PAYMENT   | NULL  | 4000   | -3659
3 | 3   | Oct 16 2017 | NET TOTAL | 150   | NULL   | -3509
4 | 4   | Oct 16 2017 | CDW       | NULL  | 50     | -3559
5 | 5   | Oct 16 2017 | PAYMENT   | NULL  | 150    | -3709

Here's the link for my desired output sql desired output

A:

if this is SQL Server 2012 or higher, you want ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW like this (notice you need an indexer, so I chose [No.] :
SELECT [NO.], Debit, Credit, 
  SUM(coalesce(Debit,0) - coalesce(Credit,0)) OVER(ORDER BY [NO.] 
     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 
          AS BALANCE
FROM
(
SELECT ROW_NUMBER() OVER( ORDER BY DR.id )                  AS 'NO.' ,          
       CONVERT(VARCHAR(13), CAST(DR.doc_date AS DATE), 100)  AS 'DOC DATE',
       v.which AS 'DOC TYPE',
        CASE WHEN 
            v.which IN ('NET TOTAL', 'DELL/COL/CHARGE') THEN V.val
            END AS DEBIT,
        CASE WHEN 
            v.which IN ('CDW', 'PAYMENT') THEN V.val
            END AS CREDIT,
        CAST(v.val AS float) + CAST(v.val AS float)  AS 'BALANCE'

FROM [dbo].[doc_customer] DC 
LEFT JOIN [dbo].[doc_rent] DR ON DR.doc_sourced_customer_id = DC.id 
LEFT JOIN [dbo].[slip_rent] SR ON SR.doc_sourced_doc_rent_id = DR.id 
LEFT JOIN [dbo].[slip_rent_payment] SP ON SP.doc_sourced_rent_id = DR.id OUTER APPLY
     (VALUES ('NET TOTAL', SR.net_total),
             ('DELL/COL/CHARGE', SR.dell_col_charge),
             ('CDW', SR.CDW),
             ('PAYMENT', sp.amount)
     ) v(which, val)
WHERE DC.id = '1-1---1-1-1---1--1~1' 
AND DR.deleted = 0
AND DR.void = 0
AND v.val IS NOT NULL 
AND v.val <> ''
) a

