Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 928
  • Last Modified:

SQL Datediff time delimma

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
swtjen01
Asked:
swtjen01
1 Solution
 
mfhorizonCommented:
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
 
BrandonGalderisiCommented:
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
 
swtjen01Author Commented:
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

NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now