Clashing of Records

[ponic]

In have a table called PRODUCTS, table structure and records can be found here.

PRODUCT_NO_REGISTRATION_NO is allowed to be used until that is returned back. i.e. There should not be any PRODUCT_NO_REGISTRATION_NO clashing or overlapping with other PRODUCT_NO. We return the products and return date is entered in ACTUAL_RETURN_DATE and if ACTUAL_RETURN_DATE is null we take END_DATE as return date.

If user wants to extend PRODUCTS, they extend the END_DATE by adding another record with PRODUCT_EXTENSION_NO, so a product can be used till the extension ends. By doing so PRODUCT_NO remains the same along with PRODUCT_NO_REGISTRATION_NO, only extension will be in sequence like 0,1,2 . For extension records START_DATE remains the same along with PRODUCT_NO and PRODUCT_NO_REGISTRATION_NO and extension will be in sequence like 0,1,2.

How can I find out whether records are clashing or overlapping except for extension records?

E.g. if you see the records PRODUCT_NO - ORP76 with PRODUCT_EXTENSION_NO - 1 is clashing with PRODUCT_NO- ORP100 with PRODUCT_EXTENSION_NO - 0.

Any help is highly appreciable.

P.S. Database is Oracle 10g on Solaris

Regards

[Tim...]

Hi.

Sorry. I've read your post twice and I don't understand your question. I'm sure it makes sense to you, but coming in cold like me it means nothing.

Break the issue down, showing me examples of what represents clashed data and what is OK. Maybe then I can get a handle on what you are asking.

Cheers

Tim...

[Tim...]

Hi.

If your question were merely about date range clashes, you could use an approach like this to check for them,

Code: Select all

select *
from   products
where  product_no = p_product_no
and    product_no_registration_no = p_product_no_registration_no
and    start_date <= p_end_date
and    end_date >= p_start_date
order by start_date

Cheers

Tim...

[ponic]

First of all for sorry for not explaining properly.

Break the issue down, showing me examples of what represents clashed data and what is OK. Maybe then I can get a handle on what you are asking.

Examples of clashed records

PRODUCT_NO=ORP76 and PRODUCT_EXTENSION_NO=1 is clashing with PRODUCT_NO=ORP100 and PRODUCT_EXTENSION_NO=0 because
for PRODUCT_NO=ORP76 Actual_return_date is 04-JUL-2002 and for PRODUCT_NO=ORP100 with PRODUCT_EXTENSION_NO=0 is allotted
from 01-JUL-2002. So it is overlapping or clashing from 01-JUL-2002 to 04-JUL-2002.
Allotment should have been only after 04-JUL-2002 ,say 05-JUL-2002.

Please let me know if I am not fully able to make you understand what I am looking for.

Thanks Tim

[Tim...]

Hi.

1) You can't write comparisons of data as prose. Display them as SQL*Plus output. Like, these two rows clash:

Code: Select all

col1  col1  col3
----  ----  ----
1..   ...   ...
2..   ...   ...

I should be able to clearly read the formatted text so I can see the comparison. Trying to read sentences that include data is a pain.

2) You have not said if the date range clash I sent is relevant or not.

Cheers

Tim...

[ponic]

Query

Code: Select all

select * from products
where PRODUCT_NO='ORP76' and PRODUCT_NO_REGISTRATION_NO ='MC2090'
and PRODUCT_EXTENSION_NO = 1
union
select * from products
where PRODUCT_NO='ORP100' and PRODUCT_NO_REGISTRATION_NO ='MC2090'
and PRODUCT_EXTENSION_NO = 0

Tim your earlier reply did not suit as it we need pass parameters, I guess p_product_no you mentioned is parameter.

Regards

[Tim...]

Hi.

But you can adapt this method to test the rows in a query...

Code: Select all

select * from products p1
where  exists (select 1
               from   products p2
               where  p1.product_no_registration_no = p2.product_no_registration_no
               and    p1.product_extension_no > p2.product_extension_no
               and    p1.start_date <= p2.end_date
               and    p1.end_date >= p2.start_date)

Cheers

Tim...

[ponic]

Tim

The query is partially correct except for one thing.

I would like to eliminate those records which are basically extensions i.e. for extension records START_DATE remains the same along with PRODUCT_NO and PRODUCT_NO_REGISTRATION_NO and extension will be in sequence like 0,1,2.

In your query PRODUCT_NO ORP53 is basically is an extension.

Code: Select all

select * from products
where PRODUCT_NO='ORP53'
order by PRODUCT_EXTENSION_NO

, Product's end_date is basically extended.

I would like to have only those records which are clashing and it should not be extended records.

Thanks

[Tim...]

Hi.

I'm not sure I understand. From what it sounds like, you just have to limit the query to only deal with rows where the "product_extension_no='0'"

Code: Select all

select * from products p1
where  p1.product_extension_no = '0'
and    exists (select 1
               from   products p2
               where  p2.product_extension_no = '0'
               and    p1.product_no_registration_no = p2.product_no_registration_no
               and    p1.product_extension_no > p2.product_extension_no
               and    p1.start_date <= p2.end_date
               and    p1.end_date >= p2.start_date)

Cheers

Tim...