Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE [dbo].[tbl_EmployeeLeaveBalance](
- [ID] [bigint] NOT NULL,
- [EmpCode] [bigint] NOT NULL,
- [FiscalYearCode] [smallint] NOT NULL,
- [IsYearlyOpeningBalance] [bit] NULL,
- [LeaveTypeCode] [tinyint] NULL,
- [NoOfLeaves] [numeric](18, 2) NULL,
- [Remarks] [nvarchar](2040) NULL
- )
- CREATE PROCEDURE [dbo].[ProcGetLeaveYearEndBalance]
- (
- @EmpCode bigint,
- @LeaveType tinyint,
- @FiscalYear smallint,
- @Balance int
- )
- AS
- BEGIN
- INSERT INTO [tbl_EmployeeLeaveBalance] VALUES ((SELECT ISNULL(MAX(ID),0) FROM [tbl_EmployeeLeaveBalance])+1, @EmpCode, @FiscalYear, 1, @LeaveType,
- (@Balance+ISNULL((SELECT TOP 1
- ISNULL(LEAD(ISNULL(NULLIF(NoOfLeaves, 0), 0))OVER (PARTITION BY EmpCode, LeaveTypeCode ORDER BY [ID]), 0) [Previous]
- FROM [tbl_EmployeeLeaveBalance]
- WHERE IsYearlyOpeningBalance = 0 AND @EmpCode = EmpCode
- ORDER BY [Previous] DESC),0)), 'Test')
- INSERT INTO [tbl_EmployeeLeaveBalance] VALUES ((SELECT ISNULL(MAX(ID),0) FROM [tbl_EmployeeLeaveBalance])+1, @EmpCode, @FiscalYear, 0, @LeaveType,
- (@Balance+ISNULL((SELECT TOP 1
- ISNULL(LEAD(ISNULL(NULLIF(NoOfLeaves, 0), 0))OVER (PARTITION BY EmpCode, LeaveTypeCode ORDER BY [ID]), 0) [Previous]
- FROM [tbl_EmployeeLeaveBalance]
- WHERE IsYearlyOpeningBalance = 0 AND @EmpCode = EmpCode
- ORDER BY [Previous] DESC),0)), 'Test')
- END
Add Comment
Please, Sign In to add comment