how to subtract value at previous row from current row?
Table data is like....
------------------------------
EmpID Emp_Name Salary
-------------------------------
1 Mark 4000
2 Twin 2000
3 Aleganja 3000
4 Peter 8000
-------------------------------
Method (1)
-------------------------------------
select top 1 EmpID,SALARY from Employee
union
SELECT e1.EmpID,(e2.Salary - e1.Salary) AS SALARY FROM Employee e1
INNER JOIN Employee e2 ON e2.EmpID = e1.EmpID - 1
Method (2)
-------------------------------------
select EmpID,
(
case (select COUNT(e3.Salary) from Employee as e3 where e3.EmpID < e1.EmpID)
when 0 then e1.Salary
else (select top 1 e2.Salary from Employee as e2 where e2.EmpID < e1.EmpID order by e2.EmpID desc) - e1.Salary
end
)
as
Salary
from Employee as e1 order by EmpID asc
---------------------------------------
------------------------------
EmpID Emp_Name Salary
-------------------------------
1 Mark 4000
2 Twin 2000
3 Aleganja 3000
4 Peter 8000
-------------------------------
Method (1)
-------------------------------------
select top 1 EmpID,SALARY from Employee
union
SELECT e1.EmpID,(e2.Salary - e1.Salary) AS SALARY FROM Employee e1
INNER JOIN Employee e2 ON e2.EmpID = e1.EmpID - 1
Method (2)
-------------------------------------
select EmpID,
(
case (select COUNT(e3.Salary) from Employee as e3 where e3.EmpID < e1.EmpID)
when 0 then e1.Salary
else (select top 1 e2.Salary from Employee as e2 where e2.EmpID < e1.EmpID order by e2.EmpID desc) - e1.Salary
end
)
as
Salary
from Employee as e1 order by EmpID asc
---------------------------------------
Comments
Post a Comment