December 12, 2013 at 12:17 pm
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
December 12, 2013 at 12:41 pm
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/
December 12, 2013 at 12:41 pm
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 🙂
- 😀
December 12, 2013 at 12:44 pm
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
December 12, 2013 at 12:59 pm
Thank you very much, I will try that right now.
December 12, 2013 at 1:02 pm
Thank you very much, I am going to try that right now.
December 12, 2013 at 1:07 pm
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.
December 12, 2013 at 1:53 pm
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 colineitemnumcoitemstatconumcolineitemnumcoitemstat
905055 1222-63-05C9050551222-63-05C
905055 2222-63-47C9050552222-63-47C
905055 3222-63-80C9050553222-63-80C
905055 4222-23-72OAW000002531222-63-147C
AW00000253 1222-63-147CAW000002532222-63-247C
AW00000253 2222-63-247CSF000003891222-23-11C
AW00000306 2222-23-31OSF000003902222-23-11C
AW00000306 10222-23-72OSF000004451222-23-11C
AW00000306 12222-23-47OSF000029943222-63-81C
SF00000389 1222-23-11CSF000040991222-63-13C
SF00000390 2222-23-11CSF000073145222-23-31C
December 12, 2013 at 2:21 pm
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 colineitemnumcoitemstatconumcolineitemnumcoitemstat
905055 1222-63-05C9050551222-63-05C
905055 2222-63-47C9050552222-63-47C
905055 3222-63-80C9050553222-63-80C
905055 4222-23-72OAW000002531222-63-147C
AW00000253 1222-63-147CAW000002532222-63-247C
AW00000253 2222-63-247CSF000003891222-23-11C
AW00000306 2222-23-31OSF000003902222-23-11C
AW00000306 10222-23-72OSF000004451222-23-11C
AW00000306 12222-23-47OSF000029943222-63-81C
SF00000389 1222-23-11CSF000040991222-63-13C
SF00000390 2222-23-11CSF000073145222-23-31C
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 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/
December 12, 2013 at 2:36 pm
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
December 12, 2013 at 2:37 pm
Well with a little tinkering here is what I came up with:
;with C_Orders_CTE (conum, itemnum, coitemstat) as (
select conum
, itemnum
, coitemstat
from @Orders
where coitemstat = 'C'
), O_Orders_CTE (conum, itemnum, coitemstat) as (
select conum
, itemnum
, coitemstat
from @Orders
where coitemstat = 'O'
)
select distinct ord.conum
from @Orders ORD inner join
C_Orders_CTE C_CTE on ord.conum = C_CTE.conum
and ord.itemnum = C_CTE.itemnum left join
O_Orders_CTE O_CTE on C_CTE.conum = O_CTE.conum
where O_CTE.conum is null
Here is the result I get:
conum
AW00000253
SF00000389
SF00000390
Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY
http://www.linkedin.com/in/kurtwzimmerman
December 12, 2013 at 2:40 pm
Hi Sean.
Here is my table.
DECLARE @TempCOGGP TABLE
(conum varchar(10),
coline varchar(10),
itemnum varchar(35),
custnum varchar(10),
custseq int,
qtyorder decimal (10,8),
qtyready decimal (10,8),
qtyship decimal (10,8),
promise date,
ship date,
orderdate date,
coitemstat varchar(3),
allocate date,
pattern varchar(3),
room varchar(1),
Tag varchar(1)
)
December 12, 2013 at 2:46 pm
Thank you very much Kurt. That is awesome and is exactly what they are looking for.
Pardon my ignorance but I have never seen ;with before, and are you using alias's? Not exactly sure how to plug that in correctly into my .sp.
Thanks again for your patience and extreme help.
Gillian
December 12, 2013 at 2:47 pm
Gillian_Pappas2002 (12/12/2013)
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
You really need to actually read the article that has been suggested. If you had read it you would see that just sticking some values in a post is nothing like consumable data. We want and need to see ddl (create table) and data (insert statements). What you should have posted would look like this.
create table #Something
(
OrderNum varchar(20),
LineNum int,
Status char(1)
)
insert #Something
select '905055', 1, 'C' union all
select '905055', 2, 'C' union all
select '905055', 3, 'C' union all
select '905055', 4, 'O' union all
select 'AW00000253', 1, 'C' union all
select 'AW00000253', 2, 'C';
Then we would be able to see and work with the same thing. This makes it really easy for us to help you.
Somebody like myself can then come along and post a query that meets your requirements. Something like this one...
select *
from #Something
where OrderNum in
(
select OrderNum
from #Something
group by OrderNum
having MAX(Status) = 'C'
)
_______________________________________________________________
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/
December 12, 2013 at 2:51 pm
Gillian_Pappas2002 (12/12/2013)
Thank you very much Kurt. That is awesome and is exactly what they are looking for.Pardon my ignorance but I have never seen ;with before, and are you using alias's? Not exactly sure how to plug that in correctly into my .sp.
Thanks again for your patience and extreme help.
Gillian
WITH is the first part of a common table expression (cte). In actuality the ;with is a little odd. Many people got in the habit of starting a line of code like that but the truth is that a cte requires the previous line of code to end with a semicolon. Remember that the semicolon is a line terminator, not a line beginninator (credit to Lynn Pettis for the phrasing). 😀
In essence it is like an inline view. Check them out on BOL here. http://msdn.microsoft.com/en-us/library/ms175972.aspx
_______________________________________________________________
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/
Viewing 15 posts - 1 through 15 (of 27 total)
You must be logged in to reply to this topic. Login to reply