SQL Dwell Calculation excluding time periods

Question

I'm currently trying to recreate a manual report which creates an "Aging" column for and Activity but excludes non-working hours from the calculation. Is this possible to create, and how would I go about it in T-SQL (SSMS 2014 or using and odbc connection to the db2), I have a moderate level of experience but cannot find any information on these sorts of calculations. Example :

Datetime1 = '2015-01-30 21:30:00.000' 
Datetime2 = '2015-02-02 06:30:00.000'
DATEDIFF = 57 hours 

However I would need the calculation to exclude any time after 22:00 or before 06:00 and weekends, so the "Aging" would read as 1 Hour.

I really hope you can help!!

Answer 1

you can use function below to calculate activity in minutes, I assume that first day of week is Monday, Otherwise you should modify lines related to calculation day of week (DatePart)

create function fn_activity_minutes(@Datetime1 DateTime,@Datetime2 DateTime)
Returns BigInt
as
Begin
    Declare 
            @Date1 Date, 
            @Date2 Date,
            @DateIndex Date,
            @minutes int,
            @lastDayMinutes int


    Set @Date1 = Convert(Date,@DateTime1)
    Set @Date2 = Convert(Date,@DateTime2)
    Set @minutes=DateDiff(minute,@DateTime1,DateAdd(Hour,22,convert(DateTime,@Date1)))
    if @minutes<0 
        Set @minutes=0

    Set @DateIndex=DateAdd(day,1,@Date1)
    While @DateIndex<@Date2
    Begin
        if DatePart(dw,@DateIndex) not in (6,7)  -- you can even check holdays here
            set @minutes=@minutes+16*60 -- 16 hours activity per day
        Set @DateIndex=DateAdd(day,1,@DateIndex)
    End
    if DatePart(dw,@DateIndex) not in (6,7)  -- you can even check holdays here
    Begin
        set @lastDayMinutes=DateDiff(minute,DateAdd(Hour,6,convert(DateTime,@Date2)),@DateTime2)
        if @lastDayMinutes>16*60
            set @lastDayMinutes=16*60
        if @Date1<>@Date2   
            set @minutes=@minutes+@lastDayMinutes
        Else
            Set @minutes=@minutes+@lastDayMinutes-16*60
    End
    return @minutes
End