how to generate monthly attendance report to yearly report

Question

how to generate monthly attendance report to yearly report sql server stored procedure

i have a monthly report sp now i need yearly report all months includes present,absent,total days,compoff,thats it how to generate

Dharmraj Thakur · Answer

Take parameter like FromDate and ToDate and provide custom daterange picker in frontview... then pass date range in sql query and get data according to that.

Madhan M · Answer

i need year only not month wise data

Sakthi Vel · Answer

CONVERT ( VARCHAR (10)e.D_DOJ103) AS 'HireDate'  s.C_LeaveType AS 'LeaveType'  lb.I_CreditYear AS 'YEAR'  ISNULL (lb.F_OpeningDays0) AS 'JanOpen'  WHERE lb.I_CreditYear between @I_Year and @I_Year GROUP BY lb.I_CreditYear s.C_EmployeeCode

Madhan M · Answer

i have only monthly report sp not year i need to generate yearly CREATE TABLE #Sample(C_EmployeeCode VARCHAR(10)C_LeaveType VARCHAR(10) January FLOATFebruary FLOAT March FLOATApril FLOATMay FLOATJune FLOATJuly FLOAT August FLOATSeptember FLOATOctober FLOATNovember FLOATDecember FLOAT) DECLARE @IntXml INT CREATE TABLE #FltData(FltId INT FltCode VARCHAR(20) FltDesc VARCHAR(50)FltLoc VARCHAR(10)) CREATE TABLE #FltMaster (FltId INT FltTyp VARCHAR(20) FltCode VARCHAR(20) FltDesc VARCHAR(100)FltLoc varchar(10)) CREATE TABLE #EmpFlt (EmpId VARCHAR(10)) CREATE TABLE #Employee(EmpId NUMERIC(180) IDENTITY(11) C_EmployeeCode VARCHAR(10)) BEGIN TRY IF @XmlDoc IS NOT NULL BEGIN EXECUTE sp_xml_preparedocument @IntXml output@XmlDoc INSERT INTO #FltData SELECT FLTID FLTCODE FLTDESCFLTLOC FROM OPENXML(@IntXml'/FLTDATA/FLTXML'1) WITH (FLTID INTFLTCODE VARCHAR(50)FLTDESC VARCHAR(50)FLTLOC VARCHAR(10)FLTCHECKED VARCHAR(30)) EXECUTE sp_xml_removedocument @IntXml END INSERT INTO #FltMaster(FltId FltTyp FltCode FltDescFltLoc) SELECT FltId FltCode RTRIM(FltDesc) RTRIM(ISNULL(C_CompanyName ''))'' FROM #FltData WITH (NOLOCK) JOIN Company WITH (NOLOCK) ON RTRIM(FltDesc) = RTRIM(C_CompanyCode) WHERE FltId = 1 UNION ALL SELECT FltId FltCode RTRIM(FltDesc) RTRIM(ISNULL(C_DepartmentName '')) RTRIM(FltLoc) FROM #FltData WITH (NOLOCK) JOIN Department WITH (NOLOCK) ON RTRIM(FltDesc) = RTRIM(C_DepartmentCode) and RTRIM(FltLoc)=RTRIM(C_LocationCode) WHERE FltId = 2 UNION ALL SELECT FltId FltCode RTRIM(FltDesc) RTRIM(ISNULL(C_DesignationName '')) RTRIM(FltLoc) FROM #FltData WITH (NOLOCK) JOIN Designation WITH (NOLOCK) ON RTRIM(FltDesc) = RTRIM(C_DesignationCode) and RTRIM(FltLoc)=RTRIM(C_LocationCode) WHERE FltId = 3 UNION ALL SELECT FltId FltCode RTRIM(FltDesc) RTRIM(ISNULL(C_DivisionName ''))RTRIM(FltLoc) FROM #FltData WITH (NOLOCK) JOIN Division WITH (NOLOCK) ON RTRIM(FltDesc) = RTRIM(C_DivisionCode) and RTRIM(FltLoc)=RTRIM(C_LocationCode) WHERE FltId = 4 UNION ALL SELECT FltId FltCode RTRIM(FltDesc) RTRIM(ISNULL(C_GradeName '')) RTRIM(FltLoc) FROM #FltData WITH (NOLOCK) JOIN Grade WITH (NOLOCK) ON RTRIM(FltDesc) = RTRIM(C_GradeCode) and RTRIM(FltLoc)=RTRIM(C_LocationCode) WHERE FltId = 6 UNION ALL SELECT FltId FltCode RTRIM(FltDesc) RTRIM(ISNULL(C_LocationName ''))'' FROM #FltData WITH (NOLOCK) JOIN Location WITH (NOLOCK) ON RTRIM(FltDesc) = RTRIM(C_LocationCode) WHERE FltId = 7 UNION ALL SELECT FltId FltCode RTRIM(FltDesc) RTRIM(ISNULL(C_PositionName '')) '' FROM #FltData WITH (NOLOCK) JOIN Position WITH (NOLOCK) ON RTRIM(FltDesc) = RTRIM(C_PositionCode) WHERE FltId = 8 UNION ALL SELECT FltId FltCode RTRIM(FltDesc) RTRIM(ISNULL(C_CategoryName '')) RTRIM(FltLoc) FROM #FltData WITH (NOLOCK) JOIN Category WITH (NOLOCK) ON RTRIM(FltDesc) = RTRIM(C_CategoryCode) and RTRIM(FltLoc)=RTRIM(C_LocationCode) WHERE FltId = 10 UNION ALL SELECT FltId FltCode RTRIM(FltDesc) '' '' FROM #FltData WITH (NOLOCK) WHERE FltId = 11 INSERT INTO #EmpFlt SELECT RTRIM(FltDesc) FROM #FltData WITH (NOLOCK) WHERE FltId = 5 INSERT INTO #Employee SELECT C_EmployeeCode FROM Employee e WITH (NOLOCK) JOIN #FltMaster AS COMP WITH (NOLOCK) ON COMP.FltId = 1 AND RTRIM(COMP.FltCode) = RTRIM(e.C_CompanyCode) JOIN #FltMaster AS DEPT WITH (NOLOCK) ON DEPT.FltId = 2 AND RTRIM(DEPT.FltCode) = RTRIM(e.C_DepartmentCode) AND RTRIM(DEPT.FltLoc)=RTRIM(e.C_LocationCode ) JOIN #FltMaster AS DESG WITH (NOLOCK) ON DESG.FltId = 3 AND RTRIM(DESG.FltCode) = RTRIM(e.C_DesignationCode) AND RTRIM(DESG.FltLoc)=RTRIM(e.C_LocationCode ) JOIN #FltMaster AS DIVN WITH (NOLOCK) ON DIVN.FltId = 4 AND RTRIM(DIVN.FltCode) = RTRIM(e.C_DivisionCode) AND RTRIM(DIVN.FltLoc)=RTRIM(e.C_LocationCode ) JOIN #FltMaster AS GRAD WITH (NOLOCK) ON GRAD.FltId = 6 AND RTRIM(GRAD.FltCode) = RTRIM(e.C_GradeCode) AND RTRIM(GRAD.FltLoc)=RTRIM(e.C_LocationCode ) JOIN #FltMaster AS LOCN WITH (NOLOCK) ON LOCN.FltId = 7 AND RTRIM(LOCN.FltCode) = RTRIM(e.C_LocationCode) JOIN #FltMaster AS POSN WITH (NOLOCK) ON POSN.FltId = 8 AND RTRIM(POSN.FltCode) = RTRIM(e.C_EmploymentPosition) JOIN #FltMaster AS CATG WITH (NOLOCK) ON CATG.FltId = 10 AND RTRIM(CATG.FltCode) = RTRIM(e.C_CategoryCode) AND RTRIM(CATG.FltLoc)=RTRIM(e.C_LocationCode ) JOIN #FltMaster AS GEND WITH (NOLOCK) ON GEND.FltId = 11 AND RTRIM(GEND.FltCode)=RTRIM(e.C_Gender) JOIN #EmpFlt WITH (NOLOCK) ON EmpId = C_EmployeeCode WHERE EXISTS (SELECT NULL FROM #EmpFlt WITH (NOLOCK) WHERE EmpId = C_EmployeeCode) AND e.B_PrintFlag=1 INSERT INTO #Sample SELECT * FROM( SELECT d.C_EmployeeCodeDATENAME(MONTHd.D_AttDate) AS 'C_MonthName'lt.C_LeaveType SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus1) LIKE l.C_Legend) AND (RIGHT(d.C_AttendanceStatus1) NOT LIKE l.C_Legend)) THEN 0.5 ELSE 0 END) + SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus1) NOT LIKE l.C_Legend) AND (RIGHT(d.C_AttendanceStatus1) LIKE l.C_Legend)) THEN 0.5 ELSE 0 END) + SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus1) LIKE l.C_Legend) AND (RIGHT(d.C_AttendanceStatus1) 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 AND e.B_PrintFlag=1 JOIN #Employee ee WITH(NOLOCK) ON ee.C_EmployeeCode=e.C_EmployeeCode JOIN LeaveType lt WITH(NOLOCK) ON lt.C_LocationCode=e.C_LocationCode JOIN Legends l WITH(NOLOCK) ON l.C_Legend=lt.C_Legend WHERE YEAR(d.D_AttDate)=@I_Year GROUP BY d.C_EmployeeCodeDATENAME(MONTHd.D_AttDate)lt.C_LeaveType --HAVING --SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus1) LIKE l.C_Legend) AND -- (RIGHT(d.C_AttendanceStatus1) NOT LIKE l.C_Legend)) THEN 0.5 ELSE 0 END) + --SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus1) NOT LIKE l.C_Legend) AND -- (RIGHT(d.C_AttendanceStatus1) LIKE l.C_Legend)) THEN 0.5 ELSE 0 END) + --SUM(CASE WHEN ((LEFT(d.C_AttendanceStatus1) LIKE l.C_Legend) AND -- (RIGHT(d.C_AttendanceStatus1) LIKE l.C_Legend)) THEN 1 ELSE 0 END) -->0 )A PIVOT ( SUM(DaysCount)FOR C_MonthName IN ([January][February][March][April][May][June][July][August][September][October][November][December]) )B; SELECT s.C_EmployeeCode AS 'EmployeeCode'e.C_EmployeeName AS 'EmployeeName' co.C_CompanyName AS 'Company'lo.C_LocationName AS 'Location'ca.C_CategoryName AS 'Category' d.C_DepartmentName AS 'Department'ds.C_DesignationName AS 'Designation' di.C_DivisionName AS 'Division'g.C_GradeName AS 'Grade' CONVERT(VARCHAR(10)e.D_DOJ103)AS 'HireDate' s.C_LeaveType AS 'LeaveType' ISNULL(lb.F_OpeningDays0) AS 'JanOpen' ISNULL(s.January0) AS 'JanAvailed' SUM(ISNULL(lb.F_OpeningDays0)- ISNULL(s.January0)) AS 'JanClosed' SUM(ISNULL(lb.F_OpeningDays0)-ISNULL(s.January0)) AS 'FebOpen' ISNULL(s.February0) AS 'FebAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0))) AS 'FebClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ ISNULL(s.February0))) AS 'MarOpen' ISNULL(s.March0) AS 'MarAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ ISNULL(s.February0)+ISNULL(s.March0))) AS 'MarClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ ISNULL(s.February0)+ISNULL(s.March0))) AS 'AprOpen' ISNULL(s.March0)AS 'AprAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0))) AS 'AprClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0))) AS 'MayOpen' ISNULL(s.May0) AS 'MayAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0))) AS 'MayClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0))) AS 'JunOpen' ISNULL(s.June0) AS 'JunAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0))) AS 'JunClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0))) AS 'JulOpen' ISNULL(s.July0) AS 'JulAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0))) AS 'JulClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0))) AS 'AugOpen' ISNULL(s.August0) AS 'AugAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0))) AS 'AugClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0))) AS 'SepOpen' ISNULL(s.September0) AS 'SepAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0))) AS 'SepClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0))) AS 'OctOpen' ISNULL(s.October0) AS 'OctAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0))) AS 'OctClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0))) AS 'NovOpen' ISNULL(s.November0) AS 'NovAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0)+ISNULL(s.November0))) AS 'NovClosed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0)+ISNULL(s.November0))) AS 'DecOpen' ISNULL(s.December0) AS 'DecAvailed' SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0)+ISNULL(s.November0)+ISNULL(s.December0))) AS 'DecClosed' CASE WHEN lp.B_IsEncashable=1 THEN CASE WHEN lp.F_Max_EncashDays lp.F_Max_EncashDays WHEN (lp.F_Max_EncashDays > SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0)+ISNULL(s.November0)+ISNULL(s.December0)))) AND (lp.F_Min_EncashDays SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0)+ISNULL(s.November0)+ISNULL(s.December0))) ELSE 0 END ELSE 0 END AS 'EncashDays' CASE WHEN lp.F_Max_EncashDays SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0)+ISNULL(s.November0)+ISNULL(s.December0)))-lp.F_Max_EncashDays WHEN (lp.F_Max_EncashDays > SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0)+ISNULL(s.November0)+ISNULL(s.December0)))) AND (lp.F_Min_EncashDays>SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0)+ISNULL(s.November0)+ISNULL(s.December0)))) THEN SUM(ISNULL(lb.F_OpeningDays0)-(ISNULL(s.January0)+ISNULL(s.February0)+ISNULL(s.March0)+ISNULL(s.April0)+ISNULL(s.May0)+ISNULL(s.June0)+ISNULL(s.July0)+ISNULL(s.August0)+ISNULL(s.September0)+ISNULL(s.October0)+ISNULL(s.November0)+ISNULL(s.December0))) ELSE 0 END AS 'ElapsedDays' FROM #Sample s WITH(NOLOCK) JOIN LeaveBalance lb WITH(NOLOCK) ON s.C_EmployeeCode=lb.C_EmployeeCode AND lb.C_LeaveType=s.C_LeaveType JOIN Employee e WITH(NOLOCK) ON e.C_EmployeeCode=lb.C_EmployeeCode AND e.B_PrintFlag=1 JOIN #Employee ee WITH(NOLOCK) ON ee.C_EmployeeCode=e.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 Category ca WITH(NOLOCK) ON ca.C_LocationCode=e.C_LocationCode AND ca.C_CategoryCode=e.C_CategoryCode JOIN Department d WITH(NOLOCK) ON d.C_LocationCode=e.C_LocationCode AND d.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 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 Leave_Policy lp WITH(NOLOCK) ON lp.C_LocationCode=e.C_LocationCode AND lp.C_CategoryCode=e.C_CategoryCode AND lp.C_LeaveType=lb.C_LeaveType WHERE lb.I_CreditYear=@I_Year GROUP BY s.C_EmployeeCodee.C_EmployeeNameco.C_CompanyNamelo.C_LocationNameca.C_CategoryName d.C_DepartmentNameds.C_DesignationName di.C_DivisionNameg.C_GradeName e.D_DOJ s.C_LeaveTypelb.F_OpeningDayss.Januarys.Februarys.Marchs.Aprils.Mays.Junes.Julys.Augusts.Septembers.Octobers.Novembers.December lp.B_IsEncashablelp.F_Min_EncashDays lp.F_Max_EncashDays ORDER BY s.C_EmployeeCode s.C_LeaveType SET NOCOUNT OFF; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS CodeERROR_MESSAGE() AS Description END CATCH END

Sakthi Vel · Answer

IN sp change where condition  WHERE Year(D_AttDate)=@I_Year  is this not working pls share full sp.

Madhan M · Answer

ALTER PROCEDURE [dbo].[PROC_Report_LeaveHistory] ( @XmlDoc NTEXT @I_Year INTEGER ) Dayfile ColunNameD_AttDate C_AttendanceStatuspresent=PRAbsent=Ab

Sakthi Vel · Answer

Madhan Share sp name and parameters  table columns which one contains datepresetabsent details