Toad World® Forums

Calculate Email Turn Around time

Please help me to make email tat report .

I have two columns email_receive_time (date time stamp)n and email_replied_time (date time stamp) , I wanted to calculate turn around time between the two during office hours only.

Condition is as follows :-

Office timing is 10 am to 7 pm and all those emails received after 7pm would be considered to be received at next day 10 am and those received before 10 am would be considered to be received at same day 10 am .

Also exclude a single holiday in a month : it can be any random day of a month .

You need to use a case statement, something like below (@yourDate would be your received datetime field and you would not need to declare it, just for testing):

Declare @YourDate as DateTime = ‘07/01/2015 20:00:00’

Select @YourDate TestDate, Cast(@YourDate as time) TimePart

, Case

When Cast(@YourDate as time) < Cast(‘10:00’ as time)

Then DateAdd(hh, 10, cast(Cast(@YourDate as Date) as datetime))

When Cast(@YourDate as time) > Cast(‘19:00’ as time)

Then DateAdd(d, 1, DateAdd(hh, 10, cast(Cast(@YourDate as Date) as datetime)))

Else @YourDate

End CalcReceived

You could then use that entire case statement as the first date value in a DateDiff function. If you are using an Oracle database, you don’t have (or need) the DateAdd function. Just use + and the decimal equivalent of 10 hours (10/24 or .4166667) or for next day + 1.4166667.