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?
 
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
 
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
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

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

All Courses

From novice to tech pro — start learning today.