CODEWITHSUNDEEP
sqlsql-serversql-server-2014cumulative-sum
HariEko
HariEko

Reputation: 397

get calculation result from two columns in sql server

Please refer to this table below.

 |RefNbr   | DocDate     | OrigAmt    | AdjAmt     | Balances |
 |INV001   | 2016-03-15  | 5,000.00   |   250.00   | 4,750.00 |
 |INV002   | 2016-03-16  | 5,000.00   |   750.00   | 4,000.00 |
 |INV003   | 2016-03-17  | 5,000.00   | 1,000.00   | 3,000.00 | 
 |INV004   | 2016-03-19  | 5,000.00   |   500.00   | 2,500.00 |

how to provide query to get value of balances ? (Balances = OrigAmt - AdjAmt (this rule only for the first row), and then in second row, Balances = Prev Balances (balances in first row) - AdjAmt, and etc).

Upvotes: 0

Views: 222

Answers (2)

Mahesh.K
Mahesh.K

Reputation: 901

Try below codes it may help you little .

**

CREATE TABLE #TAB(REFNBR VARCHAR(MAX),DOCDATE DATETIME ,ORIGAMT DECIMAL(18,2),ADJAMT DECIMAL(18,2))
 INSERT INTO #TAB VALUES ('INV001','2016-03-15',5000.00,250.00),('INV002','2016-03-16',5000.00,750.00),
 ('INV003','2016-03-17',5000.00,1000.00),('INV004','2016-03-19',5000.00,500.00)     


            ;WITH CTE AS (
SELECT REFNBR, 
       DOCDATE,
       ORIGAMT,
       ADJAMT,
       ORIGAMT-ADJAMT AS BALANCE, 
       ROW_NUMBER() OVER ( ORDER BY DOCDATE) AS RN
FROM #TAB)
SELECT a.REFNBR,
       a.DOCDATE,
       a.ORIGAMT,
       a.ADJAMT,
       CASE WHEN ISNULL(LAG(a.BALANCE + ISNULL(x.ADDS,0)) OVER (ORDER BY a.RN),0) + a.ORIGAMT - a.ADJAMT < 0 
            THEN 0
            ELSE a.BALANCE + ISNULL(x.ADDS,0)
       END AS FINAL_BALANCE
FROM CTE a
CROSS APPLY (SELECT SUM(BALANCE) AS ADDS 
                FROM CTE f 
                    WHERE f.REFNBR = a.REFNBR AND f.RN < a.RN
                    ) x

**

The above code is for 2014 for less than 2014 try below code once

                SELECT REFNBR,
       DocDate,
       OrigAmt,
       AdjAmt,
       CASE
         WHEN RNO > 1 THEN Sum(OrigAmt - ADJAMT)
                             OVER(
                               PARTITION BY REFNBR
                               ORDER BY RNO)
         ELSE Iif(( OrigAmt - ADJAMT ) < 0, 0, OrigAmt - ADJAMT)
       END
FROM   (SELECT *,
               Row_number()
                 OVER(
                   PARTITION BY REFNBR
                   ORDER BY DocDate) AS RNO
        FROM   #TAB) A

Upvotes: 0

Pரதீப்
Pரதீப்

Reputation: 93724

Here is one way using windowed aggregate function 

select OrigAmt - sum(AdjAmt) over(order by  DocDate asc) as Balances 
From yourtable

For anything less than sql server 2012 use this

SELECT OrigAmt - cum_sum AS Balances
FROM   yourtable a
       CROSS apply (SELECT Sum(AdjAmt)
                    FROM   yourtable b
                    WHERE  b.DocDate <= a.DocDate) cs( cum_sum)

Upvotes: 3

Related Questions