Query to get the previous row from the row selected
-
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 payroll detail 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.
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
I tried with all below, but none works for me
CREATE PROCEDURE [dbo].[uspGetSickVacCompESSTPreviousPayroll]
@Employee_ID int = NULL
AS
BEGIN
SET NOCOUNT ON;
declare @ss table (ss datetime)
INSERT INTO @ss
select p.Check_Date from dbo.tblPayroll P where P.Payroll_ID in (
select d.Payroll_ID from dbo.tblPayroll_Detail D
where Employee_ID = @Employee_ID)
select D.Accrue_Sick_Balance as 'Accrue_Sick_Balance',Accrue_Vacation_Balance as 'Accrue_Vacation_Balance'
,Accrue_Comp_Balance as 'Accrue_Comp_Balance',Accrue_ESST_Balance as 'Accrue_ESST_Balance' from dbo.tblPayroll_Detail D
INNER JOIN
dbo.tblPayroll R
ON D.Payroll_ID = R.Payroll_ID
AND R.Check_Date = (
select max(ss) from @ss
where ss Not In (select Max(ss) from @ss))
END
select D.Accrue_Sick_Balance as 'Accrue_Sick_Balance',Accrue_Vacation_Balance as 'Accrue_Vacation_Balance'
,Accrue_Comp_Balance as 'Accrue_Comp_Balance',Accrue_ESST_Balance as 'Accrue_ESST_Balance' from dbo.tblPayroll_Detail D
INNER JOIN
dbo.tblPayroll R
ON D.Payroll_ID IN (select Payroll_ID from dbo.tblPayroll)
where R.Check_Date = (
select max(ss) from @ss
where ss Not In (select Max(ss) from @ss))
SELECT name FROM tempdb.sys.tables WHERE name like '#SS%'
drop table #SS
create table #SS (ss datetime)
INSERT INTO #ss
select p.Check_Date
from dbo.tblPayroll P
where P.Payroll_ID in (
select d.Payroll_ID
from dbo.tblPayroll_Detail D
where Employee_ID = 1
AND Payroll_ID =3)
select D.Accrue_Sick_Balance as 'Accrue_Sick_Balance'
,Accrue_Vacation_Balance as 'Accrue_Vacation_Balance'
,Accrue_Comp_Balance as 'Accrue_Comp_Balance'
,Accrue_ESST_Balance as 'Accrue_ESST_Balance'
from dbo.tblPayroll_Detail D
INNER JOIN
dbo.tblPayroll R
ON D.Payroll_ID = R.Payroll_ID
AND R.Check_Date = (
select max(ss)
from #SS
where ss Not In (
select Max(ss)
from #ss))
CREATE PROCEDURE [dbo].[uspGetSickVacCompESSTCurrentPayroll]
@Employee_ID int = NULL,
@PayrollDetail_ID int = NULL
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
select D.Accrue_Sick_Balance as 'Accrue_Sick_Balance'
,D.Accrue_Vacation_Balance as 'Accrue_Vacation_Balance'
,D.Accrue_Comp_Balance as 'Accrue_Comp_Balance'
,D.Accrue_ESST_Balance as 'Accrue_ESST_Balance'
from dbo.tblPayroll_Detail D
INNER JOIN
dbo.tblEmployee E
ON E.Employee_ID = D.Employee_ID
AND E.Employee_ID = @Employee_ID
AND D.Payroll_Detail_ID = @PayrollDetail_ID
END
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);
-
Will the lag window function help?
https://learn.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver16
-
LAG() basically does what you're describing - goes back N rows in the window.
-
Thank you for the quick response.
I am not sure how to use the results to get the row what I need, here is my result set. When I hit the row with payroll detail id 3, I should see the accrue sick balance of payroll detail id 1 for employee id 1
-
So for payroll id of 3 (employeeID 1 ) you want to get data from the record payroll_detail_id 1 ? Do you want to get all records for that employee id or just the previous one? Can you supply a create table / insert of some sample data.
-
Yes, I just need the previous one alone and not all the previous records. When I hit the row in the front end with payroll Detail ID 3 of employee ID 1, I should get the sick balance of 2.00 and not NULL, similarly if I hit the payroll Detail ID 2 of employee ID 2, I should get an empty result set (just the previous row) and not 0.00.
-
Something like this, perhaps.
PrevSickBalance = LAG(Accrue_Sick_Balance,1,0) OVER (Partition By Employee_ID ORDER BY Payroll_Detail_ID)
The absence of evidence is not evidence of absence.
Martin ReesYou can lead a horse to water, but a pencil must be lead.
Stan Laurel -
Ok, makes sense. I think you need to partition by employee_id:
drop table if exists #t
select 1 as payroll_detail_id, 1 as payroll_id, 1 as employee_id, 2 as accrue_sick_balance, 5 as accrue_vacation_balance
into #t
union all
select 2 as payroll_detail_id, 2 as payroll_id, 2 as employee_id, 0 as accrue_sick_balance, 0 as accrue_vacation_balance
union all
select 3 as payroll_detail_id, 3 as payroll_id, 1 as employee_id, NULL as accrue_sick_balance, NULL as accrue_vacation_balance
SELECT *, LAG(accrue_sick_balance) OVER (PARTITION BY employee_ID ORDER BY payroll_detail_id ) AS prev_accrue_sick_balance
,LAG(accrue_vacation_balance) OVER (PARTITION BY employee_ID ORDER BY payroll_detail_id ) AS prev_accrue_vacation_balance
FROM #t
ORDER BY payroll_detail_id -
I tried with Lag, but it returns more than one row from which I don't know how extract the data of the previous row and use it.
Here is what I did
SELECT LAG(Accrue_Sick_Balance,1,0) OVER (PARTITION BY employee_ID ORDER BY payroll_detail_id ) AS prev_accrue_sick_balance
from tblPayroll_Detail
-
Hi Jmetape, This is the same case where I am getting more than one row and I don't know how to extract the row (alone) from the stored procedure
-
I am totally new to programming and SQL, please someone help me with the logic to extract the resulting columns into a select query so that I can pass it on to my application using a stored procedure or user defined function
-
Please appreciate we are all volunteers here with our own jobs, hobbies, families, etc. This is a relatively complicated concept for someone new to sql. You may need to put this code into your SQL editor of choice and understand it each step of the way....
Try this--put the top 2 records for that employee in a cte (common table expression). Order by payroll_detail_id desc, along with a row_num column and the lag statements. Select from the cte where row_num = 1:
drop table if exists #t
select 1 as payroll_detail_id, 1 as payroll_id, 1 as employee_id, 2 as accrue_sick_balance, 5 as accrue_vacation_balance
into #t
union all
select 2 as payroll_detail_id, 2 as payroll_id, 2 as employee_id, 0 as accrue_sick_balance, 0 as accrue_vacation_balance
union all
select 3 as payroll_detail_id, 3 as payroll_id, 1 as employee_id, NULL as accrue_sick_balance, NULL as accrue_vacation_balance
declare @employee_id int = 1
;with cte as (
select top 2 ROW_NUMBER() OVER (ORDER BY payroll_detail_id desc) row_num,LAG(accrue_sick_balance) OVER (PARTITION BY employee_ID ORDER BY payroll_detail_id ) AS prev_accrue_sick_balance
,LAG(accrue_vacation_balance) OVER (PARTITION BY employee_ID ORDER BY payroll_detail_id ) AS prev_accrue_vacation_balance,*
FROM #t WHERE employee_id = @employee_id
ORDER BY payroll_detail_id desc
)
select *
from cte
WHERE row_num = 1 -
Thank you Very much for all the help, greatly appreciate your timely help, I will try to code and will you know, I was busy working on other module, hence I could not respond yesterday.
-
By the way drop table if exists is throwing error while compiling
-
The above result set is always giving me the last but one row, whatever the row I select from my application, but I need the last but one row from the row that I currently selected
Above query is giving me an incorrect results
Sorry to say that... Please see below
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply