Click here to Skip to main content
15,176,561 members
Please Sign up or sign in to vote.
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
Updated 8-Jun-21 21:33pm
v2

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;
   
Comments
CHill60 10-Jul-19 6:30am
   
Please don't repost the same solution twice
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;
   

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




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900