Click here to Skip to main content
15,392,679 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:

I have three shifts with below timings.
                 Intime         Outtime

General Shift :  10:00 AM       06:00 PM
First Shift   :  07:00 AM       03:00 PM
Night Shift   :  23:00 PM       07:00 AM

Now , Suppose if any employee has punch at 10:00 AM, 10:30 AM then it should give me General Shift.

If any employee has punch at 23:30, 23:45,00:30 etc.. then it should return Night Shift. I tried below query for that.
select aPKShift,  shShiftInTime, shShiftOutTime
from amasterShift
(CONVERT(Time,shShiftInTime) >= convert(time,'10:00:00')
CONVERT(Time,shShiftOutTime) <= convert(time,'10:00:00'))

But its giving me wrong shifts like this
Night         23:00:00.0000000    07:00:00.0000000
Third         23:00:00.0000000    07:00:00.0000000
Sixth Shift   21:00:00.0000000    06:00:00.0000000

Please provide some suggestions for the above mentioned query
Updated 16-Nov-14 20:45pm
Richard MacCutchan 17-Nov-14 3:57am
Shift out time should be greater than shift in, and less than latest shift out of the day. You have shift out less than 10 which does not look right.
Raghava jogu 18-Nov-14 9:33am
We get shift out time less than shift in time in case of Night Shifts with different dates.
Richard MacCutchan 18-Nov-14 10:04am
Then you obviously need to use a full DateTime type, not just the time.
Raghava jogu 21-Nov-14 5:14am
how can i do even if is with full datetime?
Richard MacCutchan 21-Nov-14 5:17am
Since a DateTime type holds the date and the time, you can always check which one is earlier than the other. And the difference between the two will give you the actual number of hours in the shift.
Raghava jogu 21-Nov-14 6:06am
I need shift name which lies between particular time not the difference of two datetimes.
Richard MacCutchan 21-Nov-14 6:10am
You have all the information about the start and end times. What is the problem?
Raghava jogu 21-Nov-14 6:15am
when i use between or >= and <= then it gives me wrong shift names. suppose i find out shift for the time 23:00 then result will be empty.

When i try with 10:00 then its returns actual shift names. This is not working with 24 hours format i.e. too after 12:00
Richard MacCutchan 21-Nov-14 6:36am
Then you are either doing it wrong or your data is incorrect. As I have stated a number of times, if you always store your timestamps as DateTime values it will work. That is the only way you can create this type of application; it will handle all times and always take account of times that cross midnight.
Shweta N Mishra 17-Nov-14 11:02am
did solution1 didnt worked for you ? have you got any other solution then post it.
Raghava jogu 18-Nov-14 9:33am
Not yet
Shweta N Mishra 18-Nov-14 9:36am
whats the issue
Raghava jogu 21-Nov-14 5:14am
Please refer my query above

1 solution

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)
#masterShift sht
Full Outer Join #shiftTiming tym
On Convert(time,sht.shShiftInTime)>=tym.shShiftInTime
and Convert(time,sht.shShiftInTime)<=Dateadd(MI,59,tym.shShiftInTime)


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 *
#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.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900