I have to find the employee violation for breaks taken. I have two time shift hours to check a.(>6hrs and <10 hrs), b. (>10hrs). check # of breaks

z0qdvdin  于 2023-02-28  发布在  其他
关注(0)|答案(1)|浏览(108)

I have this data enter image description here 6th, 7th and 8th dates have one break but on 9th has 2 breaks. I have to check whether in (>6hrs and <10hrts) shift ,has the employee taken first break before end of 6th hour and the break is minimum 30 mins. Now since 6th, 7th and 8th dates have one break , it is easy to calculate the length before first break and break length also. But if there are 2 breaks in that shift, then I have to check is there any break of minimum 30 mins, if there is, then no violation or else it is a violation. I am stuck how to identify if there are 2 breaks and if either of it is minimum 30 mins.

This is stored procedure with parameters 'datestart','dateend','empid'. 6th is datestart and 9th is dateend.

select datestart
       ,DateEnd
       ,BREAKLENGTH
        ,MINSELAPSED
        ,ROW_NUMBER() OVER (PARTITION BY EMPID,CAST(DATESTART AS DATE) ORDER BY DATESTART ASC)                  BREAKCOUNT from(
select EMPID 
      ,datestart
      , DATEEND, 
      LAG_DATEEND
      ,DATEDIFF(MI,LAG_DATEEND,DateStart) AS BREAKLENGTH
     ,CAST(ROUND(MINSELAPSED/60.0,2) AS NUMERIC(36,2)) AS MINSELAPSED from (
select empid,
        datestart,
        DATEEND,
        LAG(DATEEND,1,NULL) OVER (PARTITION BY EMPID,CAST(DATESTART AS DATE) ORDER BY DATESTART ASC) AS LAG_DATEEND,
        MinsElapsed
         from coemplbr
where empid = '8371' and DateStart between '2023-02-06' and '2023-02-10' and ItmTyp = 362)a)B
--where BREAKLENGTH > 30
GROUP BY DATESTART,DateEnd,BREAKLENGTH,MINSELAPSED,EMPID
datestart   DateEnd BREAKLENGTH MINSELAPSED BREAKCOUNT
2023-02-06 07:00:00.047 2023-02-06 12:42:45.177 NULL    5.70    1
2023-02-06 13:15:29.387 2023-02-06 16:28:02.330 33  3.22    2
2023-02-07 07:07:03.360 2023-02-07 12:45:14.610 NULL    5.63    1
2023-02-07 13:14:16.067 2023-02-07 15:52:53.923 29  2.63    2
2023-02-08 06:57:52.783 2023-02-08 12:45:20.353 NULL    5.80    1
2023-02-08 13:26:11.510 2023-02-08 15:34:20.463 41  2.13    2
2023-02-09 07:00:26.690 2023-02-09 12:43:16.323 NULL    5.72    1
2023-02-09 12:46:50.937 2023-02-09 13:10:18.577 3   0.40    2
2023-02-09 13:41:06.107 2023-02-09 15:32:53.513 31  1.85    3
zengzsys

zengzsys1#

Taking your example data (and adding a couple of extra rows):

DECLARE @Table TABLE (EmployeeID INT, DateStart DATETIME, DateEnd DATETIME)
INSERT INTO @Table (EmployeeID, DateStart, DateEnd) VALUES
(1, '2023-02-06 07:00:00.047', '2023-02-06 12:42:45.177'), 
(1, '2023-02-06 13:15:29.387', '2023-02-06 16:28:02.330'), 
(1, '2023-02-07 07:07:03.360', '2023-02-07 12:45:14.610'), 
(1, '2023-02-07 13:14:16.067', '2023-02-07 15:52:53.923'), 
(1, '2023-02-08 06:57:52.783', '2023-02-08 12:45:20.353'), 
(1, '2023-02-08 13:26:11.510', '2023-02-08 15:34:20.463'), 
(1, '2023-02-09 07:00:26.690', '2023-02-09 12:43:16.323'), 
(1, '2023-02-09 12:46:50.937', '2023-02-09 13:10:18.577'), 
(1, '2023-02-09 13:41:06.107', '2023-02-09 15:32:53.513'), 
(1, '2023-02-10 01:41:06.107', '2023-02-10 09:39:53.513'), 
(1, '2023-02-10 09:41:06.107', '2023-02-10 10:32:53.513'), 
(1, '2023-02-10 10:41:06.107', '2023-02-10 08:32:53.513'); 

