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
---------------------------------------
 

Comments

Popular posts from this blog

Executing PowerShell scripts from C#

HOW TO Use the NumericUpDown Control

Exposing Agile Software Development Myths