Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL Datediff time delimma

Posted on 2009-04-10
3
Medium Priority
?
919 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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 2000 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 Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

How to use Variables  and Custom code in SSRS report and Assembly reference to use compile shared code in SSRS. Its big question for all who are working with SSRS. It is easy to create assembly and refer in SSRS report, still there are some steps…
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…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

721 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