SQL 4 shift pattern having issue with night shift.. as it is clashing with morning time

1

I'm having a problem with night shift person clocks. Say
night shift BETWEEN '17:30' AND '20:00' and clocks out at 8am,
now morning person clocks on at 6am to 7.30am

they both clash

Add: this is employee clocking in.out,late and absent report

sql so far:

WITH CTE AS
  (SELECT CONVERT(varchar(5), c.CHECKTIME, 108) AS CHECKTIME,
          CASE
          
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '17:30' AND '20:00' THEN 'night_IN'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '20:01' AND '20:30' THEN 'night_LATE'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '20:31' AND '23:00' THEN 'night_ABSENT'
              WHEN CONVERT(VARCHAR(16),c.CHECKTIME,20) BETWEEN '00:00' AND '08:00' THEN 'night_OUT'
              
              
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '06:30' AND '07:00' THEN 'morning_IN'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '07:01' AND '07:30' THEN 'morning_LATE'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '07:31' AND '11:00' THEN 'morning_ABSENT'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '11:45' AND '13:00' THEN 'morning_OUT'
              
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '15:30' AND '16:00' THEN 'afternoon_IN'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '16:01' AND '16:30' THEN 'afternoon_LATE'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '16:31' AND '17:15' THEN 'afternoon_ABSENT'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '18:00' AND '19:00' THEN 'afternoon_OUT'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '13:05' AND '14:00' THEN 'afternoon2_IN'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '14:01' AND '14:30' THEN 'afternoon2_LATE'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '14:31' AND '16:15' THEN 'afternoon2_ABSENT'
              WHEN CONVERT(VARCHAR(5),c.CHECKTIME,108) BETWEEN '17:00' AND '18:00' THEN 'afternoon2_OUT'
              
          END AS TIME_DIVISION,
          CONVERT(varchar(10), c.CHECKTIME, 20) AS DT,
          u.BADGENUMBER,
          u.NAME,
          u.TITLE,
          d.DEPTNAME
   FROM CHECKINOUT c
   INNER JOIN USERINFO u ON c.USERID = u.USERID
   INNER JOIN DEPARTMENTS d ON d.DEPTID = u.DEFAULTDEPTID
   WHERE u.BADGENUMBER = 23)
SELECT *
FROM CTE 
PIVOT (Max(checktime)
                FOR time_division IN (morning_IN,morning_LATE,morning_ABSENT,morning_OUT, afternoon_IN,afternoon_LATE,afternoon_ABSENT,afternoon_OUT, afternoon2_IN,afternoon2_LATE,afternoon2_ABSENT,afternoon2_OUT, night_IN,night_LATE,night_ABSENT,night_OUT)) AS pvt

where DT between '2015-10-24' and '2015-10-26'

result:

DT    BADGENUMBER    NAME    TITLE    DEPTNAME    morning_IN    morning_LATE    morning_ABSENT    morning_OUT    afternoon_IN    afternoon_LATE    afternoon_ABSENT    afternoon_OUT    afternoon2_IN    afternoon2_LATE    afternoon2_ABSENT    afternoon2_OUT    night_IN    night_LATE    night_ABSENT    night_OUT
2015-10-24    23    James    HQ    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    21:35    NULL
2015-10-25    23    James        HQ    NULL    07:10    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL

Should be:

DT    BADGENUMBER    NAME    TITLE    DEPTNAME    morning_IN    morning_LATE    morning_ABSENT    morning_OUT    afternoon_IN    afternoon_LATE    afternoon_ABSENT    afternoon_OUT    afternoon2_IN    afternoon2_LATE    afternoon2_ABSENT    afternoon2_OUT    night_IN    night_LATE    night_ABSENT    night_OUT
2015-10-24    23    James    HQ    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    NULL    21:35    07:10
2

assuming that your time-in-out always in pair

i added a CTE to determine which Time is IN and which is OUT.

i only change the first part of the query to add the InOut logic. Change the rest accordiingly

WITH INOUT
AS (
	SELECT *,
		InOut = CASE 
			WHEN ROW_NUMBER() OVER (
					PARTITION BY USERID ORDER BY CHECKTIME
					) % 2 = 1
				THEN 'IN'
			ELSE 'OUT'
			END
	FROM CHECKINOUT
	),
CTE
AS (
	SELECT CONVERT(VARCHAR(5), c.CHECKTIME, 108) AS CHECKTIME,
		CASE 
			WHEN InOut = 'IN'
				AND CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '17:30'
					AND '20:00'
				THEN 'night_IN'
			WHEN InOut = 'IN'
				AND CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '20:01'
					AND '20:30'
				THEN 'night_LATE'
			WHEN InOut = 'IN'
				AND CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '20:31'
					AND '23:00'
				THEN 'night_ABSENT'
			WHEN InOut = 'OUT'
				AND CONVERT(VARCHAR(16), c.CHECKTIME, 20) BETWEEN '00:00'
					AND '08:00'
				THEN 'night_OUT'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '06:30'
					AND '07:00'
				THEN 'morning_IN'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '07:01'
					AND '07:30'
				THEN 'morning_LATE'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '07:31'
					AND '11:00'
				THEN 'morning_ABSENT'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '11:45'
					AND '13:00'
				THEN 'morning_OUT'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '15:30'
					AND '16:00'
				THEN 'afternoon_IN'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '16:01'
					AND '16:30'
				THEN 'afternoon_LATE'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '16:31'
					AND '17:15'
				THEN 'afternoon_ABSENT'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '18:00'
					AND '19:00'
				THEN 'afternoon_OUT'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '13:05'
					AND '14:00'
				THEN 'afternoon2_IN'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '14:01'
					AND '14:30'
				THEN 'afternoon2_LATE'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '14:31'
					AND '16:15'
				THEN 'afternoon2_ABSENT'
			WHEN CONVERT(VARCHAR(5), c.CHECKTIME, 108) BETWEEN '17:00'
					AND '18:00'
				THEN 'afternoon2_OUT'
			END AS TIME_DIVISION,
		CONVERT(VARCHAR(10), c.CHECKTIME, 20) AS DT,
		u.BADGENUMBER,
		u.NAME,
		u.TITLE,
		d.DEPTNAME
	FROM INOUT c
	INNER JOIN USERINFO u
		ON c.USERID = u.USERID
	INNER JOIN DEPARTMENTS d
		ON d.DEPTID = u.DEFAULTDEPTID
	WHERE u.BADGENUMBER = 23
	)
SELECT *
FROM CTE
PIVOT(Max(checktime) FOR time_division IN (
			morning_IN,
			morning_LATE,
			morning_ABSENT,
			morning_OUT,
			afternoon_IN,
			afternoon_LATE,
			afternoon_ABSENT,
			afternoon_OUT,
			afternoon2_IN,
			afternoon2_LATE,
			afternoon2_ABSENT,
			afternoon2_OUT,
			night_IN,
			night_LATE,
			night_ABSENT,
			night_OUT
			)) AS pvt
WHERE DT BETWEEN '2015-10-24'
		AND '2015-10-26'