To Decide timein timeout roster after an employees actual incoming time. A case of difficult order by

Transact-SQL
4

hi Mateen

hope this helps

create tables weeks entries script 
drop  table Entries
create table  Entries  (Entrytime datetime, timein varchar(8) ,dayno int)
insert Entries select '2021-05-14 07:56:00','07:56:00',14

drop table weeks
create table   weeks  (dayno int, weekno int)
INSERT INTO weeks ([dayno],[weekno])VALUES(1,1)
INSERT INTO weeks ([dayno],[weekno])VALUES(2,1)
INSERT INTO weeks ([dayno],[weekno])VALUES(3,1)
INSERT INTO weeks ([dayno],[weekno])VALUES(4,1)
INSERT INTO weeks ([dayno],[weekno])VALUES(5,1)
INSERT INTO weeks ([dayno],[weekno])VALUES(6,1)
INSERT INTO weeks ([dayno],[weekno])VALUES(7,2)
INSERT INTO weeks ([dayno],[weekno])VALUES(8,2)
INSERT INTO weeks ([dayno],[weekno])VALUES(9,2)
INSERT INTO weeks ([dayno],[weekno])VALUES(10,2)
INSERT INTO weeks ([dayno],[weekno])VALUES(11,2)
INSERT INTO weeks ([dayno],[weekno])VALUES(12,2)
INSERT INTO weeks ([dayno],[weekno])VALUES(13,2)
INSERT INTO weeks ([dayno],[weekno])VALUES(14,3)
INSERT INTO weeks ([dayno],[weekno])VALUES(15,3)
INSERT INTO weeks ([dayno],[weekno])VALUES(16,3)
INSERT INTO weeks ([dayno],[weekno])VALUES(17,3)
INSERT INTO weeks ([dayno],[weekno])VALUES(18,3)
INSERT INTO weeks ([dayno],[weekno])VALUES(19,4)
INSERT INTO weeks ([dayno],[weekno])VALUES(20,4)
INSERT INTO weeks ([dayno],[weekno])VALUES(21,4)
INSERT INTO weeks ([dayno],[weekno])VALUES(22,4)
INSERT INTO weeks ([dayno],[weekno])VALUES(23,4)
INSERT INTO weeks ([dayno],[weekno])VALUES(24,4)
INSERT INTO weeks ([dayno],[weekno])VALUES(25,5)
INSERT INTO weeks ([dayno],[weekno])VALUES(26,5)
INSERT INTO weeks ([dayno],[weekno])VALUES(27,5)
INSERT INTO weeks ([dayno],[weekno])VALUES(28,5)
INSERT INTO weeks ([dayno],[weekno])VALUES(29,5)
INSERT INTO weeks ([dayno],[weekno])VALUES(30,5)
INSERT INTO weeks ([dayno],[weekno])VALUES(31,5)

t-sql === for all your scenarios


;WITH employeeSchedule AS (
    SELECT timein= '08:00:00', timeout= '17:00:00', weekno=1
    UNION ALL SELECT timein= '20:00:00', timeout= '05:00:00', weekno=2
    UNION ALL SELECT timein= '08:00:00', timeout= '15:00:00', weekno=3
    UNION ALL SELECT timein= '20:00:00', timeout= '04:00:00', weekno=4
    UNION ALL SELECT timein= '08:00:00', timeout= '16:00:00', weekno=5
),
cte AS (
    SELECT s.*, 
           t.Entrytime, 
           t.timein AS entry_time, 
           t.dayno,
           ABS(DATEDIFF(SECOND,
                CONVERT(DATETIME,'1969/06/19 ' + t.timein),
                CONVERT(DATETIME,'1969/06/19 ' + s.timein))) AS diff_seconds,
           (SELECT weekno FROM weeks wk WHERE wk.dayno = t.dayno) AS relatedweekno,
           ROW_NUMBER() OVER (ORDER BY 
                ABS(DATEDIFF(SECOND,
                    CONVERT(DATETIME,'1969/06/19 ' + t.timein),
                    CONVERT(DATETIME,'1969/06/19 ' + s.timein)))
           ) AS rn
    FROM employeeSchedule s
    JOIN entries t ON 1=1
)
SELECT *
FROM cte
WHERE rn = 1;