SQL Server - how to calculate previous month from the available values

hi,

 hi, for my application i am having a table containing 

 Employee_ID    CTC     PD    MONTH    YEAR
   13                 20000   250        09         2013
   20                30000    300        10         2013
   13                25000    300        11         2013

now for any Employee_ID  input i want get the details as below

Employee_ID    CTC     PD    EffectedFrom          EffectedTo
13                 20000   250        09-2013               10-2013
13                 25000   300        11-2013               null
 20                30000    300        10- 2013             null


 

 select EmployeeID, CTC, PD, EffectedFrom,
       dbo.GetLastEffectedToDate(employeeID, effectedFrom) as EffectedTo
where blah blah blah

The UDF (user defined function) you'd need to create "GetLastEffectedToDate" would take the employeeID  and that row's effectedFrom date and run a query to find the next record that met your criteria that had an EffectedFrom date greater than the parameter date passed in.  Otherwise, return null.

Another option would be to use a TABLE variable in your stored procedure.  Load all of the results into by in order by employeeid and your date columns.  Then iterate through the TABLE variable and update various columns based on the last record iterated.

https://www.nullskull.com/a/705/sql-server-table-variables-to-eliminate-cursors.aspx