31 Mar 12 17:03
I needed some help on how to set records that where cancelled. Here is the table (Orders) and the relevant fields:
[list][*]Order_ID (VARCHAR(25))[/*][*]Cancelled (BIT)[/*][/list]
Using the Order_ID, what I need to do is to find any orders that were cancelled and its corresponding original order(s). The Order_ID can be of variable length, usually from 15-20 characters. The first left(Order_ID, len(Order_ID)-2) characters of the Order_ID will always be the same for an order. Although, the last two characters determine if the order was cancelled. For example, if there is an order that was not cancelled, it would end with a '00' and there would only be one record. If the order is cancelled, the Order_ID would end with an odd number, like '01', and there would be at least two records: the original record and the cancelled record. For the cancelled orders, there could be cases where there are record count > 2 for same order. For example, an order could be cancelled, re-billed and then cancelled again. So, in this case, there would be the original order (ending with '00'), the cancelled order (ending with '01'), the re-billed order (ending with '02'), and the cancelled order (ending with '03'). In total you have four records for the one order. Theoretically, you can have x number of records for an order.
What I have to do is identify the orders that were cancelled and set the Cancelled flag to '1' where the order was cancelled. If the order was cancelled and then re-billed, I have only have to set the Cancelled = '1' for the records that were cancelled and not re-billed. Sample data is worth a thousand words:
The Order_IDs that should be set to Cancelled (Cancelled = '1') are:
Notice that the first (9876543210Y456700) and last (2135467826A898779104) Order_IDs were not set. The first as it was not cancelled. The last order had five records, all of them are set to cancelled except the Order_ID ending with '04' as it was not cancelled. Here should be the final results:
Thank you in advance for you assistance.