Richard_Michael_RM Ответов: 2

Расчет ежемесячной посещаемости сотрудников по SQL server 2012

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
Примечание : в таблице сдвига 6 строк существуют с понедельника по субботу. Воскресенье-это праздник. Каждая строка содержит одни и те же данные , такие как общее количество рабочих часов, регулярные рабочие часы от и до...


теперь я хочу рассчитать ежемесячный отчет о посещаемости с помощью shift roaster, и он должен рассчитать сверхурочную и позднюю посещаемость....

Ожидаемый Результат :

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

Р: Присутствует, А: Отсутствует

У меня была рассчитана сводная месячная посещаемость. но вам нужен подробный отчет о посещаемости в день Мудрого с shiftlength и поздним резюме, отсутствующим в сводке ежемесячной посещаемости.

Что я уже пробовал:

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

2 Ответов

Рейтинг:
2

Member 14510245 

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;

CHill60

Пожалуйста, не перепечатывайте одно и то же решение дважды

Рейтинг:
0

Member 14510245

ниже запрос надеюсь ваши ожидания 

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;