Help on Update Statement

[faimuj]

Hello,

I needed some help on how to set records that where cancelled. Here is the table (Orders) and the relevant fields:

Orders

• Order_ID (VARCHAR(25))[/*]
• Cancelled (BIT)[/*]

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:

Order_ID
9876543210Y456700
1234567890X123400
1234567890X123401
7654321078Z12345600
7654321078Z12345601
7654321078Z12345602
7654321078Z12345603
2135467826A898779100
2135467826A898779101
2135467826A898779102
2135467826A898779103
2135467826A898779104

The Order_IDs that should be set to Cancelled (Cancelled = '1') are:
1234567890X123400
1234567890X123401
7654321078Z12345600
7654321078Z12345601
7654321078Z12345602
7654321078Z12345603
2135467826A898779100
2135467826A898779101
2135467826A898779102
2135467826A898779103

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:
Order_ID Cancelled
9876543210Y456700 0
1234567890X123400 1
1234567890X123401 1
7654321078Z12345600 1
7654321078Z12345601 1
7654321078Z12345602 1
7654321078Z12345603 1
2135467826A898779100 1
2135467826A898779101 1
2135467826A898779102 1
2135467826A898779103 1
2135467826A898779104 0

Thank you in advance for you assistance.

 

[PHV]

What have you tried so far and where in your code are you stuck ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[faimuj]

Here is what I used to update the Cancelled flag when there orders that were cancelled and not re-billed (count is an even number):

UPDATE Orders
SET o.Cancelled = 1
FROM Orders o INNER JOIN (SELECT a.Order_ID, convert(int,right(a.Order_ID,2)) as Order_End, case when (x.Count % 2) = 0 then 0 else 1 end as Count_Num
FROM Orders a
INNER JOIN (SELECT left(b.Order_ID,len(b.Order_ID)-2) as Trim_Order, count(Trim_Order) as Count
FROM Orders b
GROUP BY Trim_Order
HAVING Count > 1) x ON left(a.Order_ID,len(a.Order_ID)-2) = x.Trim_Order
WHERE Count_Num = 0) y ON rder_ID = y.Order_ID

I was not able to figure out how to update the Cancelled flag when there was a re-bill (count is an odd number).

Thanks.