Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

Cursor or Temp Files? Expand / Collapse
Author
Message
Posted Thursday, December 12, 2013 12:17 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
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 Status allocate
905055 1 222-63-05 C 12/5/2013
905055 2 222-63-47 C 12/5/2013
905055 3 222-63-80 C 12/5/2013
905055 4 222-23-72 O 12/11/2013
AW00000253 1 222-63-147 C 11/21/2013
AW00000253 2 222-63-247 C 11/21/2013
Post #1522449
Posted Thursday, December 12, 2013 12:41 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 13,182, Visits: 12,672
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 Status allocate
905055 1 222-63-05 C 12/5/2013
905055 2 222-63-47 C 12/5/2013
905055 3 222-63-80 C 12/5/2013
905055 4 222-23-72 O 12/11/2013
AW00000253 1 222-63-147 C 11/21/2013
AW00000253 2 222-63-247 C 11/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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1522456
Posted Thursday, December 12, 2013 12:41 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Today @ 3:17 PM
Points: 592, Visits: 7,009
A cursor certainly wouldn't be needed here; this could be accomplished with a temp table or CTE quite easily. You could do something like so:

CREATE TABLE #Temp(Order# varchar(100))

INSERT INTO #Temp(Order#)
SELECT Order#
FROM (table)
WHERE Status = 'O'

DELETE FROM (table)
FROM (table)
INNER JOIN #Temp
ON (table).Order# = #Temp.Order#

This will find any order numbers that have a status of O at some point, and delete anything having those order numbers from the table. As always, be certain you test this before going ahead with it!

Also, if this is going to be deleting a large number of rows, you'll want to break up the delete into batches to avoid bloating your transaction log.

There may well be a more efficient means of doing this, but it's the most to-the-point method I could think of.

EDIT: Aha! Sean's method certainly works too, and would more than likely be a smoother setup for the deletion




-
Post #1522457
Posted Thursday, December 12, 2013 12:44 PM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, September 19, 2014 7:57 AM
Points: 989, Visits: 1,327
Code solutions depends on a number of things. First, it would be helpful to see the actual table descriptions.

Second, I'd want to know the scope of record volumes, in other words, how many records are you processing?

None the less this can be done using either temporary tables, table variables or one or more CTEs. No reason to be processing row by row regardless how many records you are processing.

Kurt


Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman
Post #1522458
Posted Thursday, December 12, 2013 12:59 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Thank you very much, I will try that right now.
Post #1522464
Posted Thursday, December 12, 2013 1:02 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Thank you very much, I am going to try that right now.
Post #1522465
Posted Thursday, December 12, 2013 1:07 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Hi. Thank you for your response. I really appreciate all of the help here and so quickly too. I will give this a try too.

Post #1522466
Posted Thursday, December 12, 2013 1:53 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Hi. Thank you again for your response. That works very well to get rid of either status C or O, but what they want me to do is to look at all of the same customer order numbers and their status. If the same customer order number has ANY status of 'O', they do not want to see any of those customer order records. They only want to see like customer orders that All have a 'C' status.

Below is the before and after applying your solution. They do not want to see ANY Orders 905055 because it has one with a status of 'O'. but they would want to see order AW00000253 because it doesn't have any status's of 'O". I hope this makes sense and I appreciate all of your help.

conum coline itemnum coitemstat conum coline itemnum coitemstat
905055 1 222-63-05 C 905055 1 222-63-05 C
905055 2 222-63-47 C 905055 2 222-63-47 C
905055 3 222-63-80 C 905055 3 222-63-80 C
905055 4 222-23-72 O AW00000253 1 222-63-147 C
AW00000253 1 222-63-147 C AW00000253 2 222-63-247 C
AW00000253 2 222-63-247 C SF00000389 1 222-23-11 C
AW00000306 2 222-23-31 O SF00000390 2 222-23-11 C
AW00000306 10 222-23-72 O SF00000445 1 222-23-11 C
AW00000306 12 222-23-47 O SF00002994 3 222-63-81 C
SF00000389 1 222-23-11 C SF00004099 1 222-63-13 C
SF00000390 2 222-23-11 C SF00007314 5 222-23-31 C
Post #1522471
Posted Thursday, December 12, 2013 2:21 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 3:56 PM
Points: 13,182, Visits: 12,672
Gillian_Pappas2002 (12/12/2013)
Hi. Thank you again for your response. That works very well to get rid of either status C or O, but what they want me to do is to look at all of the same customer order numbers and their status. If the same customer order number has ANY status of 'O', they do not want to see any of those customer order records. They only want to see like customer orders that All have a 'C' status.

Below is the before and after applying your solution. They do not want to see ANY Orders 905055 because it has one with a status of 'O'. but they would want to see order AW00000253 because it doesn't have any status's of 'O". I hope this makes sense and I appreciate all of your help.

conum coline itemnum coitemstat conum coline itemnum coitemstat
905055 1 222-63-05 C 905055 1 222-63-05 C
905055 2 222-63-47 C 905055 2 222-63-47 C
905055 3 222-63-80 C 905055 3 222-63-80 C
905055 4 222-23-72 O AW00000253 1 222-63-147 C
AW00000253 1 222-63-147 C AW00000253 2 222-63-247 C
AW00000253 2 222-63-247 C SF00000389 1 222-23-11 C
AW00000306 2 222-23-31 O SF00000390 2 222-23-11 C
AW00000306 10 222-23-72 O SF00000445 1 222-23-11 C
AW00000306 12 222-23-47 O SF00002994 3 222-63-81 C
SF00000389 1 222-23-11 C SF00004099 1 222-63-13 C
SF00000390 2 222-23-11 C SF00007314 5 222-23-31 C


PLEASE take a few minutes and read the first link in my signature. The sample data you posted is a mess. It is impossible to tell what is what. You have multiple columns with the same name. Turn this into some actual ddl and sample data (create table and insert statements).

This is really not a difficult query but without tables to work with we are guessing.


_______________________________________________________________

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 Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1522482
Posted Thursday, December 12, 2013 2:36 PM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, May 13, 2014 11:02 AM
Points: 56, Visits: 94
Thank you Sean.

Sorry about the mess.


Hi. Thank you again for your response. That works very well to get rid of either status C or O, but what they want me to do is to look at all of the same customer order numbers and their status. If the same customer order number has ANY status of 'O', they do not want to see any of those customer order records. They only want to see like customer orders that All have a 'C' status.

Below is the before status before applying your solution. They do not want to see ANY Orders 905055 because it has one with a status of 'O'. but they would want to see order AW00000253 because it doesn't have any status's of 'O". I hope this makes sense and I appreciate all of your help.

Order# Line# STATUS
905055 1 C
905055 2 C
905055 3 C
905055 4 O
AW00000253 1 C
AW00000253 2 C
Post #1522487
« Prev Topic | Next Topic »

Add to briefcase 123»»»

Permissions Expand / Collapse