• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to solve this insert record problem ?

 
Nakata kokuyo
Ranch Hand
Posts: 467
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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 :


if another new record as below


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
author & internet detective
Marshal
Posts: 34071
331
Eclipse IDE Java VI Editor
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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"
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic