Vacation Accrual

gistfile1.sql

USE [HRData]
GO
/****** Object:  StoredProcedure [dbo].[usp_Calculate_PTOAccrual]    Script Date: 02/27/2015 10:46:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[usp_Calculate_PTOAccrual] (
	@UserID INT = NULL
	,@EverestUserID VARCHAR(50) = NULL
	)
	
		-- =============================================
	-- Author:		Eric Gu
	-- Create date: 5/19/2014
	-- Description:	Calculates Employee PTO Accrual
	-- Modifications : 
	-- =============================================
	
AS
BEGIN
	SET NOCOUNT ON

	DECLARE @AsOfDate DATE = GETDATE()
	DECLARE @2WEEKRATE FLOAT = 0.833;
	DECLARE @3WEEKRATE FLOAT = 1.25;

			
	SET @EverestUserID = CASE 
			WHEN @EverestUserID IS NULL
				THEN 'SQL/Admin'
			ELSE @EverestUserID
			END;		

	IF OBJECT_ID('tempdb..#PTOAccrual') IS NOT NULL
		DROP TABLE #PTOAccrual;

	CREATE TABLE #PTOAccrual (
		UserID INT
		,AsOfDate DATE
		,PriorYearCarryForwardDays FLOAT
		,Date3WeekPTOAccrualStarts DATE
		,DateOfTermination DATE
		,DatePTOAccrualStarts DATE
		,PTOAccrualCeiling FLOAT
		,StartOfYear DATE
		,DayOfTermination INT
		,NumberOfDaysTerminationMonth INT
		,MonthsAt3WeekRate INT
		,MonthsAt2WeekRate INT
		,DaysAt3WeekRate FLOAT
		,DaysAt2WeekRate FLOAT
		,TotalAccrued FLOAT
		,PTODaysTaken FLOAT
		,UnadjustedPTO FLOAT
		,AdjustedPTO FLOAT
		,DaysAccruedAfterTermination FLOAT
		,DaysOwedToEmployee FLOAT
		);

	WITH TotalPTOUsed
	AS (
		SELECT PTO.UserID
			,SUM(COALESCE(PTO.NumOfDays, 0)) PTODaysTaken
		FROM dbo.PTO PTO
		WHERE PTO.IsDeleted = 0
		GROUP BY PTO.UserID
		)
	INSERT INTO #PTOAccrual
	SELECT Employees.UserID
		,CASE 
			WHEN YEAR(DateOfTermination) = YEAR(DateOfTermination)
				AND DateOfTermination IS NOT NULL
				AND DateOfTermination <= @AsOfDate
				THEN DateOfTermination
			ELSE @AsOfDate
			END AsOfDate
		,PriorYearCarryForwardDays
		,Date3WeekPTOAccrualStarts
		,DateOfTermination
		,DatePTOAccrualStarts
		,PTOAccrualCeiling
		,DATEADD(yy, DATEDIFF(yy, 0, @AsOfDate), 0) StartOfYear
		,DAY(DateOfTermination) DayOfTermination
		,DAY(DATEADD(s, - 1, DATEADD(mm, DATEDIFF(m, 0, DateOfTermination) + 1, 0))) NumberOfDaysTerminationMonth
		,NULL MonthsAt3WeekRate
		,NULL MonthsAt2WeekRate
		,NULL DaysAt3WeekRate
		,NULL DaysAt2WeekRate
		,NULL TotalAccrued
		,COALESCE(TotalPTOUsed.PTODaysTaken, 0) PTODaysTaken
		,NULL UnadjustedPTO
		,NULL AdjustedPTO
		,NULL DaysAccruedAfterTermination
		,NULL DaysOwedToEmployee
	FROM dbo.ADUsers Employees
	LEFT JOIN TotalPTOUsed
		ON TotalPTOUsed.UserID = Employees.UserID
	WHERE IsActive = 1
		AND (
			@UserID IS NULL
			OR @UserID = Employees.UserID
			)

	-- ==========================================================================================================================		
	--BEGIN CALCULATION OF MONTHLY ACCRUAL
	-- ==========================================================================================================================	
	UPDATE #PTOAccrual
	SET MonthsAt3WeekRate = CASE 
			--SENIOR EMPLOYEES: Date3WeekPTOAccrualStarts > AsOfDate: Qualify
			WHEN YEAR(AsOfDate) > YEAR(Date3WeekPTOAccrualStarts)
				THEN (
						CASE 
							--IF PTO Accrual Start Date > StartOfYear THEN 
							WHEN (DatePTOAccrualStarts) > (StartOfYear)
								THEN MONTH(AsOfDate) - MONTH(DatePTOAccrualStarts) + 1
									--ALL OTHER EMPLOYEES
							ELSE MONTH(AsOfDate)
							END
						)
					--EMPLOYEES @ TWO YEAR MARK: BASE MONTH MAY BE HYBRID
			WHEN YEAR(Date3WeekPTOAccrualStarts) = YEAR(@AsOfDate)
				THEN (
						CASE 
							--IF CURRENT MONTH > 3 WEEK ANNIVERSARY, BASE MONTH = MONTHS ELIGIBLE FOR 3 WEEK ACCRUAL
							WHEN AsOfDate >= Date3WeekPTOAccrualStarts
								THEN MONTH(AsOfDate) - MONTH(Date3WeekPTOAccrualStarts) + 1
									--IF CURRENT MONTH < 3 WEEK ANNIVERSARY, BASE MONTH = 0
							ELSE 0
							END
						)
					--EMPLOYEES NOT ELIGIBLE FOR 3 WEEKS
			ELSE 0
			END
		,MonthsAt2WeekRate = CASE 
			--SENIOR EMPLOYEES: BASE MONTH = 0; THEY DO NO ACCRUE
			WHEN YEAR(AsOfDate) > YEAR(Date3WeekPTOAccrualStarts)
				THEN 0
					--EMPLOYEES @ TWO YEAR MARK: BASE MONTH MAY BE HYBRID
			WHEN YEAR(Date3WeekPTOAccrualStarts) = YEAR(AsOfDate)
				THEN (
						CASE 
							--IF CURRENT MONTH > TWO YEAR ANNIVERSARY, BASE MONTH = MONTHS ELIGIBLE FOR 2 WEEK ACCRUAL
							WHEN AsOfDate > Date3WeekPTOAccrualStarts
								THEN (
										CASE 
											WHEN DatePTOAccrualStarts >= StartOfYear
												THEN MONTH(Date3WeekPTOAccrualStarts) - MONTH(DatePTOAccrualStarts) - 1
											ELSE MONTH(Date3WeekPTOAccrualStarts) - 1
											END
										)
							ELSE (
									CASE 
										--IF PTO Accrual Start Date > StartOfYear THEN 
										WHEN DatePTOAccrualStarts >= StartOfYear
											THEN MONTH(AsOfDate) - MONTH(DatePTOAccrualStarts)
												--ALL OTHER EMPLOYEES
										ELSE MONTH(AsOfDate)
										END
									)
							END
						)
					--ALL OTHER EMPLOYEES
			ELSE (
					CASE 
						--IF PTO Accrual Start Date > StartOfYear THEN 
						WHEN DatePTOAccrualStarts >= StartOfYear
							THEN MONTH(AsOfDate) - MONTH(DatePTOAccrualStarts)
								--ALL OTHER EMPLOYEES
						ELSE MONTH(AsOfDate)
						END
					)
			END
			
	-- ==========================================================================================================================		
	--CONVERT ALL NEGATIVE MONTHS TO 0 AND CALCULATE DAYS
	-- ==========================================================================================================================	

	UPDATE #PTOAccrual
	SET MonthsAt3WeekRate = CASE 
			WHEN MonthsAt3WeekRate < 0
				THEN 0
			ELSE MonthsAt3WeekRate
			END
		,MonthsAt2WeekRate = CASE 
			WHEN MonthsAt2WeekRate < 0
				THEN 0
			ELSE MonthsAt2WeekRate
			END
		,DaysAt3WeekRate = CASE 
			WHEN MonthsAt3WeekRate < 0
				THEN 0
			ELSE MonthsAt3WeekRate * @3WEEKRATE
			END
		,DaysAt2WeekRate = CASE 
			WHEN MonthsAt2WeekRate < 0
				THEN 0
			ELSE MonthsAt2WeekRate * @2WEEKRATE
			END

	-- ==========================================================================================================================		
	--CALCULATION OF DAYS ACCRUED AFTER TERMINATION 
	-- ==========================================================================================================================
	
	UPDATE #PTOAccrual
	SET DaysAccruedAfterTermination = CASE 
			WHEN AsOfDate > Date3WeekPTOAccrualStarts
				THEN ((CAST((NumberOfDaysTerminationMonth - DayOfTermination) AS FLOAT) / COALESCE(NumberOfDaysTerminationMonth, 0)) * @3WEEKRATE)
			ELSE ((CAST((NumberOfDaysTerminationMonth - DayOfTermination) AS FLOAT) / COALESCE(NumberOfDaysTerminationMonth, 0)) * @2WEEKRATE)
			END
	WHERE DateOfTermination IS NOT NULL
		AND YEAR(DateOfTermination) = YEAR(AsOfDate)
		AND DateOfTermination <= AsOfDate
		AND COALESCE(DaysAt2WeekRate + DaysAt3WeekRate, 0) > 0

	--Termination Calculation is only relevant for:
	--1. Termination Date falls in the Current Year (Do not calculate historically)
	--2. Termination Date must have transpired
	--3. Must have Accrued Days in the Current Year 
	
	--CALCULATION OF TOTAL PTO ACCRUAL BEFORE ACCRUAL CEILING
	UPDATE #PTOAccrual
	SET --Total Accrued = PriorYearCarryForward + DaysAt3WeekRate + DaysAt2WeekRate
		TotalAccrued = ROUND((PriorYearCarryForwardDays + DaysAt3WeekRate + DaysAt2WeekRate) * 2, 0) / 2
		--Unadjusted PTO = Total PTO - PTO Days Taken
		,UnadjustedPTO = ROUND((PriorYearCarryForwardDays + DaysAt3WeekRate + DaysAt2WeekRate - ISNULL(PTODaysTaken, 0)) * 2, 0) / 2

	--CALCULATION OF TOTAL PTO ACCRUAL AFTER ACCRUAL CEILING
	UPDATE #PTOAccrual
	SET --Adjusted Accrued = Unadjusted PTO  + DaysAt3WeekRate + DaysAt2WeekRate
		AdjustedPTO = CASE 
			WHEN PTOAccrualCeiling < UnadjustedPTO
				THEN PTOAccrualCeiling
			ELSE UnadjustedPTO
			END

	--CALCULATION OF DAYS OWED TO TERMINATED EMPLOYEE
	UPDATE #PTOAccrual
	SET DaysOwedToEmployee = CASE 
			--Termination Calculation is only relevant for:
			--1. Termination Date falls in the Current Year (Do not calculate historically)
			--2. Termination Date must have transpired
			WHEN YEAR(DateOfTermination) = YEAR(AsOfDate)
				AND DateOfTermination <= AsOfDate
				THEN --Net PTO Balance Less Days Owed for Partial Last Month = Days Owed to Terminated Employee
					COALESCE(AdjustedPTO - ISNULL(DaysAccruedAfterTermination, 0), 0)
			ELSE NULL
			END



	-- ==========================================================================================================================		
	-- COPY EXISTING RECORDS INTO AUDIT TABLE 
	-- ==========================================================================================================================
 INSERT INTO [Audit].[PTOSummary]
        (
          UserID
        , [AsOfDate]
        , [CurrentYearBeginning]
        , [AccruedToDate]
        , MonthsAt3WeekRate
        , MonthsAt2WeekRate
        , DaysAt3WeekRate
        , DaysAt2WeekRate
        , [PTODaysTaken]
        , [PTODaysRemaining]
        , [DaysAccruedAfterTermination]
        , [DaysOwedToEmployee]
        , [ModifiedBy]
        , [ModifiedDate]
        , [CreatedBy]
        , [CreatedDate]
        )
        SELECT  a.UserID
              , a.[AsOfDate]
              , a.[CurrentYearBeginning]
              , a.[AccruedToDate]
              , a.MonthsAt3WeekRate
              , a.MonthsAt2WeekRate
              , a.DaysAt3WeekRate
              , a.DaysAt2WeekRate
              , a.[PTODaysTaken]
              , a.[PTODaysRemaining]
              , a.[DaysAccruedAfterTermination]
              , a.[DaysOwedToEmployee]
              , a.[ModifiedBy]
              , a.[ModifiedDate]
              , a.[CreatedBy]
              , a.[CreatedDate]
        FROM    [PTOSummary] a
                INNER JOIN #PTOAccrual b ON a.UserID = b.UserID
                                            AND a.AsOfDate = b.AsOfDate

	-- ==========================================================================================================================		
	-- DELETE EXISTING RECORDS FROM CURRENT TABLE
	-- ==========================================================================================================================					
 DELETE FROM [dbo].[PTOSummary]
 FROM   [dbo].[PTOSummary] a
        INNER JOIN #PTOAccrual b ON a.UserID = b.UserID
                                    AND a.AsOfDate = b.AsOfDate
	
	-- ==========================================================================================================================		
	-- INSERT NEW RECORDS INTO CURRENT TABLE
	-- ==========================================================================================================================
 INSERT INTO dbo.[PTOSummary]
        (
          UserID
        , [AsOfDate]
        , [CurrentYearBeginning]
        , [AccruedToDate]
        , MonthsAt3WeekRate
        , MonthsAt2WeekRate
        , DaysAt3WeekRate
        , DaysAt2WeekRate
        , [PTODaysTaken]
        , [PTODaysRemaining]
        , [DaysAccruedAfterTermination]
        , [DaysOwedToEmployee]
        , [ModifiedBy]
        , [ModifiedDate]
        , [CreatedBy]
        , [CreatedDate]
        )
        SELECT  UserID
              , @AsOfDate
              , PriorYearCarryForwardDays
              , TotalAccrued
              , MonthsAt3WeekRate
              , MonthsAt2WeekRate
              , DaysAt3WeekRate
              , DaysAt2WeekRate
              , PTODaysTaken
              , AdjustedPTO
              , DaysAccruedAfterTermination
              , DaysOwedToEmployee
              , @EverestUserID
              , GETDATE()
              , @EverestUserID
              , GETDATE()
        FROM    #PTOAccrual 

END