SQL Server TIMECHECKIN & TIMECHECKOUT for Night shift

zlhcx6iw  于 2023-04-19  发布在  其他
关注(0)|答案(1)|浏览(114)

Name: TBLLOGFULL
| IDUser | TimeCheck |
| ------------ | ------------ |
| 22052939 | 2/Apr/2023 9:32:45 PM |
| 22052939 | 2/Apr/2023 9:33:45 PM |
| 22052939 | 3/Apr/2023 6:07:52 AM |
| 22052939 | 3/Apr/2023 6:04:52 AM |
| 22052939 | 4/Apr/2023 6:04:52 AM |
| 22052939 | 4/Apr/2023 6:05:54 PM |
| 22052939 | 4/Apr/2023 2:00:52 PM |
| 22052939 | 4/Apr/2023 2:04:54 PM |
| 22052939 | 5/Apr/2023 2:00:52 AM |
| 22052939 | 5/Apr/2023 2:01:54 PM |
| 22052939 | 5/Apr/2023 10:00:52 PM |
| 22052939 | 5/Apr/2023 10:04:54 PM |

As my script below, TIMECHECKIN for S3 is Max(Datetime) of the previous day and TIMECHECKOUT is MIN(Datetime)today Else is MIN(Datetime)today.

My expected is TIMECHECKIN for S3 is MIN(Datetime) between 8PM to 11PM of the previous day and TIMECHECKOUT is Max(Datetime) between 5AM to 7Am today Else is Max(Datetime)today.

SELECT IDuser, 
       CAST(MIN(TimeCheck) AS DATE) AS 'WORKINGDATE', 
       CASE 
         WHEN DATEADD(day, 1, MIN(TimeCheck)) - MAX(TimeCheck) < '11:00:00' THEN 'S3'
         WHEN MIN(DATEPART(hour,TimeCheck)) BETWEEN 5 AND 7 AND MAX(DATEPART(hour,TimeCheck)) BETWEEN 13 AND 18 THEN 'S1'
         WHEN MIN(DATEPART(hour,TimeCheck)) BETWEEN 10 AND 15 AND MAX(DATEPART(hour,TimeCheck)) BETWEEN 21 AND 23 THEN 'S2'
       END AS 'WORKINGSHIFT',
       CASE
            WHEN DATEADD(day, 1, MIN(TimeCheck)) - MAX(TimeCheck) < '11:00:00' THEN DATEADD(day, -1, CAST(MAX(TimeCheck) AS dateTIME)) ELSE MIN(TimeCheck)
            END AS 'TIMECHECKIN',
       CASE
            WHEN DATEADD(day, 1, MIN(TimeCheck)) - MAX(TimeCheck) < '11:00:00' THEN MIN(TimeCheck) ELSE MAX(TimeCheck)
            END AS 'TIMECHECKOUT'
        --CAST(MIN(TimeCheck) AS DATETIME) AS 'TIMECHECKIN1',
        --CAST(MAX(TimeCheck) AS DATETIME) AS 'TIMECHECKOUT1'
        --CAST(TimeCheck AS DATE) AS 'WORKINGDATE'

FROM TBllogfull
  --where IDUser ='22052939'and month(timecheck) = '03' and YEAR(timecheck)='2023'
GROUP BY IDuser, CAST(TimeCheck AS DATE)

As my current Script, TIMECHECKIN for S3 is Max(Datetime) of the previous day and TIMECHECKOUT is MIN(Datetime)today Else is MIN(Datetime)today.

**IDUserWORKINGDATETIMECHECKINTIMECHECKOUT**
220529392/Apr/20232/Apr/2023 9:33:45 PM3/Apr/2023 6:04:52 AM

My expected is TIMECHECKIN for S3 is MIN(Datetime) between 8PM to 11PM of the previous day and TIMECHECKOUT is Max(Datetime) between 5AM to 7Am today Else is Max(Datetime)today.

**IDUserWORKINGDATETIMECHECKINTIMECHECKOUT**
220529392/Apr/20232/Apr/2023 9:33:45 PM3/Apr/2023 6:07:52 AM
vq8itlhq

vq8itlhq1#

