Solved

SQL Datediff time delimma

Posted on 2009-04-10
3
851 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

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Introduction Earlier I wrote an article about the new lookup functions (http://www.experts-exchange.com/A_3433.html) that ship with SQL Server 2008 R2.  In this article I’m going to show you another new feature of SSRS 2008 R2, this time in the vis…
It is helpful to note: This is a cosmetic update and is not required, but should help your reports look better for your boss.  This issue has manifested itself in SSRS version 3.0 is where I have seen this behavior in.  And this behavior is only see…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 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

10 Experts available now in Live!

Get 1:1 Help Now