snoozles
asked on
Oracle View or Function to show non accounted for time
I have three Oracle tables all linked by an id.
Table PD it contains a record with a start time and an end time.
Table TA (1 to many)for each PD record it has many records with start time and end time that fall within the PD time.
Table SD (1 to many)for each PD record it has many records with start time and end time that fall within the PD time.
So it could look like this
PD record from 06:00 - 14:00
TA record 06:00 - 11:00
TA record 13:00 - 14:00
TA record 10:00 - 11:30
TA record 12:00 - 12:30
SD record 12:50 - 13:10
PD 6:00|---------------------
TA 6:00|---------------------
TA 10:00|--------------------
SD 12:50|------|13:10
No what I need is a procedure or a view that can give me the following information
All PD time when there is no correlating records. (*******)
for example
from 11:30 - 12:00 there is no TA and no SD
from 12:30 - 12:50 there is no TA and no SD
So I want to be able to create records of these in order to put into a another table.
If it was possible to do with a view it would be great... but I think it may have to be a procedure I am not sure.
Any help would be hugely appreciated.
Table PD it contains a record with a start time and an end time.
Table TA (1 to many)for each PD record it has many records with start time and end time that fall within the PD time.
Table SD (1 to many)for each PD record it has many records with start time and end time that fall within the PD time.
So it could look like this
PD record from 06:00 - 14:00
TA record 06:00 - 11:00
TA record 13:00 - 14:00
TA record 10:00 - 11:30
TA record 12:00 - 12:30
SD record 12:50 - 13:10
PD 6:00|---------------------
TA 6:00|---------------------
TA 10:00|--------------------
SD 12:50|------|13:10
No what I need is a procedure or a view that can give me the following information
All PD time when there is no correlating records. (*******)
for example
from 11:30 - 12:00 there is no TA and no SD
from 12:30 - 12:50 there is no TA and no SD
So I want to be able to create records of these in order to put into a another table.
If it was possible to do with a view it would be great... but I think it may have to be a procedure I am not sure.
Any help would be hugely appreciated.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Naveen Kumar
can you show me the data types of the tables before i come up with a query.
ASKER
Hi Johnsone
That is excellent, I had to make a modification to include the link between the tables, which on reflection I see I did not include in my spec.
And it works perfectly, I have generated some records and all test so far prove correct.
select pd.prod_id_pk, pd.start + ((d.sec - 1) / 86400)
from pd, (select level sec from dual connect by level < 86401) d
where pd.start + ((d.sec - 1) / 86400) < pd.end and
not exists (select 1
from ta
where pd.start + ((d.sec - 1) / 86400) between ta.start and ta.end
and pd.prod_id_pk = ta.prod_id_pk)
and not exists (select 1
from sd
where pd.start+ ((d.sec - 1) / 86400) between sd.start and sd.end
and sd.prod_id_pk = pd.prod_id_pk)
thank-you for a very simple to follow solution.
Regards
That is excellent, I had to make a modification to include the link between the tables, which on reflection I see I did not include in my spec.
And it works perfectly, I have generated some records and all test so far prove correct.
select pd.prod_id_pk, pd.start + ((d.sec - 1) / 86400)
from pd, (select level sec from dual connect by level < 86401) d
where pd.start + ((d.sec - 1) / 86400) < pd.end and
not exists (select 1
from ta
where pd.start + ((d.sec - 1) / 86400) between ta.start and ta.end
and pd.prod_id_pk = ta.prod_id_pk)
and not exists (select 1
from sd
where pd.start+ ((d.sec - 1) / 86400) between sd.start and sd.end
and sd.prod_id_pk = pd.prod_id_pk)
thank-you for a very simple to follow solution.
Regards
snoozles,
You might want to change "start" to something else because "start" is a reserved word and may cause problems for code formatters.
You might want to change "start" to something else because "start" is a reserved word and may cause problems for code formatters.