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

Post a Comment

Popular posts from this blog

How to create RSS Feed in Asp.Net

using System; using System.Data; using System.Configuration; using System.Data.SqlClient; using System.Text; using System.Xml; public partial class _Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { string connectionString = ConfigurationManager.ConnectionStrings["TestConnection"].ConnectionString; DataTable dt = new DataTable(); SqlConnection conn = new SqlConnection(connectionString); using (conn) { SqlDataAdapter ad = new SqlDataAdapter("SELECT * from tblRSS", conn); ad.Fill(dt); } Response.Clear(); Response.ContentType = "text/xml"; XmlTextWriter TextWriter = new XmlTextWriter(Response.OutputStream, Encoding.UTF8); TextWriter.WriteStartDocument(); //Below tags are mandatory rss tag TextWriter.WriteStartElement("rss"); TextWriter.WriteAttributeString("versio...
Read more

Auto generate change scripts in SQL Server Management Studio SSMS for tables

Image
Problem As a part of my best practices, I always save the T-SQL scripts used for creation and modification of objects in SQL Server. When creating and modifying tables using SQL Server Management Studio designer it is easy to right click in the designer and select "Generate Change Script...", but is there a way to automatically script the creation and/or modification of tables made through of SQL Server Management Studio (SSMS) designer?  This tip shows you an option that exists within SSMS to automatically generate these scripts for all table changes when using the table designer. Solution Within SQL Server Management Studio (SSMS) there is an option to automatically generate scripts for your changes done through the GUI.  The solution for this problem is provided in SSMS and works with both SQL Server 2005 and SQL Server 2008, but by default this option that we will discuss is disabled. To enable the option From the SSMS menus click on "Tools"  ...
Read more

HOW TO Use the NumericUpDown Control

HOW TO Use the NumericUpDown Control  Great help taken from  http://www.asp.net/ajaxlibrary/HOW%20TO%20Use%20the%20NumericUpDown%20Control.ashx NumericUpDown Demonstration NumericUpDown Description NumericUpDown is an ASP.NET AJAX extender that can be attached to an ASP.NET TextBox control to add "up" and "down" buttons that increment and decrement the value in the TextBox. The increment and decrement can be simple +1/-1 arithmetic, they can cycle through a provided list of values (like the months of the year), or they can call a Web Service to determine the next value. Page authors can also provide custom images to be used instead of the default up/down button graphics. Video - How Do I: Use the ASP.NET AJAX NumericUpDown Control? NumericUpDown Client Code Sample view plain copy to clipboard print ? < script   runat = "server" >        [System.Web.Services.W...
Read more