how to generate yearly report in sp

Question

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