Put you shift timing into a table and make a join to your master shift table
with acceptable timerange, as given below
Create Table #masterShift(UserIn int,shShiftInTime datetime,shShiftOutTime datetime)
insert #masterShift select 1,'Nov 17 2014 07:00','Nov 17 2014 15:00'
insert #masterShift select 1,'Nov 17 2014 10:00','Nov 17 2014 18:00'
insert #masterShift select 1,'Nov 17 2014 23:00','Nov 18 2014 07:00'
Create Table #shiftTiming(Shift varchar(15),shShiftInTime time,shShiftOutTime time)
insert into #shiftTiming select 'General Shift','10:00 AM','06:00 PM'
insert into #shiftTiming select 'First Shift','07:00 AM','03:00 PM'
insert into #shiftTiming select 'Night Shift','23:00 PM','07:00 AM'
select *,Dateadd(HH,2,tym.shShiftInTime)
from
#masterShift sht
Full Outer Join #shiftTiming tym
On Convert(time,sht.shShiftInTime)>=tym.shShiftInTime
and Convert(time,sht.shShiftInTime)<=Dateadd(MI,59,tym.shShiftInTime)
Go
insert #masterShift select 1,'Nov 18 2014 07:30','Nov 18 2014 15:00'
insert #masterShift select 1,'Nov 18 2014 10:30','Nov 18 2014 18:00'
insert #masterShift select 1,'Nov 18 2014 23:30','Nov 19 2014 07:00'
select *
from
#masterShift sht
Join #shiftTiming tym On Convert(time,sht.shShiftInTime)>=tym.shShiftInTime and Convert(time,sht.shShiftInTime)<=Dateadd(MI,59,tym.shShiftInTime)
here time difference of 59 minutes is given, Not as sson you will increase this time your time will change and start with 0 , so work with that to get you required result.