Calculate employee monthly attendance by SQL server 2012

Question

0.00/5 (No votes)

See more:

Table 1 : STP_Employee 
          EmpID
          Name
          ShiftID
          etc..

Table 2 : ATT_Attendance
          AttendanceID
          EmpID
          Date (Datetime)
          TimeIn (Datetime)
          TimeOut (Datetime)

Table 3 : STP_Shift
          ShiftID
          ShiftTitle
          WorkDays             (Monday,Tuesday,Wednesday,Thursday,Friday,Saturday)
          TotalWorkHours       ( diffrence between RegularWorkHourFrom and RegularWorkHourTo)
          RegularWorkHourFrom
          RegularWorkHourTo

Note : In Shift table 6 rows exist from monday to saturday. Sunday is holiday. Each row has same data like total work hours , regular work hours from and to...

now i want to calculate monthly attendance report with shift roaster and it should calculate overtime and late attendance....

Expected OutPut :

Empid 1 2 3 4 5 6 7 8 ......
Emp-1,P P A P P P A

P: Present, A: Absent

I have been calculated summary of monthly attendance. but need detail attendance report day wise with shiftlength and late summary missing in summary of monthly attendance.

What I have tried:

SQL

ALTER PROCEDURE [dbo].[sp_Earnings] @fromDate datetime, @toDate datetime ,
	@EmpID nvarchar(50)
    	-- Add the parameters for the stored procedure here    
    AS
    BEGIN
    -- Declaring a variable to hold on of days in the month.
    DECLARE @No_of_days int
    SELECT @No_of_days = DATEDIFF(day,@fromDate,DATEADD(day,1,(@toDate)))
    
    -- Declaring a constant to hold no of off days allowed in a month
    DECLARE @Day_offs_allowed int
    SELECT @Day_offs_allowed=0	
	

;WITH CTE AS (
    SELECT  
            PRL_Emp_Salary.EmpID,           
            COUNT(DISTINCT CONVERT(DATE, ATT_Attendance.Date)) AS work_days,                        
            PRL_Emp_Salary.BasicSalary, datediff(day, -1, @toDate)/7-datediff(day, 0, @fromDate)/7 AS SUN,
            CASE WHEN DATEDIFF(day, HR_Leave.FromDate, HR_Leave.ToDate) = 0 
            THEN 1 ELSE DATEDIFF(day, HR_Leave.FromDate, HR_Leave.ToDate)
            END AS Leave_Approved
			
    FROM        PRL_Emp_Salary
    INNER JOIN  ATT_Attendance ON PRL_Emp_Salary.EmpID = ATT_Attendance.EmpID LEFT OUTER  JOIN  HR_Leave ON PRL_Emp_Salary.EmpID = HR_Leave.EmpID
	 
    WHERE       ATT_Attendance.TimeIn BETWEEN CONVERT(DATETIME, @fromDate, 102) AND CONVERT(DATETIME, @toDate, 102)                        and PRL_Emp_Salary.EmpID=@EmpID
    GROUP BY    
                PRL_Emp_Salary.EmpID,
                PRL_Emp_Salary.BasicSalary,HR_Leave.FromDate,HR_Leave.ToDate
    ),

 CTE1 AS (

 select 
 CASE WHEN 
 SUM(DATEDIFF(day, HR_Holiday.FromDate, HR_Holiday.ToDate)+1) > 0 
 THEN 
 SUM(DATEDIFF(day, HR_Holiday.FromDate, HR_Holiday.ToDate)+1)
 ELSE  0 END 
 AS Holiday from HR_Holiday where IsDelete=0
 and (HR_Holiday.FromDate between @fromDate and @toDate OR HR_Holiday.ToDate between @fromDate and @toDate)
	)
	

SELECT  
        EmpID,
		work_days As WorkDayWithoutSunday,
        work_days + SUN + ISNULL(Leave_Approved,0) + (select Holiday from CTE1 )   As WorkDayWithSunday_AND_LEAVES,
		SUN As Sunday,
		@No_of_days-1  As NoOfDays,
		Leave_Approved,
		--ShiftLength,

        CASE
            WHEN (@No_of_days-1  - (work_days) >= @Day_offs_allowed)
            THEN @Day_offs_allowed
            ELSE (@No_of_days-1  - (work_days))
        END AS day_offs,
        CASE
            WHEN (@No_of_days-1  - (work_days) >= @Day_offs_allowed) 
            THEN @No_of_days-1  - (work_days) - @Day_offs_allowed 
            ELSE 0 
        END AS leave_days, 
        BasicSalary,
        (@No_of_days-1 -@Day_offs_allowed)- work_days AS no_pay_days,
		
        CONVERT(DECIMAL(10,2),(((BasicSalary) / (@No_of_days-1 -@Day_offs_allowed))) * ((@No_of_days-1 -@Day_offs_allowed)-  (work_days + SUN + ISNULL(Leave_Approved,0)  + (select Holiday from CTE1 )))) AS less_no_pay_amt, 
        BasicSalary-CONVERT(DECIMAL(10,2),((BasicSalary) / (@No_of_days-1 -@Day_offs_allowed) ) * ((@No_of_days-1 -@Day_offs_allowed)- (work_days + SUN + ISNULL(Leave_Approved,0) + (select Holiday from CTE1 )))) AS amt_for_epf,
		(select Holiday from CTE1 ) AS Holiday
		
