Calculating Column Depending On Previous Rows

Question

The table in the link below:

enter image description here

Тhe Result column which is the result of a T-SQL query in which each record reflects the movement of a чproduct-modelч from an чemployeeч to another through a bill: if the amount is moved from the employee, the amount will be filled in the Amount_Out column and vise versa.

For example: rows 3 and 4 show that 40 pieces of the product hp moved from emp1 to emp3 through the bill with code 111. This movement reduces the total amount of this product for emp1 to be 60, and increases the total amount of the same product for emp3 to be 40.

Now I need to calculate in the query the result in each record to show the total amount of a product in an employee's custody until the date in the record:

Result = The amount calculated for all previous bills + Amount_In - Amount_Out

which means the result will depend on previous records' values. How could this be achieved by the query in SQL server 2008? and which is the best to achieve it, stored procedure or view?

Can you please edit your question and post the table in as text so that we can use it? — StuartLC, Apr 06 '15 at 08:03
Sounds like a running total. If that's the case, there's plenty of examples available. — James Z, Apr 06 '15 at 08:08

score 0 · Accepted Answer · answered Apr 06 '15 at 08:31

You can do this with correlated subquery. Change AND it.No <= ot.No to AND it.Date <= ot.Date if Nos are not sequential:

DECLARE @t TABLE
    (
      No INT ,
      Employee CHAR(4) ,
      Model CHAR(4) ,
      Date DATE ,
      Bill CHAR(3) ,
      Income MONEY ,
      Outcome MONEY
    )

INSERT  INTO @t
VALUES  ( 1, 'emp1', 'hp', '20140101', '000', 100, 0 ),
        ( 2, 'emp1', 'dell', '20140101', '000', 100, 0 ),
        ( 3, 'emp1', 'hp', '20140308', '111', 0, 40 ),
        ( 4, 'emp3', 'hp', '20140308', '111', 40, 0 ),
        ( 5, 'emp3', 'hp', '20140408', '222', 0, 20 ),
        ( 6, 'emp2', 'hp', '20140408', '222', 20, 0 ),
        ( 7, 'emp1', 'hp', '20140608', '333', 0, 5 ),
        ( 8, 'emp2', 'hp', '20140608', '333', 5, 0 ),
        ( 9, 'emp1', 'dell', '20150101', '444', 0, 40 )


SELECT  * ,
        ( SELECT    SUM(Income - Outcome)
          FROM      @t it
          WHERE     it.Model = ot.Model
                    AND it.Employee = ot.Employee
                    AND it.No <= ot.No
        ) AS Result
FROM    @t ot

Output:

No  Employee    Model   Date        Bill    Income  Outcome Result
1   emp1        hp      2014-01-01  000     100.00  0.00    100.00
2   emp1        dell    2014-01-01  000     100.00  0.00    100.00
3   emp1        hp      2014-03-08  111     0.00    40.00   60.00
4   emp3        hp      2014-03-08  111     40.00   0.00    40.00
5   emp3        hp      2014-04-08  222     0.00    20.00   20.00
6   emp2        hp      2014-04-08  222     20.00   0.00    20.00
7   emp1        hp      2014-06-08  333     0.00    5.00    55.00
8   emp2        hp      2014-06-08  333     5.00    0.00    25.00
9   emp1        dell    2015-01-01  444     0.00    40.00   60.00

score 0 · Answer 2 · edited May 23 '17 at 12:13

Sql 2008 doesn't have access to the additional analytic functions in Sql2012, such as LEAD and LAG, so you'll need to resort to a workaround such as one of these here

Here's an example of the recursive CTE implementation.

Note that this is dependent on being able to number the consecutive movements in the Employee, ProductModel groupings incrementally (1,2,3 etc) without any ambiguity, since this is used in the JOIN condition (Sequence to Sequence + 1). You will get erratic results if there is a tie, for instance.

WITH RankedByEmpProduct AS
(
    SELECT No, Employee, Product_Model, Date, Bill_Code, Amount_In, 
           Amount_Out, Amount_In-Amount_Out AS Nett,
           DENSE_RANK() OVER (PARTITION BY Employee, Product_Model 
           ORDER BY No ASC) AS EmpModelSeq
    FROM ProductModel
),
RecursiveRunningTotal AS 
(
    SELECT No, Employee, Product_Model, Date, Bill_Code, Amount_In, 
           Amount_Out, EmpModelSeq, Amount_In-Amount_Out AS Nett, 
           Amount_In-Amount_Out AS RunningTotal
    FROM RankedByEmpProduct rep
    WHERE EmpModelSeq = 1

    UNION ALL

    SELECT rep.No, rep.Employee, rep.Product_Model, rep.Date, rep.Bill_Code, 
           rep.Amount_In, rep.Amount_Out, rep.EmpModelSeq, 
           rep.Amount_In-rep.Amount_Out AS Nett, 
           rrt.RunningTotal + rep.Amount_In - rep.Amount_Out AS RunningTotal
    FROM RankedByEmpProduct rep
        INNER JOIN RecursiveRunningTotal rrt
        ON rep.Employee = rrt.Employee AND rep.Product_Model = rrt.Product_Model 
        AND rep.EmpModelSeq = rrt.EmpModelSeq + 1
)
SELECT * FROM RecursiveRunningTotal
ORDER BY No ASC;

SqlFiddle here

score 0 · Answer 3 · answered Apr 06 '15 at 08:50

You can get the Result by a simple subquery.

SELECT No,Employee,Product_Model,[Date],Bill_Code,Amount_In,Amount_Out,
(
    SELECT SUM
    (
        SELECT D1.Amount_In - D0.Amount_Out AS subTotal
        FROM Table_Name AS D1
        WHERE 
        (
            D1.[Date] < D0.[Date]
        )
        OR
        (
            D1.[Date] = D0.[Date]
            AND 
            D1.No <= D0.No
        )
    )
) AS Result
FROM Table_Name D0
ORDER BY D0.No, D0.[Date]

And the actual output should be：

No  Employee    Product_Model   Date        Bill_Code    Amount_In  Amount_Out   Result
1   emp1        hp              2014-01-01  000          100.00     0.00         100.00
2   emp1        dell            2014-01-01  000          100.00     0.00         200.00
3   emp1        hp              2014-03-08  111          0.00       40.00        160.00
4   emp3        hp              2014-03-08  111          40.00      0.00         200.00
5   emp3        hp              2014-04-08  222          0.00       20.00        180.00
6   emp2        hp              2014-04-08  222          20.00      0.00         200.00
7   emp1        hp              2014-06-08  333          0.00       5.00         195.00
8   emp2        hp              2014-06-08  333          5.00       0.00         200.00
9   emp1        dell            2015-01-01  444          0.00       40.00        160.00

Calculating Column Depending On Previous Rows

3 Answers3