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.

What changes have been made to application to prevent bad data from continuing to contaminate table?

>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.

Perhaps I need new glasses, but I don't see what you report as a problem.
Please post DDL for table & then post DML for sample test data.
It would be helpful if you provided expected/desired results & a detailed explanation how & why the test data gets transformed or organized.

Is the sample data above valid?
If not, what are the "rules" that enumerate what column gets changed to which value and why this is the case?

Thanks anacedent.

I apologize that my previous post was not clear at all. I was under stress and did not revise what I wrote. Also I am new to this forum and did not know how to use the code tag.

There was a bug in the source code and someone dropped date overlap validation.
The rule is that:
* One service must end before new one start.
* Customer can have only one current service. Current service is a service with null end date or end date is in future.

The code has been fixed but I need to cleanse the database.

This is an example of current database.
Problem: Customer A's service 3 ends on 09-JUL-10 but service 4 starts on 01-JUL-10. It means that customer A has two services (3 and 4). Customer B has two open-ended services. Service 2 must have end date prior to the start date of service 3.

It was agreed with business that the end date of overlapping services would he one day before the next service start date. Therefore the fields need to be modified to:

I would really really be appreciated if you could give me an insight with your knowledge.

Regards,
Peter

Realize rows in a table are like balls in a basket.
Rows in a table have NO inherent order.
If you pick 1 ball from the basket, how do you know or decide which ball is the "next" ball?