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

Bob LearnedCommented:
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
mlmccCommented:
DO you need this in Oracle syntax or are you doing the calculation in a program or reporting tool?

mlmcc
0
sdstuberCommented:
I wasn't sure how you were storing the begin/end of work day, so I created an inline view of hours and minutes.
Adjust as needed.

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.

Start your 7-day free trial
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Bob LearnedCommented:
(Ya gotta love when someone does your work for you!!)
0
sdstuberCommented:
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
Bob LearnedCommented:
Hey, whatever works!!  *BIG GRIN*
0
arpoador1Author 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
mlmccCommented:
How is the selected comment the answer?

mlmcc
0
sdstuberCommented:
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.