Results 1 to 5 of 5
  1. #1
    Join Date
    Jun 2006
    Posts
    3

    Unanswered: Update SQL column with comparision between last and current records

    My question is concerned with the three columns below (customerID, RepairDate, CompletedRepair (Yes or No). The column name "CompletedRepair " is blank initially. I need to update the CompletedRepair column with this logic below:

    - A customer comes to our store to fix their car, if we fix their problem on the first time and they don’t return later for this same issue, then the
    • CompletedRepair column = Y

    - If a customer needs to come back to our store to re-fix the same issue within 7 days windows based on the RepairDate on the previous transaction then
    • On the last return transaction: CompletedRepair = Y (example: RepairDate =6/12/2006)
    • On all previous transactions: CompletedRepair = N (example: RepairDate =6/8/2006, 6/9/2006, 6/10/2006)

    - If a customer needs to come back to our store to re-fix the same issue but out of the 7 days windows based on the RepairDate then
    • On the last return transaction: CompletedRepair = Y (RepairDate =6/12/2006)
    • On the previous transaction: CompletedRepair = Y (RepairDate =6/1/2006)

    Every time customer comes to for car repair shop for a new issue or an old issue, we create a new repair transaction in our SQL db. The update on the "CompletedRepair " column will be run every day. Today's records will be run against with last 7 days records (based on Repair Date) to check when customer has been really fixed: the last fix counted Y, the previous fix counted as N but comparison in only 7 days. In other words, a repair today is considered as a completed repair when comparing with last 7 day repairs but it might become not a completed repair if this same customer would come back within next 7 days for the same issue.

    The CompletedRepair column is dynamic column and is updated daily by using the logic above.

    Below is the expected outcome after we update the Completed Repair column:

    CustomerID Repair Date Completed Repair

    ab1 06/12/06 Y
    ab1 05/28/06 Y
    ab1 05/18/06 Y
    ab1 05/15/06 N
    ab1 05/12/06 N

    Initially 5/12/06 had Y, when 5/15/06 transaction came, it took the Y and made the 5/12/06 become N. The 5/18/06 transaction did the same to 5/15/06 transaction, made itself Y and converted 5/15/06 into N. The 5/28/06 is Y because comparing with 5/18/06, it is out of 7 days window. The 6/12/2006 is Y because comparing with 5/28/06, it is out of 7 days window.

    ab2 06/02/06 Y
    ab2 05/28/06 N
    ab2 04/19/06 Y
    ab2 04/14/06 N

    The 4/14/06 transaction initially was Y, it became N when new transaction on 4/19/06 came. Same thing with transactions on 5/28/06 and 6/2/06

    ab3 05/11/06 Y
    ab3 03/29/06 Y
    ab3 03/23/06 N
    ab3 03/12/06 Y

    The 3/23/06 was Y, when new transaction on 3/29/06 came, it became N and the new transaction is Y. The 5/11/06 is Y because comparing back to 3/29/06, they are out of 7 days window.

    ab4 05/11/06 Y

    This ab4 customer came to fix her car only one time and don't come back. We supposed the fix was sucessfully and so we mark the CompletedRepair as Y.


    I think that I would need to use SQL cursor or case statement for this but I really don't know how to start. Please advice and help me out. Any ideas and suggestion are really appreciated! If you need more information, please let me know!

    Thank you!

    Tracy

  2. #2
    Join Date
    Jun 2006
    Posts
    3

    Update SQL column with comparision between last and current records

    My question is concerned with the three columns below (customerID, RepairDate, CompletedRepair (Yes or No). The column name "CompletedRepair " is blank initially. I need to update the CompletedRepair column with this logic below:

    - A customer comes to our store to fix their car, if we fix their problem on the first time and they don’t return later for this same issue, then the
    • CompletedRepair column = Y

    - If a customer needs to come back to our store to re-fix the same issue within 7 days windows based on the RepairDate on the previous transaction then
    • On the last return transaction: CompletedRepair = Y (example: RepairDate =6/12/2006)
    • On all previous transactions: CompletedRepair = N (example: RepairDate =6/8/2006, 6/9/2006, 6/10/2006)

    - If a customer needs to come back to our store to re-fix the same issue but out of the 7 days windows based on the RepairDate then
    • On the last return transaction: CompletedRepair = Y (RepairDate =6/12/2006)
    • On the previous transaction: CompletedRepair = Y (RepairDate =6/1/2006)

    Every time customer comes to for car repair shop for a new issue or an old issue, we create a new repair transaction in our SQL db. The update on the "CompletedRepair " column will be run every day. Today's records will be run against with last 7 days records (based on Repair Date) to check when customer has been really fixed: the last fix counted Y, the previous fix counted as N but comparison in only 7 days. In other words, a repair today is considered as a completed repair when comparing with last 7 day repairs but it might become not a completed repair if this same customer would come back within next 7 days for the same issue.

    The CompletedRepair column is dynamic column and is updated daily by using the logic above.

    Below is the expected outcome after we update the Completed Repair column:

    CustomerID Repair Date Completed Repair

    ab1 06/12/06 Y
    ab1 05/28/06 Y
    ab1 05/18/06 Y
    ab1 05/15/06 N
    ab1 05/12/06 N

    Initially 5/12/06 had Y, when 5/15/06 transaction came, it took the Y and made the 5/12/06 become N. The 5/18/06 transaction did the same to 5/15/06 transaction, made itself Y and converted 5/15/06 into N. The 5/28/06 is Y because comparing with 5/18/06, it is out of 7 days window. The 6/12/2006 is Y because comparing with 5/28/06, it is out of 7 days window.

    ab2 06/02/06 Y
    ab2 05/28/06 N
    ab2 04/19/06 Y
    ab2 04/14/06 N

    The 4/14/06 transaction initially was Y, it became N when new transaction on 4/19/06 came. Same thing with transactions on 5/28/06 and 6/2/06

    ab3 05/11/06 Y
    ab3 03/29/06 Y
    ab3 03/23/06 N
    ab3 03/12/06 Y

    The 3/23/06 was Y, when new transaction on 3/29/06 came, it became N and the new transaction is Y. The 5/11/06 is Y because comparing back to 3/29/06, they are out of 7 days window.

    ab4 05/11/06 Y

    This ab4 customer came to fix her car only one time and don't come back. We supposed the fix was sucessfully and so we mark the CompletedRepair as Y.


    I think that I would need to use SQL cursor or case statement for this but I really don't know how to start. Please advice and help me out. Any ideas and suggestion are really appreciated! If you need more information, please let me know!

    Thank you!

    Tracy

  3. #3
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    All you need is an insert/update trigger on the table that sets the COMPLETED value of all identical repairs for the customer in the last seven days to "N".
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    what was the question?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Jun 2006
    Posts
    3

    Update SQL column with comparision between last and current records

    My question was that how to write an SQL script to update that column. I have written a script with CASE statement but it still doesn't work right. Still need some modification. I might look for a SQL expert who can help with the script and I will pay if the script works. Thanks for asking!


    Tracy

Posting Permissions

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