[heemalkumar]

Team,
I am trying to drill down exactly how many minutes a User spends on a case. From the below SQL, I am getting the below results:

SQL
SELECT app_cache_view.`APP_NUMBER` AS CaseNumber,
app_cache_view.`DEL_INIT_DATE` AS StartTime,
app_cache_view.`DEL_FINISH_DATE` AS FinishTime,
app_cache_view.`DEL_DURATION` AS Duration,
TIMEDIFF(app_cache_view.`DEL_FINISH_DATE`, app_cache_view.`DEL_INIT_DATE`) AS TimeDifference
FROM app_cache_view
JOIN app_delegation ON app_cache_view.`APP_UID` = app_delegation.`APP_UID`

Results
"CaseNumber" "StartTime" "FinishTime" "Duration" "TimeDifference"
"7999" "2017-04-25 14:30:04" "2017-04-25 14:50:39" "0.014293981481481" "00:20:35"
"8798" "2017-05-09 11:47:18" "2017-05-09 11:47:29" "0.00012731481481481" "00:00:11"

The Field Time Difference Is accurate, however, it tends to take into account weekends and also non-working hours for eg, our working hours is 8AM-5PM, so I don't want after 5PM to be taken into account.

I can't seem to understand how the DEL_DURATION works. I am not able to put it into a format, which would be understandable by the business.
Please assist.

[amosbatto]

Calculating the correct time based upon the selected calendar is very complicated. I have created a PM Function that you can use to calculate task duration. See:
viewtopic.php?f=47&t=730347#p813652