Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2010
    Posts
    2

    Unanswered: Comparing and updating values in same table but in different rows

    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.

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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.

    Code:
    CUSTNUM START_DATE END_DATE SERVICE
    ------- ---------- --------- ---------
    C       18-JUL-08  25-SEP-09 RCBR1
    C       28-SEP-09  09-MAR-10 RCBR2
    C       12-MAR-10  30-JUN-10 RCBR3
    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?
    Last edited by anacedent; 08-24-10 at 22:20.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Aug 2010
    Posts
    2

    test

    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.
    Code:
    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  09-JUL-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'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:

    Code:
    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
    I would really really be appreciated if you could give me an insight with your knowledge.

    Regards,
    Peter
    Last edited by swan61; 08-24-10 at 23:26.

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    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?
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •