CREATE Procedure [dbo].[USP_Reports_EOYearStatus]
@AppraisalTypeID INT
AS
BEGIN
SET NOCOUNT ON
DECLARE @Year INT
SET @Year = (
SELECT Top 1 CAST (SUBSTRING(Title, 1, 4) AS INT)
FROM AppraisalTypes
WHERE AppraisalTypeID = @AppraisalTypeID
)
DECLARE @midYearStart DATE = CAST(CONCAT(@Year, '-05-01') AS DATE);
DECLARE @midYearEnd DATE = CAST(CONCAT(@Year, '-09-30') AS DATE);
DECLARE @endYearStart DATE = DATEADD(DAY, 1, @midYearEnd);
DECLARE @endYearEnd DATE = DATEADD(YEAR, 1, @midYearStart);
SELECT
Distinct P.ProfileID, P.AppraisalID, P.EmployeeNumber, P.EmployeeName,
P.DivisionID, D.DisplayName AS Division, P.CountryID, C.CountryName, P.[Location],
P.DateHired,A.EmployeeSignDate, M.EmployeeName AS Manager, M.EmployeeNumber AS MgrNumber, A.ManagerSignDate as ManagerSignDate,
MM.EmployeeName AS MgrsMgrName, MM.EmployeeNumber AS MgrsMgrNumber, A.ManagersManagerSignDate as ManagersManagerSignDate,
A.AppraisalID, AA.AppraisalTypeID, AA.Title,
CASE
WHEN ISNULL(A.SelfAssessmentComplete, '') = '' OR A.SelfAssessmentComplete = 0 THEN 'Employee Pending'
WHEN A.SelfAssessmentComplete = 1 AND (A.ManagerStepComplete = 0 OR ISNULL(A.ManagerStepComplete, '') = '') THEN 'Manager Pending'
WHEN A.ManagerStepComplete = 1 AND (A.EmployeeName IS NULL OR A.EmployeeSignDate IS NULL) THEN 'Employee Sign-off Pending'
WHEN A.ManagerStepComplete = 1 AND A.EmployeeName IS NOT NULL AND A.EmployeeSignDate IS NOT NULL
AND (A.ManagerName IS NULL OR A.ManagerSignDate IS NULL) THEN 'Manager Sign-off Pending'
WHEN A.ManagerStepComplete = 1 AND A.EmployeeName IS NOT NULL AND A.EmployeeSignDate IS NOT NULL
AND A.ManagerName IS NOT NULL AND A.ManagerSignDate IS NOT NULL AND (A.ManagersManagerName IS NULL OR A.ManagersManagerSignDate IS NULL) THEN 'Manager''s manager Sign-off Pending'
END AS EOYearStatus,
J.[Description] AS JobFamily, P.Email, H.ASSIGNMENT_STATUS, H.TERMINATIONDATE, H.DIRECT_INDIRECT, AR.[Description] AS Rating,
@Year AS AppraisalYear,
NULL AS ModifiedOn
FROM
Profiles P
JOIN tbl_HRITMasterData H ON H.EMPLOYEE_NUMBER = P.EmployeeNumber
JOIN JobFamily J ON J.Id = P.JobFamilyID
JOIN Appraisals A ON A.AppraisalID = P.AppraisalID
JOIN AppraisalRatings AR ON AR.Id = A.OverallRating
JOIN AppraisalTypes AA ON A.AppraisalTypeID = AA.AppraisalTypeID
JOIN [dbo].[vw_BusinessUnits] D ON D.BusinessUnitID = P.DivisionID
JOIN [dbo].[vw_Countries] C ON C.CountryID = P.CountryID
JOIN Profiles M ON P.ManagerID = M.ProfileID
JOIN Profiles MM ON M.ManagerID = MM.ProfileID
WHERE A.AppraisalTypeID = @AppraisalTypeID
--AND A.AppraisalID = 8444
AND P.TerminationDate IS NULL
AND H.DIRECT_INDIRECT = 'indirect'
AND P.Networkid NOT IN ('removed','Duplicate','pdecker','deveritt')
AND P.Networkid NOT Like '%Term_Duplicate%'
AND H.EMPLOYEE_NUMBER NOT IN ('103098', '78568', '34358')
UNION
SELECT
Distinct P.ProfileID, P.AppraisalID, P.EmployeeNumber, P.EmployeeName,
P.DivisionID, D.DisplayName AS Division, P.CountryID, C.CountryName, P.[Location],
P.DateHired,A.EmployeeSignDate, M.EmployeeName AS Manager, M.EmployeeNumber AS MgrNumber,A.ManagerSignDate as ManagerSignDate,
MM.EmployeeName AS MgrsMgrName, MM.EmployeeNumber AS MgrsMgrNumber,A.ManagersManagerSignDate as ManagersManagerSignDate,
A.OriginalAppraisalID AS AppraisalID, AA.AppraisalTypeID, AA.Title,
--'Completed' AS EOYearStatus
CASE
WHEN A.EmployeeName = 'HR Close' OR A.ManagerName = 'HR Close' OR A.ManagersManagerName = 'HR Close' THEN 'HR Close'
ELSE 'Completed'
END AS EOYearStatus,
J.[Description] AS JobFamily, P.Email, H.ASSIGNMENT_STATUS, H.TERMINATIONDATE, H.DIRECT_INDIRECT, AR.[Description] AS Rating,
@Year AS AppraisalYear, CASE WHEN A.EmployeeName = 'HR Close' OR A.ManagerName = 'HR Close' OR A.ManagersManagerName = 'HR Close' THEN A.ModifiedOn ELSE NULL END AS ModifiedOn
FROM
Profiles P
JOIN tbl_HRITMasterData H ON H.EMPLOYEE_NUMBER = P.EmployeeNumber
JOIN JobFamily J ON J.Id = P.JobFamilyID
JOIN Archive.Appraisals A ON A.OriginalAppraisalID = P.AppraisalID
JOIN AppraisalRatings AR ON AR.Id = A.OverallRating
JOIN AppraisalTypes AA ON A.AppraisalType = AA.Title
JOIN [dbo].[vw_BusinessUnits] D ON D.BusinessUnitID = P.DivisionID
JOIN [dbo].[vw_Countries] C ON C.CountryID = P.CountryID
JOIN Profiles M ON P.ManagerID = M.ProfileID
JOIN Profiles MM ON M.ManagerID = MM.ProfileID
WHERE AA.AppraisalTypeID = @AppraisalTypeID
AND H.DIRECT_INDIRECT = 'indirect'
AND P.Networkid NOT IN ('removed','Duplicate','pdecker','deveritt')
AND P.Networkid NOT Like '%Term_Duplicate%'
AND H.EMPLOYEE_NUMBER NOT IN ('103098', '78568', '34358')
UNION
SELECT
Distinct P.ProfileID, P.AppraisalID, P.EmployeeNumber, P.EmployeeName,
P.DivisionID, D.DisplayName AS Division, P.CountryID, C.CountryName, P.[Location],
P.DateHired,NULL as EmployeeSignDate, M.EmployeeName AS Manager, M.EmployeeNumber AS MgrNumber,NULL as ManagerSignDate,
MM.EmployeeName AS MgrsMgrName, MM.EmployeeNumber AS MgrsMgrNumber,NULL as ManagersManagerSignDate,
NULL AppraisalID, NULL AppraisalTypeID, NULL Title,
'Ineligible' AS EOYearStatus,
J.[Description] AS JobFamily, P.Email, H.ASSIGNMENT_STATUS, H.TERMINATIONDATE, H.DIRECT_INDIRECT, NULL AS Rating,
@Year AS AppraisalYear,NULL AS ModifiedOn
FROM
Profiles P
JOIN tbl_HRITMasterData H ON H.EMPLOYEE_NUMBER = P.EmployeeNumber
JOIN JobFamily J ON J.Id = P.JobFamilyID
JOIN [dbo].[vw_BusinessUnits] D ON D.BusinessUnitID = P.DivisionID
JOIN [dbo].[vw_Countries] C ON C.CountryID = P.CountryID
JOIN Profiles M ON P.ManagerID = M.ProfileID
JOIN Profiles MM ON M.ManagerID = MM.ProfileID
WHERE P.DateHired BETWEEN @endYearStart AND @endYearEnd
AND H.DIRECT_INDIRECT = 'indirect'
AND P.Networkid NOT IN ('removed','Duplicate','pdecker','deveritt')
AND P.Networkid NOT Like '%Term_Duplicate%'
AND H.EMPLOYEE_NUMBER NOT IN ('103098', '78568', '34358')
Order BY P.ProfileID
END
No comments:
Post a Comment