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.
**IDUser | WORKINGDATE | TIMECHECKIN | TIMECHECKOUT | ** |
---|---|---|---|---|
22052939 | 2/Apr/2023 | 2/Apr/2023 9:33:45 PM | 3/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.
**IDUser | WORKINGDATE | TIMECHECKIN | TIMECHECKOUT | ** |
---|---|---|---|---|
22052939 | 2/Apr/2023 | 2/Apr/2023 9:33:45 PM | 3/Apr/2023 6:07:52 AM |
1条答案
按热度按时间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.
https://dbfiddle.uk/0Q8hKRFm
I.e. this is the second part of the problem, which can be written like:
and
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