December 15, 2023 at 9:04 pm
I resolved the problem on my own, but thank you all very much who jumped in to help me.
I am posting this code to help others if anyone is looking for 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)
December 15, 2023 at 10:53 pm
I resolved the problem on my own, but thank you all for jumping in to help me.
I want to post this code so that if anyone else has similar problem, will help them get through
CREATE PROCEDURE [dbo].[uspGetSickVacCompESSTPreviousPayroll]
@Employee_ID int = NULL,
@PayrollDetail_ID int = NULL
AS
BEGIN
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)
END
December 15, 2023 at 10:54 pm
ALTER PROCEDURE [dbo].[uspGetSickVacCompESSTPreviousPayroll]
@Employee_ID int = NULL,
@PayrollDetail_ID int = NULL
AS
BEGIN
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)
END
December 15, 2023 at 11:25 pm
Looking at your first stored procedure you might be able to get what you need by selecting the top 1 row where employee_id = @employee_id and payroll_detail_id < @payroll_detail_id. If all the data you need is from one row then you don't need a lag function for every column, you just need to identify the row. It might be safer to use the payroll date for identifying the previous payroll. The PayrollDetail_ID is probably in the same order as the payroll dates, but I doubt it's guaranteed.
CREATE PROCEDURE [dbo].[uspGetSickVacCompESSTCurrentPayroll] @Employee_ID int = NULL, @PayrollDetail_ID int = NULL
AS
select top (1) D.Accrue_Sick_Balance as 'Prev_Accrue_Sick_Balance'
,D.Accrue_Vacation_Balance as 'Prev_Accrue_Vacation_Balance'
,D.Accrue_Comp_Balance as 'Prev_Accrue_Comp_Balance'
,D.Accrue_ESST_Balance as 'Prev_Accrue_ESST_Balance'
from dbo.tblPayroll_Detail AS D
INNER JOIN dbo.tblEmployee AS E ON E.Employee_ID = D.Employee_ID
WHERE E.Employee_ID = @Employee_ID
AND D.Payroll_Detail_ID < @PayrollDetail_ID
ORDER BY D.PayrollDetail_ID DESC
If you want to make it work using lag functions you need allow the lag functions to read data from previous rows before you limit the payroll_detail_id. Once you return a single row there is no previous row to read. If you pass in the employee_id you can read the data for that employee and apply the lag function to every row. By nesting this initial select in a cte you can then filter on payroll_detail_id to return a single row to which the lag functions have been applied. Again it may be safer to order the lag function by payroll date, assuming it is available.
--drop table if exists #t
create table #t
(
payroll_detail_id int identity(1,1),
payroll_id int,
employee_id int,
accrue_sick_balance int,
accrue_vacation_balance int
)
INSERT #t VALUES (1,1,2,5), (2,2,0,0), (3,1,null,null)
declare @employee_id int = 1,
@payroll_id int = 3;
with emp_data as
(
select payroll_detail_id, payroll_id, employee_id,
accrue_vacation_balance, accrue_sick_balance,
lag(accrue_sick_balance,1) over (partition by employee_id order by payroll_detail_id ) as prev_sick_balance,
lag(accrue_vacation_balance,1) over (partition by employee_id order by payroll_detail_id ) as prev_vacation_balance
FROM #t
where employee_id = @employee_id
)
select *
from emp_data
where payroll_detail_id = @payroll_id
Viewing 4 posts - 16 through 18 (of 18 total)
You must be logged in to reply to this topic. Login to reply