×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Help on Update Statement

Help on Update Statement

Help on Update Statement

(OP)
Hello,

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

Orders
 [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:

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.
 

RE: Help on Update Statement

(OP)
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 o.Order_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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close