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.