Solved

SQL Datediff time delimma

Posted on 2009-04-10
3
866 Views
Last Modified: 2012-06-27
I am trying to set up a statement that will calculate the hours correctly and deduct the break time if the employee has worked over 6 hours.
Here is my current statement that does return the correct minutes.  

case
                              when datediff(hour, min(s.arriveDateTime), max(s.departDateTime)) > 6 then
                                    datediff(minute, min(s.arriveDateTime), max(s.departDateTime)) - 30
                              else
                                    datediff(minute, min(s.arriveDateTime), max(s.departDateTime))
                        end DutyMinutes,

This does return the correct number of minutes but here is my problem:
A person has a start time of 7:20 and a end time of 17:55&.which equates to 10 hours and 35 minutes.
The break logic says basically if they work over 6 hours then deduct -30 minutes.
So 10 hours and 35 minutes = 635 minutes minus the break would be 605 minutes&.now to turn that back into hours you think you would just divide by 60 but when you do the calculation is 10.08&.the correct time should show 10.05

I know this is in the way I am setting up the time.  How can I do this to get the correct time to show.

Thanks,
Jen
0
Comment
Question by:swtjen01
3 Comments
 
LVL 5

Expert Comment

by:mfhorizon
ID: 24116162
You query is correct,
When you are calculating back to hours do like this
Take Non-Fractional part of "605 divided by 60" which is 10 (hours)
Take remainder part of "605 divided by 60" which is .083.
Multily remainder.083 with 60 and get round value 5 (minutes.)

it's just mathematical.
0
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 500 total points
ID: 24116544
5 minutes is .083 hours.  If you want the value to be in hours:minutes, then try this.

Below there is setup with temporary data, but the main part to look at is lines 1 and 3.

Minutes from start to finish:
select datediff(minute, arriveDateTime, departDateTime) as shiftminutes

Add the number of minutes worked (-30 if over 6 hours) to Jan 1, 1900 and then extract the time with format code 108.
convert(varchar(8),dateadd(n, shiftminutes+case when shiftminutes > 360 then -30 else 0 end, 0),108)



 
select convert(varchar(8),dateadd(n, shiftminutes+case when shiftminutes > 360 then -30 else 0 end, 0),108)

from

(select datediff(minute, arriveDateTime, departDateTime) as shiftminutes

 

/*

This is just sample data to query from

*/

from (select getdate() as arrivedatetime, dateadd(hh, 7, getdate()) as departdatetime

union all select '2009-04-10 7:20', '2009-04-10 17:55'

) s

)d

Open in new window

0
 

Author Closing Comment

by:swtjen01
ID: 31568933
It took me a bit to figure it and I had to adapt it for my query out but this was exactly what I needed and it works perfectly.  Thank you so much
0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, just open a new email message. In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

911 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now