Query to get the previous row from the row selected

Transact-SQL
1

Hi
I have payroll detail table in which I have payroll details id and an employee id (foreign key from employee table).
My requirement is, when a row is selected for a particular employee, I want to get the value from a the previous row.
For example if I have 3 employees and each have a 3 payrolls with payrolldetail id's and If i choose the payroll details id for employee 2, it should display the details of payroll detail id of employee 2.

Can some one please let me know how to write a query or solve this within a stored procedure
It is kind of urgent requirement, greatly appreciate your help.

2

What your asking is a bit confused and unclear.

Are you asking "I want to return all of the PayrollDetail records for any specified EmployeeID"?

3

Not All the payrolldetail records but the previous payroll record for a specific employee id, if I am on a 3rd payroll, and hit the payroll record, the beginning vacation balance should get from my previous record to show how many vacation leaves I still have or accrued

Now I am using the following query but it is returning the same results for all the employees

select D.ESST_Total_Worked_Hours
,D.Accrue_Sick_Balance
,D.Accrue_Vacation_Balance
,D.Accrue_Comp_Balance
,D.Accrue_ESST_Balance
from tblPayroll_Detail D
where D.Payroll_Detail_ID =(
select top 1 MIN(Payroll_Detail_ID)
OVER (ORDER BY Payroll_Detail_ID ROWS BETWEEN 1 PRECEDING AND 1 PRECEDING) AS PrevPayrollDetailID
FROM tblPayroll_Detail where Employee_ID = 1)
4

This might work, but it would be better if the parts involving Payroll_Detail_ID were actually based on a DATE rather than an ID. If you have a column like Payroll_Date you should use that instead.

 SELECT payr.Employee_ID
    ,   prev.ESST_Total_Worked_Hours
    ,   prev.Accrue_Sick_Balance
    ,   prev.Accrue_Vacation_Balance
    ,   prev.Accrue_Comp_Balance
    ,   prev.Accrue_ESST_Balance
FROM    tblPayroll_Detail payr
OUTER APPLY
        (   SELECT TOP (1)
                   pd.ESST_Total_Worked_Hours
               ,   pd.Accrue_Sick_Balance
               ,   pd.Accrue_Vacation_Balance
               ,   pd.Accrue_Comp_Balance
               ,   pd.Accrue_ESST_Balance
           WHERE   pd.Employee_ID = payr.Employee_ID
               AND pd.Payroll_Detail_ID < payr.Payroll_Detail_ID
           ORDER BY pd.Payroll_Detail_ID DESC
        ) AS prev
WHERE   payr.Employee_ID = 1
;
5

What is this pd, I understood prev and payr.

6

Apologies, I somehow deleted a line...

 SELECT payr.Employee_ID
    ,   prev.ESST_Total_Worked_Hours
    ,   prev.Accrue_Sick_Balance
    ,   prev.Accrue_Vacation_Balance
    ,   prev.Accrue_Comp_Balance
    ,   prev.Accrue_ESST_Balance
FROM    tblPayroll_Detail payr
OUTER APPLY
        (   SELECT TOP (1)
                   pd.ESST_Total_Worked_Hours
               ,   pd.Accrue_Sick_Balance
               ,   pd.Accrue_Vacation_Balance
               ,   pd.Accrue_Comp_Balance
               ,   pd.Accrue_ESST_Balance
           FROM    tblPayroll_Detail pd  -- this line
           WHERE   pd.Employee_ID = payr.Employee_ID
               AND pd.Payroll_Detail_ID < payr.Payroll_Detail_ID
           ORDER BY pd.Payroll_Detail_ID DESC
        ) AS prev
WHERE   payr.Employee_ID = 1
;
7

No, it is not working as expected, but I figured out on my own and here is the code, this will help others who has similar problem

IF OBJECT_ID('tempdb.dbo.#temptbl', 'U') IS NOT NULL
DROP TABLE #temptbl
CREATE TABLE #temptbl (tmppayrollID int)
IF OBJECT_ID('tempdb.dbo.#tmptblPrevpayrollDtid', 'U') IS NOT NULL
DROP TABLE #tmptblPrevpayrollDtid
SELECT
Payroll_Detail_ID as value_prev
into #tmptblPrevpayrollDtid FROM tblPayroll_Detail where Payroll_Detail_ID < @PayrollDetail_ID and Employee_ID = Employee_ID

;with cte (payrolldetail_id) as (
select max(value_prev) as payrolldet from #tmptblPrevpayrollDtid
)
insert into #temptbl
select payrolldetail_id
from cte
select Payroll_Detail_ID,Employee_ID, Accrue_Sick_Balance,Accrue_Vacation_Balance,
Accrue_Comp_Balance,Accrue_ESST_Balance,ESST_Total_Worked_Hours
from tblPayroll_Detail where Payroll_Detail_ID =(select tmppayrollID from #temptbl)

8

Thank you so much for jumping in