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

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

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
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.
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
###### It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.