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 19 (of 19 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy