are there any time zone issues with this data (i.e. is 8am-5pm as seen in the dbms also 8am-5pm to all users ?)

additionally: please provide: otherwise: you will get generic answers using generic table/field names

I have a task table that contains datetime values for each task that has been completed.  I have mangaged to create a temp table that contains all of the tasks and datetimes in sequential order for each employee. I need the ability to get the time elapsed between two completion datetime values in the table by calling a sql function.  There are no time zone issues, 8am-5pm is the same for all users.  I need to be able to display the
elapsed time for multiple datetime combinations. I tried using DATEADD(SS,(DATEDIFF(SS,@date1,@date2)),'1900/01/01') but I'm not sure how I would elminate weekends and times that fall outside of the normal work hours.

Ex.
Task Table
task_ID   Completion_Date          
1       '2013-03-10 8:30:18.007'
2       '2013-03-15 10:00:18.007'
3       '2013-03-16 8:30:18.007'
4      '2013-03-18 8:30:18.007'
......

Execute a sql function to calculate elapsed time for all sections
getElapsedTime('2013-03-10 8:30:18.007','2013-03-15 10:00:18.007')
getElapsedTime('2013-03-16 8:30:18.007','2013-03-18 8:30:18.007')

Results
Elapsed time for task 1 to task 2
Days : hours : Minutes
Elapsed time for task 3 to task 4
Days : hours : Minutes

might I observe that you probably want the temp table structure like this:
employeeId, taskId, Commence_date, Completion_Date

tracking over rows through one field is possible but overly complicates your objective.
what is indicated above is that rows 1 & 2 belong to each other, and rows 3 & 4 belong to each other - by "sequential order" - that's not what I would recommend.

Is it possible to change the structure of this temp table?

the following code (from here) gives a basic structure for a "networkdays" calculation - but not (yet) the partial day portion of your question.

CREATE FUNCTION dbo.NetWorkDays (
 @StartDate DATETIME
 , @EndDate DATETIME
 )
RETURNS INT
AS
BEGIN
 DECLARE @result INT
  , @StartDate2 DATETIME
  , @EndDate2 DATETIME
  , @DateSwap1 DATETIME
  , @DateSwap2 DATETIME
  , @ReturnNegative BIT

 SET @DateSwap1 = @StartDate
 SET @Dateswap2 = @EndDate
 SET @ReturnNegative = 0

 IF @EndDate < @StartDate
 BEGIN
  SET @ReturnNegative = 1
  SET @StartDate = @Dateswap2
  SET @EndDate = @DateSwap1
 END

 SET @StartDate2 = dateadd(d, 8 - datepart(dw, @StartDate), @StartDate)
 SET @EndDate2 = dateadd(d, 1 - datepart(dw, @EndDate), @EndDate)
 SET @result = datediff(d, @StartDate2, @EndDate2) * 5 / 7 + datediff(d, @StartDate, @StartDate2) - 1 + datediff(d, @EndDate2, @EndDate) - CASE 
   WHEN datepart(dw, @StartDate) = 1
    THEN 1
   ELSE 0
   END - CASE 
   WHEN datepart(dw, @EndDate) = 7
    THEN 1
   ELSE 0
   END

 IF @ReturnNegative = 1
 BEGIN
  SET @result = @result * - 1
 END

 RETURN @result
END;

Select allOpen in new window

and there's scope for an improved method of weekend detection (using modulus) that could be incorporated.

Sorry, the sequential order in the table does not affect the query.  It's just an identity value.  In some cases, I will need to be able to calculate the time elapsed between tasks that do not occur in a sequence.  For example - If task 21 has not been completed, get the time elapsed between task 20 and task 22.  That's why I decided to create a function to be able to calculate the time elapsed between any two datetime values.
    I'm open to any table structure.  I just need to be able to calculate the elapsed time for two datetime values.  The results of the query will be in a Crystal Report.  I can arrange the results in the correct order on the report.

How would I convert the INT value from the example above into Days : hours : Minutes?

I have prepared an alternative function which adopts some of the previous function (which I didn't write) but works in seconds instead of days. The logic of this new function is to treat the time elements of a start date/time and a finish date/time separately, then any days between are calculated as before (multiplied by standard working seconds).
e.g.

a start at 13:00:00 is evaluated to 17:00:00 = 14400 seconds (4 hours)
a finish at 12:00:00 is evaluated from 08:00:00 = 14400 seconds (4 hours)

e.g:
start day1 13:00:00 = 14400 seconds (4 hours)
finish day2 12:00:00 = 14400 seconds (4 hours)
= 28800 seconds (8 hours)

start dayMon 13:00:00 = 14400 seconds (4 hours) on that Monday
finish dayTue 12:00:00 = 14400 seconds (4 hours) on that Tuesday
= 28800 seconds (8 hours)
+ 5 * 28800 seconds (5 * 8 hours) [Tue/Wed/Thu/Fri/Mon]
= 172800 seconds (48 hours)

HOWEVER.....

08:00:00 to 17:00:00 is actually 9 hours....
SO
a maximum of 28800 seconds (8 hours) per day is imposed, which is OK except that a start of 09:00:00 and finish of 17:00:00 on the same day is also 28800 seconds (8 hours) and no further deduction is made. Basically you MIGHT get an extra hour on the start day and/or an extra hour on the finish day because this 1 hour (probably lunch) isn't taken into account.

You should definitely take the time to look over the test results at this sqlfiddle link. There you will find some few deliberate test cases and some randomly generated data used to evaluate this new function's output. It is also compared to the former networkdays function (which is also compared to calculations done by Excel when generating the random data).

I have opted to return seconds from this new function which can be further calculated to days/hours as needed, like this:
, dbo.NetWorkSecs(start,finish) /(60.0*60.0)  as NetWorkHours

If you don't want seconds returned then the function itself can be modified to return decimal hours - this I leave to you.

Please do evaluate the function at sqlfiddle. Here's the current code

CREATE FUNCTION dbo.NetWorkSecs (
       @p_StartDate as DATETIME
     , @p_EndDate as DATETIME
     )
RETURNS INT
AS
BEGIN
 DECLARE
    @result       as INT  = 0
  , @v_StartDate  as DATE
  , @v_EndDate    as DATE
  , @v_StartDate2 as DATE
  , @v_EndDate2   as DATE
  , @v_StartTime  as TIME
  , @v_EndTime    as TIME
  , @v_ShiftStart as TIME = cast('08:00:00' as time)
  , @v_ShiftEnd   as TIME = cast('17:00:00' as time)
  , @v_ShiftSecs  as INT  = (8*60*60)
  , @v_OnStart    as INT
  , @v_OnEnd      as INT

IF @p_StartDate <> @p_EndDate /* i.e. no point doing more if they are equal */
  BEGIN
    IF @p_EndDate < @p_StartDate
         /* finish before start, so flip over the given dates for "sequence of event" consistency */
         BEGIN
          SET @v_StartTime = cast(@p_EndDate as TIME)
          SET @v_EndTime   = cast(@p_StartDate as TIME)
          SET @v_StartDate = dateadd(day,1,cast(@p_EndDate as date))   /* next day, because start time accounted for seperately */
          SET @v_EndDate   = dateadd(day,0,cast(@p_StartDate as date)) /* start of last day because finish time accounted for seperately */
         END
    ELSE
         BEGIN
          SET @v_StartTime = cast(@p_StartDate as TIME)
          SET @v_EndTime   = cast(@p_EndDate as TIME)
          SET @v_StartDate = dateadd(day,1,cast(@p_StartDate as date)) /* next day, because start time accounted for seperately */
          SET @v_EndDate   = dateadd(day,0,cast(@p_EndDate as date))   /* start of last day because finish time accounted for seperately */
         END
         
        IF @v_StartDate = @v_EndDate /* i.e. after taking time into accout there are no whole days between start and finish times */
          BEGIN
             SET @v_OnStart = datediff(second,@v_StartTime ,@v_ShiftEnd)       /* seconds on start date within shift */
             SET @v_OnEnd   = datediff(second,@v_ShiftStart,@v_EndTime)        /* seconds on end date within shift */
             SET @result =   CASE
                                when @v_OnStart > @v_ShiftSecs then @v_ShiftSecs when @v_OnStart > 0 then @v_OnStart else 0
                             END
                           + CASE
                                when @v_OnEnd > @v_ShiftSecs then @v_ShiftSecs when @v_OnEnd > 0 then @v_OnEnd else 0
                             END
             END
        ELSE
          BEGIN
             SET @v_OnStart = datediff(second,@v_StartTime ,@v_ShiftEnd)       /* seconds on start date within shift */
             SET @v_OnEnd   = datediff(second,@v_ShiftStart,@v_EndTime)        /* seconds on end date within shift */
             /* the following logic from source 'networkdays' function, results are consistent with Excel networkdays */
             SET @v_StartDate2 = dateadd(d, 8 - datepart(dw, @v_StartDate), @v_StartDate)
             SET @v_EndDate2 = dateadd(d, 1 - datepart(dw, @v_EndDate), @v_EndDate)
             SET @result = (
                            (  datediff(d, @v_StartDate2, @v_EndDate2) * 5 / 7 
                             + datediff(d, @v_StartDate, @v_StartDate2) - 1 
                             + datediff(d, @v_EndDate2, @v_EndDate) 
                             - CASE 
                                   WHEN datepart(dw, @v_StartDate) = 1
                                    THEN 1
                                   ELSE 0
                                   END 
                             - CASE 
                                   WHEN datepart(dw, @v_EndDate) = 7
                                    THEN 1
                                   ELSE 0
                                   END)
                           * @v_ShiftSecs)
                           /* above we multiply by shift seconds for each intervening work day, and below, add seconds of first and last days) */
                           + CASE
                                when @v_OnStart > @v_ShiftSecs then @v_ShiftSecs when @v_OnStart > 0 then @v_OnStart else 0
                             END
                           + CASE
                                when @v_OnEnd > @v_ShiftSecs then @v_ShiftSecs when @v_OnEnd > 0 then @v_OnEnd else 0
                             END
           END
   END
 RETURN @result
END
;

Select allOpen in new window

Oh, and also note, if the provided dates are 'backward' (finish is before start) the new function (NetWorkSecs) simply flips these over. This means the new function does not return a negative number of seconds. The logic to this is that we are evaluating start times and finish times within a day (to the second), so it is important to begin at the start and move forward over time until the end. i.e. there is only one logical sequence here. IF the business logic required a deduction of hours for some reason, then the functions output can be multiplied by -1

Also, there is no accommodation for holidays (at all), just Sat/Sun. (& Here I have NOT attempted revise the previous logic of NetWorkDays (which I didn't write)). Ideally this weekend logic would be independent of dbms settings and even better would be the ability to nominate which days of the week are excluded - but neither has been attempted.

This new function can be easily tweaked for different day shifts such as 09:00 to 18:00 simply by changing the variable values.  BUT it's worth mentioning that this function isn't suited to shifts that span a date (e.g. a 'night shift' starting 21:00 and finishing 06:00 the next day), this would require additional logic.

[edit, aded excel file for reference, data is random!]
Q-28064978-networksecs-randomdat.xlsx

NetWorkDays function from:
http://www.sqlservercentral.com/Forums/Topic14491-23-1.aspx#bm1362656

by: davidcriley      (citing  Patrick Jasinski as originator)

Ok, I will test the function and see if the solutions works for me.  Also, how do I convert seconds into days, hours, and minutes?
Thanks

?!!? (surprise)
the algorithm from seconds to higher units is pretty simple

minutes = seconds/60
hours = seconds/60/60
days = seconds/60/60/24

a scalar function can only return one value, plus you will want to avoid calling the function multiple times. so 'nest' your query, e.g.

select
  start
, finish
, networksecs/60 as networkminutes
,etc.
from (
           select
             start
           , finish
           , dbo.NetWorkSecs(start,finish) as networksecs
          from tasks
        ) as derived

The function could probably be revised to return a "time" type...
then you could use:
, datepart(day,networktime) as days
, datepart(hour,networktime) as hours
, datepart(minute,networktime) as minutes

there's a bucket load of voluntary work for these points

actually returning a time isn't possible, it would have to be a datetime and that's going to be complex for you to interpret, because the number of days would be measured from 1900-01-01

it will be much simpler to return a numeric, it could be minutes instead of seconds
nb: you can aggregate a numeric so calculating totals in much easier this way.

does it absolutely have to be displayed as days/hours/minutes?

why not just decimals hours? (seconds/3600)
e.g 101.55 hours

OK, forgetting the last couple of comments, if the function stays as is (returns seconds), the thing to remember is a "day" is actually represented by 8*60*60 = 28800 seconds, so the following query will report hours, days, minutes using that function:

select
  start
, finish
, (networksecs/28800) as days
, ((networksecs - ((networksecs/28800)*28800))/3600) as hours
, ((networksecs - (((networksecs/28800)*28800) + ((networksecs - ((networksecs/28800)*28800))/3600)*3600)) /60) as minutes
from (
           select
             start
           , finish
           , dbo.NetWorkSecs(start,finish) as networksecs
          from tasks
        ) as derived
;

Select allOpen in new window

see it at:
http://sqlfiddle.com/#!3/42277/3 (look for the second set of results)

all of this would be marginally simpler if the function returned minutes

First, thanks for all of the help, I'm really learning a lot.  The function does not have to return seconds, It can return minutes.  I just need the final output to display days, minutes, and hours.  If the function returns minutes, would i still need a nested query?

a scalar function can only return one value. No exceptions.

To display 3 values from the function would require either:
a. calling the function 3 times, or
b. nesting so the single returned value can be reused

b. is better for performance

so, it will not matter if the function returns minutes, seconds or datetime you would still need to interpret the result for display into 3 seperate parts.

Sorry, I was wondering if there was a way to modify the function to return a varchar instead of an Int value with all three values.  Something like the following:

Select @ReturnValue = CONVERT(VARCHAR(3),(networksecs/28800)) + 'Days '
+ CONVERT(VARCHAR(3),((networksecs - ((networksecs/28800)*28800))/3600))+ 'Hours '
...... 'Minutes'

Would something like that work?

you could not aggregate or sort the output if a varchar
I did consider it but would not recommend it

you are free to try it of course, in theory yes it can be done - just not sure its wise

Ok, I would not need to aggregate and the I would order the task by the task ID on the report.  Why would the report be simpler if I used minutes?

the function is only slightly simpler to use in the query if it returns minutes

IF you relented on display by days/hours/minutes, and simply presented decimal hours that would be simpler....

otherwise I don't understand the question

I am not a development shop.... the function COULD return varchar in days/hours/minutes and I have shown you how that is possible also

what next?

I haven't been working with SQL that long and just needed more clarification on why one method was simpler than the other.  When I convert the seconds that are being returned from the code above, I'm still not receiving the expected results.

For example.  If I calculate the elapsed time from 2012-06-28 10:37:14.997 to 2012-07-11 08:31:02.813, I receive 12Days 21Hours 53Minutes.  However, If I use the code above(excluding weekends, including 8-5 work hours), I receive 284040 for the totalSeconds.  When I convert the total seconds to day, hours, minutes, I receive 3 days, 78 hours, and 4734 minutes.   The days are way off even with weekends excluded.  Is there a way to get the amount of days, hours, and minutes from the total seconds and have it display like 12Days 21Hours 53Minutes?

select @totalSeconds/60 as minutes  -4734
select @totalseconds/60/60 as hours  -78
select @totalSeconds/60/60/24 as days   --3

I corrected myself here you are using "standard" formula for seconds to higher units - but the function is using "working days" so the number of seconds in a "working day" is 1/3 of the seconds in 24 hours.

The correct calculations are these:

declare @networksecs as int = 284040

select
  (@networksecs/28800) as days
, ((@networksecs - ((@networksecs/28800)*28800))/3600) as hours
, ((@networksecs - (((@networksecs/28800)*28800) + ((@networksecs - ((@networksecs/28800)*28800))/3600)*3600)) /60) as minutes

DAYS HOURS MINUTES
9    6     54

Select allOpen in new window

You sir are amazing!!! I wish I could give you a ton of points for this.  Thanks for helping me through this.  I've learned a lot in the process.

Excellent solution.

I am truly pleased to hear you have a solution, it took a while but think it was worth it in the end.

Cheers.
Paul

Function seem to fail in first hour after end of working period.
Both for start and end date.

declare @dateFrom as datetime
declare @dateUntil as datetime

set @dateFrom = {ts '2016-09-08 10:00:00'}
set @dateUntil = {ts '2016-09-08 17:10:00'}

select cast(dbo.GetWorkingStr(@dateFrom,@dateUntil,9,17,'min') as decimal) / 60

results in 7.166666 (should be 7.000000)

set @dateFrom = {ts '2016-09-08 10:00:00'}
set @dateUntil = {ts '2016-09-08 18:10:00'}

select cast(dbo.GetWorkingStr(@dateFrom,@dateUntil,9,17,'min') as decimal) / 60

results in 7.000000 (is correct)