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
1条答案
按热度按时间zengzsys1#
Taking your example data (and adding a couple of extra rows):
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 |