Cumulative addition in SQL Server 2008
This is what I got in my SQL SERVER database table where I am trying to calculate an employee's vacation remainder. My actual data:
EmpId EmpName EvalDate OpeningEL EnjoyedEL BalanceEL
12 CHANDRA 2014-04-01 18:30:00.000 0.95 0.00 0.95
12 CHANDRA 2014-05-01 18:30:00.000 1.30 0.00 1.30
12 CHANDRA 2014-06-01 18:30:00.000 1.20 1.00 1.20
12 CHANDRA 2014-07-01 18:30:00.000 1.25 0.00 1.25
12 CHANDRA 2014-08-01 18:30:00.000 1.25 1.00 1.25
But I need the data below.
EmpId EmpName EvalDate OpeningEL EnjoyedEL BalanceEL
12 CHANDRA 2014-04-01 18:30:00.000 0.95 0.00 0.95
12 CHANDRA 2014-05-01 18:30:00.000 2.25 0.00 2.25
12 CHANDRA 2014-06-01 18:30:00.000 3.45 1.00 2.45
12 CHANDRA 2014-07-01 18:30:00.000 3.70 0.00 3.70
12 CHANDRA 2014-08-01 18:30:00.000 4.95 1.00 3.95
The previous balance items are added with the following support services.
So how to achieve this .... Please suggest something.
+3
source to share
1 answer
You can use CROSS APPLY
and GROUP BY
to achieve this
OpeningEL
and BalanceEL
from CROSS APPLY
will get the sum of the current and previous records for the employee.
SELECT
EL1.EmpId,
EL1.EmpName,
EL1.EvalDate,
Temp.OpeningEL,
EL1.EnjoyedEL,
Temp.BalanceEL
FROM EmployeeLeave EL1
CROSS APPLY
(
SELECT
SUM(OpeningEL) as OpeningEL,
SUM(BalanceEL) - SUM(EnjoyedEL) as BalanceEL
FROM EmployeeLeave EL2
WHERE EL2.EmpId = EL1.EmpId
AND EL2.EvalDate <= EL1.EvalDate
)Temp;
0
source to share