After you have your shifts gruped correctly and ready to process - like in your previous question , you may use that as a subquery to get the type of the workshift.

SELECT *, CAST(TimeCheckIn as DATE) WorkshiftDay, 
      CASE 
         WHEN DayCheckIn<> pairs.DayCheckOut AND  pairs.HourCheckOut < 11 THEN 'S3'
         WHEN HourCheckIn BETWEEN 5 AND 7 AND HourCheckOut BETWEEN 13 AND 18 THEN 'S1'
         WHEN HourCheckIn BETWEEN 10 AND 15 AND HourCheckOut BETWEEN 21 AND 23 THEN 'S2'
       END AS 'WORKINGSHIFT'
FROM 
(
    SELECT IDUser,        
           MIN(TimeCheck) AS TimeCheckIn,
           MAX(TimeCheck) AS TimeCheckOut,
           DATEPART(hour,MIN(TimeCheck)) AS HourCheckIn,
           DATEPART(hour,MAX(TimeCheck)) AS HourCheckOut,
           DATEPART(day,MIN(TimeCheck)) AS DayCheckIn,
           DATEPART(day,MAX(TimeCheck)) AS DayCheckOut
    FROM (
      SELECT *,
             ROW_NUMBER() OVER (PARTITION BY IDUser ORDER BY TimeCheck) AS rn
      FROM TBLLOGFULL
    ) AS t
    GROUP BY IDUser,  FLOOR((rn - 1) / 2)
    HAVING COUNT(*) = 2    
) pairs
ORDER BY IDUser

https://dbfiddle.uk/0Q8hKRFm

I.e. this is the second part of the problem, which can be written like:

SELECT *, CAST(TimeCheckIn as DATE) WorkshiftDay, 
      CASE 
         WHEN DayCheckIn <> DayCheckOut AND HourCheckOut < 11 THEN 'S3'
         WHEN DayCheckIn = DayCheckOut  AND HourCheckIn BETWEEN 5 AND 7 AND HourCheckOut BETWEEN 13 AND 18 THEN 'S1'
         WHEN DayCheckIn = DayCheckOut  AND HourCheckIn BETWEEN 10 AND 15 AND HourCheckOut BETWEEN 21 AND 23 THEN 'S2'
       END AS 'WORKINGSHIFT'
FROM WorkShiftsView 
ORDER BY IDUser

and

CREATE VIEW WorkShiftsView AS
    SELECT IDUser,        
           MIN(TimeCheck) AS 'TimeCheckIn',
           MAX(TimeCheck) AS 'TimeCheckOut',
           DATEPART(hour,MIN(TimeCheck)) AS HourCheckIn,
           DATEPART(hour,MAX(TimeCheck)) AS HourCheckOut,
           DATEPART(day,MIN(TimeCheck)) AS DayCheckIn,
           DATEPART(day,MAX(TimeCheck)) AS DayCheckOut
    FROM (
      SELECT *,
             ROW_NUMBER() OVER (PARTITION BY IDUser ORDER BY TimeCheck) AS rn
      FROM TBLLOGFULL
    ) AS t
    GROUP BY IDUser,  FLOOR((rn - 1) / 2)
    HAVING COUNT(*) = 2

Thus you can separate the previous logic of finding shifts and solve the two problems separately. You see that then the second task is very simple, the tricky part is to get your shifts data from chronological log.

https://dbfiddle.uk/X6rfaZyv

If you want to solve another problem of duplicate checks, you should add another layer to remove duplicates (less than 5 minutes) like the code bellow and use FixDuplicateChecks view instead of reference to TBLLOGFULL table:

https://dbfiddle.uk/YvjH14Ro

CREATE VIEW FixDuplicateChecks AS
SELECT T1.IDUser, T1.TimeCheck
FROM TBLLOGFULL T1
WHERE NOT EXISTS (
   SELECT 1
   FROM TBLLOGFULL T2
   WHERE T2.IDUser = T1.IDUser
   AND T2.TimeCheck > DATEADD(MINUTE, -5, T1.TimeCheck)
   AND T2.TimeCheck < T1.TimeCheck
)

相关问题