arpoador1
asked on
Need to compute the number of 'business minutes' between two date ranges given a fixed stard and ending HH:MM for the business day.
Terms:
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)
Case 1:
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,
(SELECT COUNT(*)
FROM DUAL
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
FROM myTable
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)
Case 1:
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,
(SELECT COUNT(*)
FROM DUAL
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
FROM myTable
DO you need this in Oracle syntax or are you doing the calculation in a program or reporting tool?
mlmcc
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
(Ya gotta love when someone does your work for you!!)
laziness on my part
easier on my end to just do it than explain it. :)
but now that it's written...
iterate through each day in the period
for each day do one of the following..
if it's a weekend add 0
if the day is the first day, add partial day if needed
if the day is the last day, add partial day if needed
if a weekday in the middle, add entire business day range
oracle date math units are in DAYS, so multiply by 1440 to get minutes.
easier on my end to just do it than explain it. :)
but now that it's written...
iterate through each day in the period
for each day do one of the following..
if it's a weekend add 0
if the day is the first day, add partial day if needed
if the day is the last day, add partial day if needed
if a weekday in the middle, add entire business day range
oracle date math units are in DAYS, so multiply by 1440 to get minutes.
Hey, whatever works!! *BIG GRIN*
ASKER
Well done. Still testing it but looks clear and thorough.
Looks very Oracle, which is what I need.
I was using 24 hours to subtract out weekend days and 16 hours to subtract out hours in a weekday that aren't worked. That is assuming you take the whole range and remove the non-working parts.
I like your suggestion better which is just to walk thru each day and add up the hours (minutes) spent.
I learned a few things from this - aside from just the problem solving part.
My next step is to take this and add in a DAY NOT IN (<holiday>,<holiday>, etc...).
If it get it working I'll post here.
Thanks so much.
Cheers.
Looks very Oracle, which is what I need.
I was using 24 hours to subtract out weekend days and 16 hours to subtract out hours in a weekday that aren't worked. That is assuming you take the whole range and remove the non-working parts.
I like your suggestion better which is just to walk thru each day and add up the hours (minutes) spent.
I learned a few things from this - aside from just the problem solving part.
My next step is to take this and add in a DAY NOT IN (<holiday>,<holiday>, etc...).
If it get it working I'll post here.
Thanks so much.
Cheers.
How is the selected comment the answer?
mlmcc
mlmcc
yes, it looks like you are thanking me for my post 40772615, but you accepted a different one.
I assume that's a misclick?
I assume that's a misclick?
2) Why not just subtract beginning of work day from end of work day, convert to minutes for each day during the date period, and sum?