Home » SQL & PL/SQL » SQL & PL/SQL » Resource allocation problem (Oracle 12c- 12.2.0.1.0 )
Resource allocation problem [message #669084] |
Mon, 02 April 2018 12:14 |
|
suneeshvr
Messages: 5 Registered: May 2015
|
Junior Member |
|
|
Hi Experts ,
I do have complex scenario to be solved using PL/SQL.I have n arrays of resource availability. Out of that I need to get best 5 resource allocation.
For example :
a[105]= [0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]
a[111]=[0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]
a[119]=[1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 ]
a[105] represents the resource id 105.Each if this array represents resource availability . 0 - means resource is not available and 1 means resource available. Each node represents half an hour
duration . I need to get 5 best possibilities where resource(s) who can work on 6 hours.
- My first preference will be resources can work continues 6 hours.
- My second preference will be resource minimum number of resources to work 6 hours
- I need to get 5 possibilities to display .
|
|
|
|
|
Re: Resource allocation problem [message #669088 is a reply to message #669087] |
Mon, 02 April 2018 19:43 |
|
suneeshvr
Messages: 5 Registered: May 2015
|
Junior Member |
|
|
Sorry not to explain the problem in details . Let me try to explain . Below is my table and insert queries
create table RESOURCE_ALLOC
(
cal_entry_id number,
resource_id number,
start_date TIMESTAMP WITH LOCAL TIME ZONE,
end_date TIMESTAMP WITH LOCAL TIME ZONE
);
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (2,119,to_timestamp('09-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('09-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (3,119,to_timestamp('10-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('10-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (4,119,to_timestamp('11-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('11-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (5,119,to_timestamp('12-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('12-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (6,119,to_timestamp('13-APR-18 12.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('13-APR-18 12.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (8,111,to_timestamp('09-APR-18 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('09-APR-18 06.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (9,111,to_timestamp('10-APR-18 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('10-APR-18 06.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (10,111,to_timestamp('11-APR-18 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('11-APR-18 06.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (11,111,to_timestamp('12-APR-18 08.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('12-APR-18 06.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (13,105,to_timestamp('09-APR-18 06.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('09-APR-18 02.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (14,105,to_timestamp('10-APR-18 06.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('10-APR-18 02.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (15,105,to_timestamp('11-APR-18 06.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('11-APR-18 02.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
Insert into RESOURCE_ALLOC (CAL_ENTRY_ID,RESOURCE_ID,START_DATE,END_DATE) values (16,105,to_timestamp('12-APR-18 06.00.00.000000000 AM','DD-MON-RR HH.MI.SSXFF AM'),to_timestamp('12-APR-18 02.00.00.000000000 PM','DD-MON-RR HH.MI.SSXFF AM'));
My requirement here is to find the best 5 possible path. For eg. The resource 119 is available from 09-APR-18 12.00.00.000000000 AM to 09-APR-18 12.00.00.000000000 PM . So I need to get 6 hours slot for resource 119.
He can start at 09-APR-18 12.00.00.000000000 AM and end at 09-APR-18 06.00.00.000000000 AM
He can start at 09-APR-18 00.30.00.000000000 AM and end at 09-APR-18 06.30.00.000000000 AM
He can start at 09-APR-18 01.00.00.000000000 AM and end at 09-APR-18 07.00.00.000000000 AM
Note each increment is 30 mints . Likewise I need to search on other rows for the same resource and other resource and get 5 best possible time slots.
- My first preference will be one single resource can work continues 6 hours.
- My second preference will be minimum number of resources to work 6 hours countinously
- I need to get 5 possibilities to display
|
|
|
|
Re: Resource allocation problem [message #669090 is a reply to message #669089] |
Mon, 02 April 2018 21:11 |
|
suneeshvr
Messages: 5 Registered: May 2015
|
Junior Member |
|
|
1) We want to make the performance of this query more efficient .
metrics for best
a) the resource should start as soon as possible
b) Minimum change of resource
c) if possible we should pick minimum lodaded resource
2) Timezone is used for different purpose for this usecase that many not applicable
3) Time increment : Say the resources are available from 00:00AM to 24:00 hours a day. And I need to search the resource availability from 6am. Frist I need to search for resource availability starting from 6am. If I am not finding any I need to start search again from 6.30 am and so on.
Hope this is clear.
|
|
|
Goto Forum:
Current Time: Sat Sep 28 15:37:32 CDT 2024
|