FROM    CTE 

 END

Posted 15-Nov-16 21:07pm

Richard_Michael_RM

Updated 8-Jun-21 20:33pm

Nelek

v2

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Member 14510245 · Answer 1 · 2019-07-09T07:39:00

SELECT --EMP_NO, EMP_NAME 
	EMP_NO, EMP_NAME, R, [1], [2], [3], [4],[5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15], [16], [17], [18], [19],[20], [21], [22], [23], [24],[25], [26], [27], [28],[29],[30],[31],
	([1] + [2]+ [3]+ [4]+[5]+ [6]+ [7]+ [8]+ [9]+[10]+ [11]+ [12]+ [13]+ [14]+ [15] + [16]
	+ [17]+ [18]+ [19]+[20]+ [21]+ [22]+ [23]+ [24]+[25]+ [26]+ [27]+ [28] + [29] + [30] + [31])DUTY
	FROM(
	SELECT 
	  EMP_NO, EMP_NAME, DEPT R
	  ,CASE WHEN substring(DATENAME(WEEKDAY, Indatetime),1,3) <> 'SUN'  and  ATTENDANCE_STATUS = 1 THEN 'P' 
			WHEN ATTENDANCE_STATUS = 0 and substring(DATENAME(WEEKDAY, Indatetime),1,3) <> 'SUN'   THEN 'A'
			--else 'WO' 
			end ATTENDANCE
	  --,DATENAME(M, CHECKDATE)AS [MONTHVALUE]
	  ,ISNULL(DAY(CHECKDATE),0) AS [DAYVALUE]
	FROM EMPLOYEETIMESEQUENCE WHERE SUBSTRING(REPLACE(CONVERT(VARCHAR,CHECKDATE,106),' ','/'),4,8) = 'MAY/2019' and Emp_No = 1000) AS STUD
	PIVOT
	(
	 COUNT([ATTENDANCE])
	  FOR [DAYVALUE] IN ([1], [2], [3], [4],[5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15], [16], [17], [18], [19],[20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30],[31])
	) AS PV;

Member 14510245 · Answer 2 · 2019-07-09T07:37:00

below query hope your expectation

SELECT --EMP_NO, EMP_NAME 
	EMP_NO, EMP_NAME, R, [1], [2], [3], [4],[5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15], [16], [17], [18], [19],[20], [21], [22], [23], [24],[25], [26], [27], [28],[29],[30],[31],
	([1] + [2]+ [3]+ [4]+[5]+ [6]+ [7]+ [8]+ [9]+[10]+ [11]+ [12]+ [13]+ [14]+ [15] + [16]
	+ [17]+ [18]+ [19]+[20]+ [21]+ [22]+ [23]+ [24]+[25]+ [26]+ [27]+ [28] + [29] + [30] + [31])DUTY
	FROM(
	SELECT 
	  EMP_NO, EMP_NAME, DEPT R
	  ,CASE WHEN substring(DATENAME(WEEKDAY, Indatetime),1,3) <> 'SUN'  and  ATTENDANCE_STATUS = 1 THEN 'P' 
			WHEN ATTENDANCE_STATUS = 0 and substring(DATENAME(WEEKDAY, Indatetime),1,3) <> 'SUN'   THEN 'A'
			--else 'WO' 
			end ATTENDANCE
	  --,DATENAME(M, CHECKDATE)AS [MONTHVALUE]
	  ,ISNULL(DAY(CHECKDATE),0) AS [DAYVALUE]
	FROM EMPLOYEETIMESEQUENCE WHERE SUBSTRING(REPLACE(CONVERT(VARCHAR,CHECKDATE,106),' ','/'),4,8) = 'MAY/2019' and Emp_No = 1000) AS STUD
	PIVOT
	(
	 COUNT([ATTENDANCE])
	  FOR [DAYVALUE] IN ([1], [2], [3], [4],[5], [6], [7], [8], [9],[10], [11], [12], [13], [14],[15], [16], [17], [18], [19],[20], [21], [22], [23], [24],[25], [26], [27], [28], [29],[30],[31])
	) AS PV;

Calculate employee monthly attendance by SQL server 2012

2 solutions

Solution 2

Solution 1

Add your solution here

Preview 0