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

Transact-SQL
1

I have Sql Server 2008

I pray, the expert understand, what I am asking.

--We have 5 weeks in a month.
--We know which dayno relates to which weekno from weeks table
--An employee can come in five different times [employeeSchedule]
--When an emplooyes comes table can be
drop table Entries
create table Entries (Entrytime datetime)
insert Entries select '2021-05-12 07:56:00'

--When he comes , we calculate the nearest timein from schedule
--We have no issue in calculating nearest time by applying datediff functoin and storing diff_secs.
--We then sort the [employeeSchedule] order by seconds ascending and pick first row

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)

;with employeeSchedule as (		   select timein=  '08', timeout=  '17', diff_secs = 3000,  weekno=1
			union all  select timein=  '10', timeout=  '19', diff_secs = 4000, weekno=2
			union all  select timein = '11', timeout = '19', diff_secs = 5000,  weekno=3
			union all  select timein = '20', timeout = '04', diff_secs = 6000, weekno=4
			union all  select timein = '21', timeout = '05', diff_secs = 7000,  weekno=5
)
select top 1 *
from employeeSchedule
order by diff_secs asc  -- order by diff_secs is compulsory, and after that need another sort operator

--and the result is
--timein timeout diff_secs weekno
--08 17 3000 1

--Upto this point there is no issue and everything is going well and working.
--There is no issue because all timein is distinct.

--Lest Change EmployeeSchedule

insert Entries select '2021-05-14 07:56:00'

;with employeeSchedule as ( select timein= '08', timeout= '17', diff_secs = 3000, weekno=1
union all select timein= '20', timeout= '05', diff_secs = 4000, weekno=2
union all select timein = '08', timeout = '15', diff_secs = 3000, weekno=3
union all select timein = '20', timeout = '04', diff_secs = 4000, weekno=4
union all select timein = '08', timeout = '16', diff_secs = 3000, weekno=5
)
select top 1 *
from employeeSchedule
order by diff_secs asc --,'something else alse needed which relates dayno to weekno'

-- now it will not work as what i want, it will again pick weekno 1
-- It should pick weekno 3 because 14 fall in 3rd is.

--To be Brief
--Pick one row from employeeSchedule on diff_secs sort order when all timein is same.
--If alltimein is not same then
--pick Pick one row from employeeSchedule on diff_secs
--and within that same diff_secs choose the coorect weekno rows.

insert Entries select '2021-05-26 07:56:00'
;with employeeSchedule as ( select timein= '08', timeout= '17', diff_secs = 3000, weekno=1
union all select timein= '20', timeout= '05', diff_secs = 4000, weekno=2
union all select timein = '08', timeout = '15', diff_secs = 3000, weekno=3
union all select timein = '20', timeout = '04', diff_secs = 4000, weekno=4
union all select timein = '08', timeout = '16', diff_secs = 3000, weekno=5
)
select top 1 *
from employeeSchedule
order by diff_secs asc ,'something else alseo needed which relates dayno to weekno'
--it should pick weekno 5

insert Entries select '2021-05-20 07:56:00'
;with employeeSchedule as ( select timein= '08', timeout= '17', diff_secs = 3000, weekno=1
union all select timein= '20', timeout= '05', diff_secs = 4000, weekno=2
union all select timein = '08', timeout = '15', diff_secs = 3000, weekno=3
union all select timein = '20', timeout = '04', diff_secs = 4000, weekno=4
union all select timein = '08', timeout = '16', diff_secs = 3000, weekno=5
)
select top 1 *
from employeeSchedule
order by diff_secs asc ,'something else alseo needed which relates dayno to weekno'

--It should pick any one row of weekno 1,3,5 because he has come completely out of schedule.

Ideally an employee should come according to fixed roster.
If the roster match he is present else absent.
We have done very wrong by giving the facility of coming at anytime any day
deciding the roster after his coming

Please execute my entire post#2 in your query analyzer, and see what I want.

2

Hi Experts

declare @Entries table (Entrytime datetime, timein varchar(8) ,dayno int)

declare  @weeks table (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)


insert @Entries select '2021-05-14 07:56:00','07:56:00',14
--Lest Change EmployeeSchedule
;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
)
select  *
,diff_seconds = abs(datediff(s,convert(datetime,'1969/06/19' +
                                           ' ' + t.timein),
                  convert(datetime,'1969/06/19' + ' ' + s.timein)))
, relatedweekno=(select weekno from @weeks wk
   where wk.dayno = t.dayno)
from employeeSchedule s
join @entries t on 1=1
timein timeout weekno Entrytime timein dayno diff_seconds relatedweekno
08:00:00 17:00:00 1 2021-05-14 07:56:00.000 07:56:00 14 240 3
20:00:00 05:00:00 2 2021-05-14 07:56:00.000 07:56:00 14 43440 3
08:00:00 15:00:00 3 2021-05-14 07:56:00.000 07:56:00 14 240 3
20:00:00 04:00:00 4 2021-05-14 07:56:00.000 07:56:00 14 43440 3
08:00:00 16:00:00 5 2021-05-14 07:56:00.000 07:56:00 14 240 3

I want row number 3

delete from @entries
insert @Entries select '2021-05-24 07:56:00','07:56:00',24
--Lest Change EmployeeSchedule
;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
)
select  *
,diff_seconds = abs(datediff(s,convert(datetime,'1969/06/19' +
                                           ' ' + t.timein),
                  convert(datetime,'1969/06/19' + ' ' + s.timein)))
, relatedweekno=(select weekno from @weeks wk
   where wk.dayno = t.dayno)
from employeeSchedule s
join @entries t on 1=1
timein timeout weekno Entrytime timein dayno diff_seconds relatedweekno
08:00:00 17:00:00 1 2021-05-24 07:56:00.000 07:56:00 24 240 4
20:00:00 05:00:00 2 2021-05-24 07:56:00.000 07:56:00 24 43440 4
08:00:00 15:00:00 3 2021-05-24 07:56:00.000 07:56:00 24 240 4
20:00:00 04:00:00 4 2021-05-24 07:56:00.000 07:56:00 24 43440 4
08:00:00 16:00:00 5 2021-05-24 07:56:00.000 07:56:00 24 240 4

I want any one of row number 1,3,5

and for this


delete from @entries
insert @Entries select '2021-05-07 19:56:00','20:05:00',7
--Lest Change EmployeeSchedule
;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
)
select  *
,diff_seconds = abs(datediff(s,convert(datetime,'1969/06/19' +
                                           ' ' + t.timein),
                  convert(datetime,'1969/06/19' + ' ' + s.timein)))
, relatedweekno=(select weekno from @weeks wk
   where wk.dayno = t.dayno)
from employeeSchedule s
join @entries t on 1=1
timein timeout weekno Entrytime timein dayno diff_seconds relatedweekno
08:00:00 17:00:00 1 2021-05-07 19:56:00.000 20:05:00 7 43500 2
20:00:00 05:00:00 2 2021-05-07 19:56:00.000 20:05:00 7 300 2
08:00:00 15:00:00 3 2021-05-07 19:56:00.000 20:05:00 7 43500 2
20:00:00 04:00:00 4 2021-05-07 19:56:00.000 20:05:00 7 300 2
08:00:00 16:00:00 5 2021-05-07 19:56:00.000 20:05:00 7 43500 2

I want row number 2

in all cases we need to pick from smallest diff_seconds group.

3

Moderator please delete this question.