how to solve this insert record problem ?

[Nakata kokuyo]

hi, good day, i having a problem, hope some one can provide me some good idea ,

problem description :
data field suppose insert into DB
- product_code , start_date, end_date

however, when insert into DB, i need to make sure start_date and end_date of record should not fall into date range which have same product_code

for example,

records in DB :

- product_code , start_date, end_date 
 
 
product1 | 15/01/2006 | 20/07/2006
product1 | 02/01/2005 | 29/05/2005

if another new record as below

product1 | 01/05/2006 | 09/09/2006

this record cannot be save because the date range is fall into date range of product1 in DB

what i should do in this scenario, how to detect the date is fall into date range, so the record not to save ? really need your guide on this , thank you very much
[ February 09, 2006: Message edited by: Nakata kokuyo ]

[Jeanne Boyarsky]

Nakata,
You'll want to do this in two steps. Preferably within a transaction.

The first step is to query for the date range:
select count(*)
from table
where product_code = ?
and start_date >= ?
and end_date <= ?

Then if the count is zero, you can insert the record.

[pradeep jaladi]

hi,

I think you need to check 4 cases

1) start_date <= & end_date <=
2) start_date <= & end_date >=
3) start_date >= & end_date <=
4)start_date >= & end_date >=


Regards,
Pradeep Jaladi

"rise above the Rest"