How Do I Get Employee Shift Which Lies Between First Punch Or Intime

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

Hi,

I have three shifts with below timings.

C#

                 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.

SQL

select aPKShift,  shShiftInTime, shShiftOutTime
from amasterShift
WHERE
(CONVERT(Time,shShiftInTime) >= convert(time,'10:00:00')
and
CONVERT(Time,shShiftOutTime) <= convert(time,'10:00:00'))

But its giving me wrong shifts like this

CSS

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

Posted 16-Nov-14 20:23pm

Raghava jogu

Updated 16-Nov-14 20:45pm

Thanks7872

v2

Add a Solution

Comments

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

Shweta N Mishra · Answer 1 · 2014-11-16T22:42:00

Put you shift timing into a table and make a join to your master shift table

with acceptable timerange, as given below

SQL

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.