# 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

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
###### Who is Participating?

Commented:
I wasn't sure how you were storing the begin/end of work day, so I created an inline view of hours and minutes.

SELECT fromdate,
thrudate,
(    SELECT ROUND(
NVL(
SUM(
CASE
WHEN TO_CHAR(fromdate + LEVEL - 1, 'DY') IN ('SAT', 'SUN')
THEN
0
WHEN LEVEL = 1
THEN
CASE
WHEN fromdate >=
TRUNC(fromdate) + ewdhh / 24 + ewdmm / 1440
THEN
0
ELSE
TRUNC(fromdate)
+ ewdhh / 24
+ ewdmm / 1440
- GREATEST(
fromdate,
(TRUNC(fromdate) + bwdhh / 24 + bwdmm / 1440)
)
END
WHEN TRUNC(fromdate) + LEVEL - 1 = TRUNC(thrudate)
THEN
CASE
WHEN thrudate <
TRUNC(thrudate) + bwdhh / 24 + bwdmm / 1440
THEN
0
ELSE
TRUNC(thrudate)
+ bwdhh / 24
+ bwdmm / 1440
- LEAST(
thrudate,
TRUNC(thrudate) + ewdhh / 24 + ewdmm / 1440
)
END
ELSE
(TRUNC(thrudate) + ewdhh / 24 + ewdmm / 1440)
- (TRUNC(thrudate) + bwdhh / 24 + bwdmm / 1440)
END
),
0
)
* 1440,
2
)
FROM DUAL
CONNECT BY LEVEL <= TRUNC(thrudate) - TRUNC(fromdate) + 1)
cnt
FROM yourtable, (SELECT 7 bwdhh, 30 bwdmm, 17 ewdhh, 0 ewdmm -- 7:30am to 5pm business hours
FROM DUAL)
0

Commented:
1) Why use 1440 minutes, which is 24 hours?

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?
0

Commented:
DO you need this in Oracle syntax or are you doing the calculation in a program or reporting tool?

mlmcc
0

Commented:
(Ya gotta love when someone does your work for you!!)
0

Commented:
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

oracle date math units are in DAYS, so multiply by 1440 to get minutes.
0

Commented:
Hey, whatever works!!  *BIG GRIN*
0

Author Commented:
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.
0

Commented:
How is the selected comment the answer?

mlmcc
0

Commented:
yes, it looks like you are thanking me for my post 40772615, but you accepted a different one.

I assume that's a misclick?
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.