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 DateCompleted Repair

ab1 06/12/06 Y
ab1 05/28/06 Y
ab1 05/18/06 Y
ab105/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