Gillian_Pappas2002 (12/12/2013)
Hi Everyone.I have a collection of records linked by a customer order number. For each like customer order number I want to check the status on them. If all of their status is C, I want to keep that order and combine them into one summary record.
If the status is O on any one of the records with the same Customer Order number then I do not want to save any of the records and want to move to the next set of records with the same customer order.
What is the easiest way to do this?
Below is an example of my data.
Any help would be very, greatly appreciated
Thanks.
Gillian
So for order 905055 I wouldn't want to keep any records because one of the lines has an O status. But for
order AW00000253 I would want to keep those records and combine them into a summary record.
Order# coline itemnum Statusallocate
905055 1222-63-05C12/5/2013
905055 2222-63-47C12/5/2013
905055 3222-63-80C12/5/2013
905055 4222-23-72O12/11/2013
AW00000253 1222-63-147C11/21/2013
AW00000253 2222-63-247C11/21/2013
You don't want to use a cursor OR a temp file. You can use a HAVING clause for this.
select OrderNum
from yourTable
group by OrderNum
having max(Status) = 'C'
If you want/need more specific coding assistance please take a few minutes and read the first link in my signature for best practices when posting questions.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/