There's a situation in which i have to:
1) omit the canceled records. If there is a cancelation then the canceled record (flag: canceled = 1) and the relative record(aa_rec_ref = aa_rec ) with no-cancel flag should be left out.
2) omit the 'old' record when an update is done. Get only the record with the 'updated' flag = 1.
To be more specific.
Inserts and updates done in a table and are sent to a public organization where they get a protocol number and these records are locked (no update or delete is permitted).
But, there maybe a possibility in which a cancellation or an update would occur. In order to register these kinds of modifications - to the locked set of recs - new records
should be done referring the specific locked rec, with update or cancel flag.
The objective is not to specify more than 5hr totally per day, per person. Otherwise an error should be returned.
Note: There may be more than 1 non-canceled records in the same day, which they must be fetched.
The 'virual' table with some sample data and the script - i wrote so far- to get the desired result is:
with sample_data(aa_rec, person_id, dates, time_from, time_to, canceled, updated, aa_rec_ref) as
(select 1, 1520, to_date('30/10/2018', 'dd/mm/yyyy'), '12:00', '14:30', 0, 0, null from dual union all
select 2, 1520, to_date('30/10/2018', 'dd/mm/yyyy'), '16:00', '16:30', 0, 0, null from dual union all
select 3, 1520, to_date('30/10/2018', 'dd/mm/yyyy'), '16:00', '16:30', 1, 0, 2 from dual union all
select 4, 1520, to_date('30/10/2018', 'dd/mm/yyyy'), '12:00', '23:30', 0, 1, 1 from dual union all
select 5, 1520, to_date('30/10/2018', 'dd/mm/yyyy'), '08:00', '12:30', 0, 1, null from dual
)
select *
from sample_data
where canceled = 0
and person_id = 1520
and dates = to_date('30/10/2018', 'dd/mm/yyyy')
and aa_rec not in (select aa_rec
from sample_data
where person_id = 1520
and dates = to_date('30/10/2018', 'dd/mm/yyyy')
start with aa_rec_ref is not null
connect by prior aa_rec_ref = aa_rec
and (prior canceled = 1 and (prior updated = 0))
)
Explanation:
aa_rec: a sequence number - primary key.
person_id : the person's id
dates: the specific date
time_from: the start time
time_to: the end time. The difference between end time and start time is computed afterwards so as not to get over 5hrs.
canceled: the canceled flag. By default is 0, when canceled is 1.
updated: the updated flag. By default is 0, when there is an update then its value gets 1.
aa_rec_ref: it has value only when the canceled flag or the updated flag have the value 1. They
refer to the aa_rec of the record which is canceled or is updated respectively.
So, in the above table data:
The record with aa_rec: 3 cancels the existence of record with aa_rec: 2 , because the column aa_rec_ref = 2. So. both of records
with aa_rec: 2 and 3 must be left out.
The record with aa_rec: 4 updates the time_from, time_to column value with aa_rec: 1, because the column aa_rec_ref does have the value=1.
So, the "old" record (with aa_rec=1) must be omitted and the record with aa_rec = 4 should be returned.
To sum up:
The right set of records to get is:
aa_rec: 4, 5 and not 1,5 as the sql stmt i wrote above.
Is there any way to get the desired result...????
Note: I use oraDB 11g v2
To get the total number of hours, per day, per person:
select sum(24*(to_date(dates||' '|| time_to,'dd/mm/yyyy hh24:mi') -
to_date(dates||' '|| time_from,'dd/mm/yyyy hh24:mi')))
..... and the rest of sql script
Thanks
Sim