PROCEDURE [dbo].[PROC_Report_TotalCount]
(
@C_LocationCode VARCHAR(12),
@C_CategoryCode VARCHAR(12),
@C_ShiftBasedOn VARCHAR(1), -- P-Only Present Count / A-All
@I_Year INTEGER,
@I_Month INTEGER
)
AS
BEGIN
SET NOCOUNT ON;
BEGIN TRY
CREATE TABLE #ShiftTbl(C_EmployeeCode VARCHAR(12), C_Shift VARCHAR(MAX),I_ShiftCount FLOAT,C_EmployeeName VARCHAR(40),C_CompanyName VARCHAR(100),C_LocationName VARCHAR(100), C_CategoryName VARCHAR(100),C_DepartmentName VARCHAR(100),C_DivisionName VARCHAR(100),C_DesignationName VARCHAR(100),C_GradeName VARCHAR(100))
DECLARE @Cols AS NVARCHAR(MAX),@Query AS NVARCHAR(MAX)
DECLARE @DT DATETIME
SET @DT=CONVERT(DATETIME, CONVERT(VARCHAR,@I_Year) + CASE WHEN LEN(CONVERT(VARCHAR,@I_Month))=1 THEN '0'+(CONVERT(VARCHAR,@I_Month)) ELSE CONVERT(VARCHAR,@I_Month) END + '01')
IF @C_ShiftBasedOn='P'
BEGIN
INSERT INTO #ShiftTbl(C_EmployeeCode,C_Shift ,I_ShiftCount)
SELECT d.C_EmployeeCode,'Shift-'+d.C_Shift,
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'X' ) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE 'X')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE 'X') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'X')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'X') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'X')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'D' ) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE 'D')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE 'D') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'D')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'D') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'D')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'X') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'D')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'D') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'X')) THEN 1 ELSE 0 END)
FROM Dayfile d WITH(NOLOCK)
JOIN Employee e WITH(NOLOCK) ON e.C_EmployeeCode=d.C_EmployeeCode
JOIN Shift_Policy sp WITH(NOLOCK) ON sp.C_LocationCode=e.C_LocationCode AND sp.C_CategoryCode=e.C_CategoryCode AND sp.C_ShiftCode=d.C_Shift
WHERE e.C_LocationCode=@C_LocationCode AND e.C_CategoryCode=@C_CategoryCode AND MONTH(d.D_AttDate)=@I_Month AND YEAR(d.D_AttDate)=@I_Year AND d.C_Shift<>'WW'
GROUP BY d.C_EmployeeCode,d.C_Shift,MONTH(d.D_AttDate)
END
ELSE IF @C_ShiftBasedOn='A'
BEGIN
INSERT INTO #ShiftTbl(C_EmployeeCode,C_Shift ,I_ShiftCount)
SELECT d.C_EmployeeCode,'Shift-'+d.C_Shift,COUNT(*) FROM Dayfile d WITH(NOLOCK)
JOIN Employee e WITH(NOLOCK) ON e.C_EmployeeCode=d.C_EmployeeCode
JOIN Shift_Policy sp WITH(NOLOCK) ON sp.C_LocationCode=e.C_LocationCode AND sp.C_CategoryCode=e.C_CategoryCode AND sp.C_ShiftCode=d.C_Shift
WHERE e.C_LocationCode=@C_LocationCode AND e.C_CategoryCode=@C_CategoryCode AND MONTH(d.D_AttDate)=@I_Month AND YEAR(d.D_AttDate)=@I_Year AND d.C_Shift<>'WW'
GROUP BY d.C_EmployeeCode,d.C_Shift,MONTH(d.D_AttDate)
END
INSERT INTO #ShiftTbl(C_EmployeeCode,C_Shift ,I_ShiftCount)
SELECT d.C_EmployeeCode, l.C_Description ,
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE l.C_Legend) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE l.C_Legend)) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE l.C_Legend) AND (RIGHT(d.C_AttendanceStatus,1) LIKE l.C_Legend)) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE l.C_Legend) AND (RIGHT(d.C_AttendanceStatus,1) LIKE l.C_Legend)) THEN 1 ELSE 0 END)
AS 'DaysCount' FROM Dayfile d WITH(NOLOCK)
JOIN Employee e WITH(NOLOCK) ON e.C_EmployeeCode=d.C_EmployeeCode
JOIN Legends l WITH(NOLOCK) ON (l.C_Legend LIKE LEFT(d.C_AttendanceStatus,1)) OR (l.C_Legend LIKE RIGHT(d.C_AttendanceStatus,1)) OR (l.C_Legend LIKE d.C_AttendanceStatus)
WHERE e.C_LocationCode=@C_LocationCode AND e.C_CategoryCode=@C_CategoryCode AND MONTH(d.D_AttDate)=@I_Month AND YEAR(d.D_AttDate)=@I_Year
GROUP BY d.C_EmployeeCode,l.C_Description
--HAVING
--SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE l.C_Legend) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE l.C_Legend)) THEN 0.5 ELSE 0 END) +
--SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE l.C_Legend) AND (RIGHT(d.C_AttendanceStatus,1) LIKE l.C_Legend)) THEN 0.5 ELSE 0 END) +
--SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE l.C_Legend) AND (RIGHT(d.C_AttendanceStatus,1) LIKE l.C_Legend)) THEN 1 ELSE 0 END)
-->0 --ORDER BY d.C_EmployeeCode
UNION ALL
SELECT d.C_EmployeeCode,'LOP Days',
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'A' ) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE 'A')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE 'A') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'A')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'A') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'A')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'L' ) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE 'L')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE 'L') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'L')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'L') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'L')) THEN 1 ELSE 0 END)
FROM Dayfile d WITH(NOLOCK) JOIN Employee e WITH(NOLOCK) ON d.C_EmployeeCode=e.C_EmployeeCode
WHERE e.C_LocationCode=@C_LocationCode AND e.C_CategoryCode=@C_CategoryCode AND MONTH(d.D_AttDate)=@I_Month AND YEAR(d.D_AttDate)=@I_Year
GROUP BY d.C_EmployeeCode
UNION ALL
SELECT d.C_EmployeeCode,'Actual WorkDays',
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'X' ) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE 'X')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE 'X') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'X')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'X') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'X')) THEN 1 ELSE 0 END)
FROM Dayfile d WITH(NOLOCK) JOIN Employee e WITH(NOLOCK) ON d.C_EmployeeCode=e.C_EmployeeCode
WHERE e.C_LocationCode=@C_LocationCode AND e.C_CategoryCode=@C_CategoryCode AND MONTH(d.D_AttDate)=@I_Month AND YEAR(d.D_AttDate)=@I_Year
GROUP BY d.C_EmployeeCode
UNION ALL
SELECT d.C_EmployeeCode,'OnDuty',
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'D' ) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE 'D')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE 'D') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'D')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'D') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'D')) THEN 1 ELSE 0 END)
FROM Dayfile d WITH(NOLOCK) JOIN Employee e WITH(NOLOCK) ON d.C_EmployeeCode=e.C_EmployeeCode
WHERE e.C_LocationCode=@C_LocationCode AND e.C_CategoryCode=@C_CategoryCode AND MONTH(d.D_AttDate)=@I_Month AND YEAR(d.D_AttDate)=@I_Year
GROUP BY d.C_EmployeeCode
UNION ALL
SELECT d.C_EmployeeCode,'Calendar Days',DATEDIFF(DAY,@DT,DATEADD(MONTH,1,@DT))
FROM Dayfile d WITH(NOLOCK) JOIN Employee e WITH(NOLOCK) ON d.C_EmployeeCode=e.C_EmployeeCode
WHERE e.C_LocationCode=@C_LocationCode AND e.C_CategoryCode=@C_CategoryCode AND MONTH(d.D_AttDate)=@I_Month AND YEAR(d.D_AttDate)=@I_Year
GROUP BY d.C_EmployeeCode
UNION ALL
SELECT d.C_EmployeeCode, 'Pay Days', ((DATEDIFF(DAY,@DT,DATEADD(MONTH,1,@DT)))-
(SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'A' ) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE 'A')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE 'A') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'A')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'A') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'A')) THEN 1 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'L' ) AND (RIGHT(d.C_AttendanceStatus,1) NOT LIKE 'L')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) NOT LIKE 'L') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'L')) THEN 0.5 ELSE 0 END) +
SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus,1) LIKE 'L') AND (RIGHT(d.C_AttendanceStatus,1) LIKE 'L')) THEN 1 ELSE 0 END)))
FROM Dayfile d WITH(NOLOCK) JOIN Employee e WITH(NOLOCK) ON d.C_EmployeeCode=e.C_EmployeeCode
WHERE e.C_LocationCode=@C_LocationCode AND e.C_CategoryCode=@C_CategoryCode AND MONTH(d.D_AttDate)=@I_Month AND YEAR(d.D_AttDate)=@I_Year
GROUP BY d.C_EmployeeCode
UNION ALL
SELECT o.C_WorkedEmployee, 'OT Hours',
dbo.CONHRS(SUM(dbo.CONMIN(ISNULL(C_ChangedHrs,'0000')))) from
Employee e JOIN OverTime_Req o ON o.C_WorkedEmployee=e.C_EmployeeCode AND o.C_Status IN ('A','C')
WHERE e.C_LocationCode=@C_LocationCode AND e.C_CategoryCode=@C_CategoryCode AND MONTH(o.C_WorkedDate)=@I_Month AND YEAR(o.C_WorkedDate)=@I_Year
GROUP BY o.C_WorkedEmployee
UPDATE s SET s.C_EmployeeName=e.C_EmployeeName ,
s.C_CompanyName=co.C_CompanyName,
s.C_LocationName=lo.C_LocationName,
s.C_CategoryName=c.C_CategoryName ,
s.C_DepartmentName=de.C_DepartmentName ,
s.C_DesignationName=ds.C_DesignationName,
s.C_DivisionName=di.C_DivisionName,
s.C_GradeName=g.C_GradeName
FROM #ShiftTbl s
JOIN Employee e WITH(NOLOCK) ON e.C_EmployeeCode=s.C_EmployeeCode
JOIN Company co WITH(NOLOCK) ON co.C_CompanyCode=e.C_CompanyCode
JOIN Location lo WITH(NOLOCK) ON lo.C_LocationCode=e.C_LocationCode
JOIN Division di WITH(NOLOCK) ON di.C_LocationCode=e.C_LocationCode AND di.C_DivisionCode=e.C_DivisionCode
JOIN Grade g WITH(NOLOCK) ON g.C_LocationCode=e.C_LocationCode AND g.C_GradeCode=e.C_GradeCode
JOIN Department de WITH(NOLOCK) ON de.C_LocationCode=e.C_LocationCode AND de.C_DepartmentCode=e.C_DepartmentCode
JOIN Designation ds WITH(NOLOCK) ON ds.C_LocationCode=e.C_LocationCode AND ds.C_DesignationCode=e.C_DesignationCode
JOIN Category c WITH(NOLOCK) ON c.C_LocationCode=e.C_LocationCode AND c.C_CategoryCode=e.C_CategoryCode
SELECT @Cols=ISNULL(@Cols+',','') + QUOTENAME(C_Shift)
FROM (SELECT DISTINCT(C_Shift) FROM #ShiftTbl)AS ShiftNames --ORDER BY C_Shift
SELECT @Query=N'SELECT C_EmployeeCode AS EmployeeCode,C_EmployeeName AS EmployeeName,C_CompanyName AS Company,C_LocationName AS Location,C_CategoryName AS Category,C_DepartmentName AS Department, C_DesignationName AS Designation,C_DivisionName AS Division,C_GradeName AS Grade ,' + @Cols + '
FROM #ShiftTbl
PIVOT(SUM(I_ShiftCount)
FOR C_Shift IN (' + @Cols + ')) AS ShiftNames'
EXEC sp_executesql @Query
SET NOCOUNT OFF;
END TRY
BEGIN CATCH
END CATCH
END
Nilesh ShahPosted May 17, 2017, 11:18 AM