Finding time between 2 dates considering working hours

Question

Finding time between 2 dates considering working hours

Jankhana

Say Hey Kid

Points: 681
More actions
March 15, 2012 at 4:09 am

#265834

Hi All.
I have a scenario in which i need to calculate the time consumed by the user to do a task.
suppose Start Time is : 2012-03-14 10:00 am
and End Time is : 2012-03-15 10:am
and working hours is 7 am to 2 pm
then result should be : 7 hours and not 24 hrs.
I know i did to use DateDiff with case statement but i am not able to frame by T-SQL statements.
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Please post table definitions, sample data and desired output. Read this to see the best way to post this to get quick responses.

http://www.sqlservercentral.com/articles/Best+Practices/61537/

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Dwain Camps SSC Guru Points: 86948 More actions · Answer 2

C'mon Gila, surely you can be more helpful than that!

DECLARE @fromdate DATETIME

,@todate DATETIME

,@workstart DATETIME

,@workend DATETIME

SELECT @workstart = '2012-03-01 07:00:00', @workend = '2012-03-01 14:00:00'

SELECT @fromdate = '2012-03-14 10:00:00', @todate = '2012-03-15 10:00:00'

SELECT DATEDIFF(hour, @fromdate, @todate) - DATEDIFF(day,@fromdate,@todate)*(24 - DATEDIFF(hour, @workstart, @workend)) As ElapsedWorkHrs

,DATEDIFF(minute, @fromdate, @todate) - 60*DATEDIFF(day,@fromdate,@todate)*(24 - DATEDIFF(hour, @workstart, @workend)) As ElapsedWorkMin

,DATEDIFF(second, @fromdate, @todate) - 60*60*DATEDIFF(day,@fromdate,@todate)*(24 - DATEDIFF(hour, @workstart, @workend)) As ElapsedWorkSec

,DATEDIFF(millisecond, @fromdate, @todate) - 1000*60*60*DATEDIFF(day,@fromdate,@todate)*(24 - DATEDIFF(hour, @workstart, @workend)) As ElapsedWorkMSec

Above suggestion took me about 5 minutes. Perhaps one of the results displayed can be suitably formatted by the client application to report elapsed time used.

My mantra: No loops! No CURSORs! No RBAR! Hoo-uh![/I]
My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St

Jankhana Say Hey Kid Points: 681 More actions · Answer 3

Thanks @dwain, that solved my problem. I was struggling in datediff calculations. Thank You.

The Dixie Flatline SSC Guru Points: 53354 More actions · Answer 4

Don't forget about weekends or holidays. You may want to make reference to a calendar table that defines holidays or weekends, so that you're only including hours for valid workdays.

__________________________________________________

Against stupidity the gods themselves contend in vain. -- Friedrich Schiller
Stop, children, what's that sound? Everybody look what's going down. -- Stephen Stills

Joe Old Hand Points: 312 More actions · Answer 5

The previous examples output does not change if you tweak the workstart, workend “minutes” in other words the output is the same if the workend is 2012-03-01 14:00:00 or 2012-03-01 14:30:00 the ElapsedWorkMin will still be 420 regardless when it should be 450.

Here is a similar example; you can scale this to seconds if you wish.

DECLARE

@StartShift DATETIME,

@EndShift DATETIME,

@StartDate DATETIME,

@EndDate DATETIME,

@ShiftMinutes FLOAT,

@DateMinutes FLOAT

SELECT @StartShift = '2012-03-15 07:00:00.000', @EndShift = '2012-03-15 14:30:00.000', @ShiftMinutes = DATEDIFF(minute, @StartShift, @EndShift)

SELECT @StartDate = '2012-03-14 10:00:00.000', @EndDate = '2012-03-15 10:00:00.000', @DateMinutes = DATEDIFF(minute, @StartDate, @EndDate)

SELECT

@StartDate AS StartDate,

@EndDate AS EndDate,

@DateMinutes/60 AS RangeInHours,

(@DateMinutes/60)/24 AS RangeInDays,

@DateMinutes AS RangeInMinutes,

@StartShift AS StartShift,

@EndShift AS EndShift,

@ShiftMinutes * ((@DateMinutes/60)/24)/60 AS ShiftHours,

@ShiftMinutes * (@DateMinutes/60)/24 AS ShiftMinutes

Joe Stariha | Coughlan Companies - Capstone | https://www.capstonepub.com/ | https://www.capstonepub.com/about-us/our-story

mtassin SSC-Insane Points: 23099 More actions · Answer 6

The Dixie Flatline (3/15/2012)
Don't forget about weekends or holidays. You may want to make reference to a calendar table that defines holidays or weekends, so that you're only including hours for valid workdays.

I agree with Dixie.

Better to use a Calendar table with the holidays, weekends, and work days defined.

Don't also forget partial work days.... which can be a pain.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]