Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- SELECT MIN(a.CHECKTIME) as work_start_time, MAX(a.CHECKTIME) as work_end_time, b.CheckDate as work_date, WEEKDAY(b.CheckDate,2) as work_day, a.USERID as user_id
- FROM CHECKINOUT a RIGHT JOIN (
- SELECT DISTINCT
- DateValue(CHECKTIME) as CheckDate,
- USERID FROM CHECKINOUT
- WHERE ( MONTH(CHECKTIME) = 4 )
- AND YEAR(CHECKTIME) = 2019 AND USERID =50
- )
- b on a.USERID = b.USERID
- and DateValue(a.CHECKTIME) = b.CheckDate GROUP BY b.checkDate, a.USERID
- Work_start_Time | work_end_time | Work_date | Work_day |User_ID
- 1/04/2019 8:30:19 AM | 1/04/2019 8:30:19 AM | 1/04/2019| 1 |50
- 2/04/2019 12:16:38 AM | 2/04/2019 9:40:52 AM | 2/04/2019| 2 |50
- 3/04/2019 1:25:29 AM | 3/04/2019 1:25:29 AM | 3/04/2019| 3 |50
- 4/04/2019 7:09:26 AM | 4/04/2019 9:10:37 PM | 4/04/2019| 4 |50
- SELECT MIN(a.CHECKTIME) as work_start_time,
- IF (MIN(a.CHECKTIME+1) < 5:00:00 AM )
- MIN(a.CHECKTIME+1) as work_end_time,
- ELSE
- MAX(a.CHECKTIME) as work_end_time,
- b.CheckDate as work_date, WEEKDAY(b.CheckDate,2) as work_day, a.USERID as user_id
- FROM CHECKINOUT a RIGHT JOIN (
- SELECT DISTINCT
- DateValue(CHECKTIME) as CheckDate,
- USERID FROM CHECKINOUT
- WHERE ( MONTH(CHECKTIME) = 4 )
- AND YEAR(CHECKTIME) = 2019 AND USERID =50
- )
- b on a.USERID = b.USERID
- and DateValue(a.CHECKTIME) = b.CheckDate GROUP BY b.checkDate, a.USERID
- Work_start_Time | work_end_time | Work_date | Work_day |User_ID
- 1/04/2019 8:30:19 AM | 2/04/2019 12:16:38AM | 1/04/2019| 1 |50
- 2/04/2019 9:40:52 AM | 3/04/2019 1:25:29 AM | 2/04/2019| 2 |50
- 3/04/2019 1:25:29 AM | 3/04/2019 1:25:29 AM | 3/04/2019| 3 |50
- 4/04/2019 7:09:26 AM | 4/04/2019 9:10:37 PM | 4/04/2019| 4 |50
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement