CODEWITHSUNDEEP
oracle-database
Ethan Bradberry
Ethan Bradberry

Reputation: 107

Find max number of open tasks

What I am attempting to do is find out the MAX number of tasks I may receive on a day during the next 6 months.

for example 

task 1 runs 1-jan-16 and ends 10-jan-16
Task 2 runs 3-Jan-16 and ends 15-jan-16
task 3 runs 6-Jan-16 and ends 10-Jan-16
Task 4 runs 9-Jan-16 and ends 20-Jan-16

So in this example there are 4 tasks that are open between 1-Jan and 10th Jan so I want the outcome to be 4 in this scenario. The reason being is I'm displaying them in a Gantt chart so they'll be all underneath each other.

All I have to work with so far is:

    select schedule_start_date,am.AC,count(wo) as  from ac_master am
left outer join wo on wo.ac = am.ac and ac_type = '190'
where wo.status = 'OPEN'
group by am.ac,schedule_start_date

This will show the count per day but some of these may overlap.

Is there anyway to do what I am trying to accomplish?

Upvotes: 0

Views: 47

Answers (2)

user5683823
user5683823

Reputation:

Regardless of how you develop a set of rows with start_date and end_date, here is a method to show how the task count changes over time. Each date is the first date when the task count changes from the previous value to the new one.

If you only need max(tasks), that's a simple matter of grouping by whatever is needed. (Or, in Oracle 12, you can order by tasks and use the new fetch first feature.) Notice also the partition by clause - if you need different groups for different categories (for example: for different "departments" etc.) you can use this clause so that the computations are done separately for each group, all in one pass over the data.

with
     intervals ( start_date, end_date ) as (
       select date '2016-01-01', date '2016-01-10' from dual union all
       select date '2016-01-03', date '2016-01-15' from dual union all
       select date '2016-01-06', date '2016-01-10' from dual union all
       select date '2016-01-09', date '2016-01-20' from dual
     ),
     u ( dt, flag ) as (
       select start_date  ,  1 from intervals
       union all
       select end_date + 1, -1 from intervals
     )
select distinct dt, sum(flag) over (partition by null order by dt) as tasks
from   u
order by dt;


DT             TASKS
---------- ---------
2016-01-01         1
2016-01-03         2
2016-01-06         3
2016-01-09         4
2016-01-11         2
2016-01-16         1
2016-01-21         0

Upvotes: 1

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522292

If you just want the count for each scheduled group at a given point in time, then you can just use BETWEEN with the start and end dates:

SELECT schedule_start_date,
       am.AC,
       COUNT(*) AS theCount
FROM ac_master am
LEFT OUTER JOIN wo
    ON wo.ac = am.ac AND
       ac_type = '190'
WHERE wo.status = 'OPEN' AND
      '2016-01-10' BETWEEN schedule_start_date AND schedule_end_date
GROUP BY schedule_start_date,
         am.ac

Upvotes: 1

Related Questions