Guest User

Untitled

a guest
Jul 17th, 2018
62
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 1.32 KB | None | 0 0
  1. CREATE TABLE [dbo].[tbl_EmployeeLeaveBalance](
  2. [ID] [bigint] NOT NULL,
  3. [EmpCode] [bigint] NOT NULL,
  4. [FiscalYearCode] [smallint] NOT NULL,
  5. [IsYearlyOpeningBalance] [bit] NULL,
  6. [LeaveTypeCode] [tinyint] NULL,
  7. [NoOfLeaves] [numeric](18, 2) NULL,
  8. [Remarks] [nvarchar](2040) NULL
  9. )
  10.  
  11. CREATE PROCEDURE [dbo].[ProcGetLeaveYearEndBalance]
  12. (
  13. @EmpCode bigint,
  14. @LeaveType tinyint,
  15. @FiscalYear smallint,
  16. @Balance int
  17. )
  18. AS
  19. BEGIN
  20.  
  21. INSERT INTO [tbl_EmployeeLeaveBalance] VALUES ((SELECT ISNULL(MAX(ID),0) FROM [tbl_EmployeeLeaveBalance])+1, @EmpCode, @FiscalYear, 1, @LeaveType,
  22. (@Balance+ISNULL((SELECT TOP 1
  23. ISNULL(LEAD(ISNULL(NULLIF(NoOfLeaves, 0), 0))OVER (PARTITION BY EmpCode, LeaveTypeCode ORDER BY [ID]), 0) [Previous]
  24. FROM [tbl_EmployeeLeaveBalance]
  25. WHERE IsYearlyOpeningBalance = 0 AND @EmpCode = EmpCode
  26. ORDER BY [Previous] DESC),0)), 'Test')
  27.  
  28. INSERT INTO [tbl_EmployeeLeaveBalance] VALUES ((SELECT ISNULL(MAX(ID),0) FROM [tbl_EmployeeLeaveBalance])+1, @EmpCode, @FiscalYear, 0, @LeaveType,
  29. (@Balance+ISNULL((SELECT TOP 1
  30. ISNULL(LEAD(ISNULL(NULLIF(NoOfLeaves, 0), 0))OVER (PARTITION BY EmpCode, LeaveTypeCode ORDER BY [ID]), 0) [Previous]
  31. FROM [tbl_EmployeeLeaveBalance]
  32. WHERE IsYearlyOpeningBalance = 0 AND @EmpCode = EmpCode
  33. ORDER BY [Previous] DESC),0)), 'Test')
  34.  
  35. END
Add Comment
Please, Sign In to add comment