424,054 Members | 1,044 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,054 IT Pros & Developers. It's quick & easy.

Comparing and updating values in same table but in different rows

P: 1
Hi all,

My company database has been screwed and I need to build a query to fix. It has to be done by query.

There are many rows for services of same customer that their start date and end date are clashing. Customers can have one current service at a time. The system does not allow multiple current values during processing, and it throws lots of errors.

Current data:

Table name: Services
CUSTNUM START_DATE END_DATE SERVICE
------- ---------- --------- ---------
A 18-JUL-08 27-SEP-09 RCBR1
A 28-SEP-09 09-MAR-10 RCBR2
A 10-MAR-10 30-JUN-10 RCBR3
A 01-JUL-10 (Null) RCBR4
B 05-JAN-09 09-JAN-09 RCBR1
B 10-JAN-09 (Null) RCBR2
B 01-JUL-10 (Null) RCBR3
...
...

Problem: Customer A has both service 3 and 4 from 01-JUL-10 to 06-JUL-10. They are invalid in my system as it validates a single current service. Customer B has two services (2 and 3) from 10-JAN-09. These should be changed to:

CUSTNUM START_DATE END_DATE SERVICE
------- ---------- --------- ---------
A 18-JUL-08 27-SEP-09 RCBR1
A 28-SEP-09 09-MAR-10 RCBR2
A 10-MAR-10 30-JUN-10 RCBR3
A 01-JUL-10 (Null) RCBR4
B 05-JAN-09 09-JAN-09 RCBR1
B 10-JAN-09 30-JUN-10 RCBR2
B 01-JUL-10 (Null) RCBR3

Could you please explain me how I achieve this by using SQL only?

Thanks in advance.
Aug 24 '10 #1
Share this Question
Share on Google+
1 Reply


yarbrough40
100+
P: 320
am I missing something? do you just want to update the END_DATE value for that record?

Expand|Select|Wrap|Line Numbers
  1. UPDATE MyTableName SET END_DATE = '30-JUN-10' WHERE CUSTNUM = 'B' AND START_DATE = '10-JAN-09' AND SERVICE = 'RCBR2'
  2.  
  3.  
Aug 28 '10 #2

Post your reply

Sign in to post your reply or Sign up for a free account.