For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!
Hi, @user-yl3t0 Thanks for posting the sample data. Don't forget to post the exact results you want from that sample data, and your Oracle version.
Oracle Version: 11gr2 Express edition SAMPLE_DATA.txt (7.58 KB)Thank you so much for your prompt response. There is a minor change in data. Please look at the attached text file for the same. Look at the highlighted Rows. for Person ID 1111, Since highlighted row #7 almost overlap with the first row, so Net hours shouldn't be 14.03 FOR Mandatory_work='N' but just a couple of minutes where both don't overlap. for Person ID 2222, where min start_time for Mandatory_work='Y' 02-06-2021 18:00:00 & max end_time is 03-06-2021 10:00:00 so date range for Mandatory_work='N' ( 02-06-2021 17:59:00 03-06-2021 07:49:00) almost overlap with that Hence, It should get only 1 minute as Net_hrs & Overlap should be calculated accordingly. Finally, How do I add "Available" as a project ID between rows # 3, 4. Please also recommend some good training around these topics.
Your code woud be SOOOO much easier to read if you formatted it instead of simply posting a hot mess of unformatted, run-on code. Took me less time to run it through a formatter than it took to write this paragraph.
WITH data AS (SELECT 1111 AS person_id, '01-AUG-11' AS event_date, To_date('01-AUG-11 18:00', 'DD-MON-YY Hh24:MI') AS start_time, To_date('02-AUG-11 06:00', 'DD-MON-YY Hh24:MI') AS end_time, 'AAA' project_id, 'Y' AS mandatory_work FROM dual UNION ALL SELECT 1111 AS person_id, '02-AUG-11' AS event_date, To_date('02-AUG-11 03:00', 'DD-MON-YY Hh24:MI') AS start_time, To_date('02-AUG-11 04:00', 'DD-MON-YY Hh24:MI') AS end_time, 'BBB' project_id, 'Y' AS mandatory_work FROM dual UNION ALL SELECT 1111 AS person_id, '02-AUG-11' AS event_date, To_date('02-AUG-11 05:00', 'DD-MON-YY Hh24:MI') AS start_time, To_date('02-AUG-11 07:15', 'DD-MON-YY Hh24:MI') AS end_time, 'CCC' project_id, 'N' AS mandatory_work FROM dual UNION ALL SELECT 1111 AS person_id, '02-AUG-11' AS event_date, To_date('02-AUG-11 06:16', 'DD-MON-YY Hh24:MI') AS start_time, To_date('02-AUG-11 07:00', 'DD-MON-YY Hh24:MI') AS end_time, 'CCC' project_id, 'N' AS mandatory_work FROM dual UNION ALL SELECT 1111 AS person_id, '03-AUG-11' AS event_date, To_date('03-AUG-11 11:00', 'DD-MON-YY Hh24:MI') AS start_time, To_date('03-AUG-11 15:00', 'DD-MON-YY Hh24:MI') AS end_time, 'AAA' project_id, 'Y' AS mandatory_work FROM dual UNION ALL SELECT 2222 AS person_id, '02-AUG-11' AS event_date, To_date('02-AUG-11 18:00', 'DD-MON-YY Hh24:MI') AS start_time, To_date('03-AUG-11 08:00', 'DD-MON-YY Hh24:MI') AS end_time, 'AAA' project_id, 'Y' AS mandatory_work FROM dual UNION ALL SELECT 2222 AS person_id, '03-AUG-11' AS event_date, To_date('03-AUG-11 01:00', 'DD-MON-YY Hh24:MI') AS start_time, To_date('03-AUG-11 10:00', 'DD-MON-YY Hh24:MI') AS end_time, 'BBB' project_id, 'Y' AS mandatory_work FROM dual UNION ALL SELECT 2222 AS person_id, '03-AUG-11' AS event_date, To_date('03-AUG-11 01:00', 'DD-MON-YY Hh24:MI') AS start_time, To_date('03-AUG-11 10:00', 'DD-MON-YY Hh24:MI') AS end_time, 'CCC' project_id, 'Y' AS mandatory_work FROM dual) -- -- end of test data -- SELECT person_id, start_time, end_time, project_id, mandatory_work, ( end_time - start_time ) * 24 hours, ( end_time - start_time ) * 24 - Greatest(( end_time - Greatest( start_time, Nvl( Lag(end_time) over ( PARTITION BY person_id, mandatory_work ORDER BY mandatory_work DESC, start_time ASC, end_time DESC), start_time )) ) * 24, 0) overlap_hrs, Greatest(( end_time - Greatest(start_time, Nvl(Lag(end_time) over ( PARTITION BY person_id, mandatory_work ORDER BY mandatory_work DESC, start_time ASC, end_time DESC), start_time )) ) * 24, 0) Net_hrs FROM data ORDER BY person_id, mandatory_work DESC, start_time, end_time;
Thank you. I didn't know how to post a clean code.. Truly appreciate your help...
Hi, @user-yl3t0 Please look at the attached text file for the same. Post everything right in the this site. Not everyone who wants to help you can or will open attachments.
For formatting: If you select several lines from your post, a little "paragraph" symbol appears to the left of the editing window. If you click on it you will see formatting options for the fragment. If you click the double-quote you will find a few options - one is for quoting text, but another is for marking text as code. That one looks like this: </> If you copy code from an editor, paste it here, and then format it as code (as explained above), this website's code formatter will still destroy your alignment, etc. What I found to work better is this: First, in the editing window here (on the web site), I type two lines of nonsense, like aaa aaa then I format them as code. (Still nonsense, right?) Then I copy the code from my code editor, come to the editing window here, select the "nonsense" that is already formatted as code, and paste my actual code instead. I found that doing so preserves my code exactly as it was in the code editor - the web site formatter doesn't mess with it anymore. It takes a bit of practice and experimentation, but it's not too hard.
</>
Regarding your question (the real one) - I am trying to understand it at the abstract level. So, for each person id, you have several projects, and for each person and project, you have one or more time interval (or intervals) of MANDATORY work. Question 1: Is it guaranteed that the data makes sense - that is, for each person, the MANDATORY work intervals in the table don't overlap? I assume they can immediately succeed each other; so a person may work on one project until 10:00 AM, and then immediately (at 10:00 AM) start work on a different project (or perhaps continue on the same project; that could be shown more efficiently in the data, but it would not necessarily be a contradiction). Is that correct? Then, for each person and project, you also have time intervals marked as "NOT mandatory". What does that mean - a person was supposed to work on something from 3:30 to 4:30 (that's the MANDATORY), but they actually worked from 3:25 to 4:33 (that is the NOT MANDATORY), and you are computing the extra time they worked on it? They started 5 minutes early and ended three minutes late, so the "net" is eight minutes? Are the "not mandatory" intervals for a person also non-overlapping (guaranteed in the data, not part of your current task)? If not, how would you handle overlapping non-mandatory intervals for a given person, either for the same project or for different projects? Finally: you ask about "available" periods. What does that mean? Periods that were not assigned to MANDATORY work? Or what else? And, "available" from what start date-time and until when? What I mean is this: person 1234 must work on project AAAA from 9:00 to 11:00 AM. They are available, then, from 1 January 1900 until 31 December 9999 - except for those two hours on a given date. Very likely, that's not what you mean by "available". So, what are the "global" start and end date-times to determine "available"? These questions seem to have a lot to do with interval arithmetic/algebra, overlapping ranges with priority, etc. Stew Ashton has a few articles about this on his web site, for example: https://stewashton.wordpress.com/2014/03/22/overlapping-ranges-with-priority/
Thank you for your interest in my post. Please find details as follows: Mandatory work doesn't overlap with the other mandatory work. However, It may overlap with Optional work. ( Mandatory work ='N'). I can continue working on my mandatory assigned project work, but I can in parallel work on my own interest some personal interest project at exactly the same time windows when I am working on mandatory work. This optional work can overlap with mandatory work & other optional work as well. (E.g. I am working on some java design documents as mandatory work but in parallel, I could listen to some podcasts on the ORACLE subquery & parallel watch a cooking show on TV with a mute volume.) Please ignore the quality of work here... What does that mean - a person was supposed to work on something from 3:30 to 4:30 (that's the MANDATORY), but they actually worked from 3:25 to 4:33 (that is the NOT MANDATORY), and you are computing the extra time they worked on it? They started 5 minutes early and ended three minutes late, so the "net" is eight minutes? Here there are two scenarios. In simple words allocate hours to mandatory work first, fill gaps with optional work wherever possible. First I want to add “Available” in project id when there is a gap between two consecutive mandatory activities. Secondly, If optional work overlap with “Available” Project ID then allocate time to optional work as per example given allocate time to optional work between 3:30 to 4:30 as one hour since there was no mandatory work going on during that time. (Assuming that other mandatory work was in progress before 3:30 and exactly after 4:30). If optional work partially overlap with available slot & partially with mandatory work then intersection of available slot overlapping with optional slot can be considered for optional work. What does available means? Let’s assume 6 hours work window morning 6:00 to 12:00 & again from 12:00 pm to 6:00 evening and finally 6 to 12:00 mid-night. I am going through Stew Ashton & Alberto Dell’Era's blog to understand how this is being done.. Any help is highly appreciated..
Hi mathguy , I got solution for available hours #3. A Big Thanks to @Solomon Yakobson . reference: https://community.oracle.com/tech/developers/discussion/2252558/fill-gaps-in-dates
SELECT t.person_id, t.event_date,t.MAX_PREV_END_DATE AS START_TIME, T.START_TIME AS END_TIME, 'AVAILABLE' AS PROJECT_ID, t.Mandatory_work from (select t.*, max(end_time) over ( partition by person_id,mandatory_work order by start_time rows between unbounded preceding and 1 preceding ) as max_prev_end_date from t ) t WHERE max_prev_end_date < end_time union all select * from t order by person_id,mandatory_work desc, start_time asc, end_time desc );
Still trying to understand blog referenced for my requirement Stew Ashton & Alberto Dell’Era's blog.