SELECT EmployeeID, WorkDate, COUNT(BreakDurationSeconds) AS Breaks, SUM(BreakDurationSeconds)/60.0 AS BreakMinutes, (SUM(WorkingSeconds)/3600.0)+(COALESCE(SUM(a.BreakDurationSeconds)/3600.0,0)) AS TotalHours, 
       MAX(CASE WHEN rn = 1 THEN WorkingSeconds END)/3600.0 AS FirstLength,
       SUBSTRING(CASE WHEN COUNT(BreakDurationSeconds) > 1 THEN 'VIOLATION: '
            WHEN MAX(CASE WHEN rn = 1 THEN WorkingSeconds END)/3600.0 > 6 THEN 'VIOLATION: '
            WHEN SUM(BreakDurationSeconds)/60.0 < 30 THEN 'VIOLATION: '
            ELSE ', '
        END +
        CASE WHEN COUNT(BreakDurationSeconds) > 1 THEN 'TOO MANY BREAKS, ' ELSE '' END+
        CASE WHEN MAX(CASE WHEN rn = 1 THEN WorkingSeconds END)/3600.0 > 6 THEN 'BREAKS TOO LATE, ' ELSE '' END+
        CASE WHEN SUM(BreakDurationSeconds)/60.0 < 30 THEN 'BREAKS TOO SHORT, ' ELSE '' END,
        0,LEN(CASE WHEN COUNT(BreakDurationSeconds) > 1 THEN 'VIOLATION: '
            WHEN MAX(CASE WHEN rn = 1 THEN WorkingSeconds END)/3600.0 > 6 THEN 'VIOLATION: '
            WHEN SUM(BreakDurationSeconds)/60.0 < 30 THEN 'VIOLATION: '
            ELSE ', '
        END +
        CASE WHEN COUNT(BreakDurationSeconds) > 1 THEN 'TOO MANY BREAKS, ' ELSE '' END+
        CASE WHEN MAX(CASE WHEN rn = 1 THEN WorkingSeconds END)/3600.0 > 6 THEN 'BREAKS TOO LATE, ' ELSE '' END+
        CASE WHEN SUM(BreakDurationSeconds)/60.0 < 30 THEN 'BREAKS TOO SHORT, ' ELSE '' END)) AS Violations
  FROM (
SELECT EmployeeID, CAST(DateStart AS DATE) AS WorkDate, DateStart, DateEnd, DATEDIFF(SECOND,DateEnd,LEAD(DateStart,1) OVER (PARTITION BY EmployeeID, CAST(DateStart AS DATE) ORDER BY DateStart)) AS BreakDurationSeconds,
       DATEDIFF(SECOND,DateStart,DateEnd) AS WorkingSeconds, ROW_NUMBER() OVER (PARTITION BY EmployeeID, CAST(DateStart AS DATE) ORDER BY DateStart) AS rn
  FROM @Table
       ) a
 GROUP BY EmployeeID, WorkDate

There some nifty tricks going on here. Once of them is just to provided the flavor on the Violation column. It's just a bit of string manipulation and case expressions (which you can tweak, but make sure to do it on both sides)

There's a ROW_NUMBER() in there which flags the first shifts, so we can tell how long they were. We use that in the case expression to check if it has a rn of 1.

There's a LEAD windowed function which find the next row, if it's still the same day, so we can figure out how long the break was.

From there, it's just a bunch of datetime math to figure out durations and what not.
| EmployeeID | WorkDate | Breaks | BreakMinutes | TotalHours | FirstLength | Violations |
| ------------ | ------------ | ------------ | ------------ | ------------ | ------------ | ------------ |
| 1 | 2023-02-06 | 1 | 32.733333 | 9.467221 | 5.712500 | |
| 1 | 2023-02-07 | 1 | 29.033333 | 8.763888 | 5.636388 | VIOLATION: BREAKS TOO SHORT |
| 1 | 2023-02-08 | 1 | 40.850000 | 8.607777 | 5.791111 | |
| 1 | 2023-02-09 | 2 | 34.366666 | 8.540832 | 5.713888 | VIOLATION: TOO MANY BREAKS |
| 1 | 2023-02-10 | 2 | 9.433333 | 6.863055 | 7.979722 | VIOLATION: TOO MANY BREAKS, BREAKS TOO LATE, BREAKS TOO SHORT |

相关问题