BWD: Beginning of Work Day
EWD: End of Work Day
BT: Begin Task (begin date)
ET: End Task (end date)
I have a work ticket table in Oracle. Each ticket has either a begin datetime *and* an end datetime or just a begin datetime and no end datetime, in which case it's still in progress so the computed elapsed time should be begin datetime to <current date time>.
The results should not count hours on weekends.
IE: where DATEPART(weekday, startdate) NOT IN (1, 7) (1 is sunday...7 is saturday)
If begin and end is on the same date, it's: select (ET - BT) * 1440 from TABLE_X;
Case 2: A is time on begin date. B is time on end date. There may be days in the the middle: ((workday end - work day begin) -2)* work days + A + B. (excluding weekends)
A. select (TO_DATE(EWD DATE() EWD:HH EWD:MM) – BT * 1440
B. select (ET – TO_DATE(ET date: BWD HH; BWD MM) * 1440
Having trouble pulling all of this together.
Have seen this, too:
SELECT FromDate, ThruDate,
WHERE TO_CHAR(FromDate + LEVEL - 1, 'DY') NOT IN ('SAT', 'SUN')
CONNECT BY LEVEL <= TRUNC(ThruDate,’DD’) - TRUNC(FromDate,’DD) + 1
) -2 * 1440 AS Weekday_Count