SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Selecting orders based on orderline values


Selecting orders based on orderline values

Author
Message
timothy.merridew
timothy.merridew
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 100
Hi everyone, I'm in the process of creating some tsql to do various lookups and selections on some order tables we have here. The current table I'm working against has the header and details in a single table (due to application design and other requirements). What I'm trying to do is work out the sql required for a selection of orders where the result column is Null for all order lines on that order. I'll give an example to help illustrate the problem.

OrderNo LineNo Product Result
1 1 ABC Null
1 2 XYZ Requires review
2 1 Foobar Null
2 2 Id10t Null

I need a script that can return all orders where all order lines for each OrderNo are Null in the Result column.

I thought I had it with using a CTE and a count distinct, however checking the results I can see orders in my list where there is a row that has Requires review in it.

Any guidance or help would be most appreciated.... Sorry I've been unable to product a table extraction, however the above should show a much simpler dataset.
KenSimmons
KenSimmons
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1638 Visits: 2614
How about something like this?

Declare @Table Table (OrderNo int, [LineNo] int, Product varchar(50), Result varchar(50))

Insert Into @Table
Select 1,1, 'ABC', NULL UNION ALL
Select 1,2, 'XYZ', 'Requires Review' UNION ALL
Select 2,1, 'FooBar', NULL UNION ALL
Select 2,2, 'ID10t', NULL


Select * from @Table
where OrderNo Not IN
(Select OrderNo From @Table Where Result IS NOT NULL)



Ken Simmons
http://twitter.com/KenSimmons
Lynn Pettis
Lynn Pettis
SSC-Forever
SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)SSC-Forever (40K reputation)

Group: General Forum Members
Points: 40474 Visits: 38567
Here is another method:


create table #Orders (
OrderNum int,
LineNum int,
Product varchar(10),
Result varchar(25)
)
;
insert into #Orders
select 1,1,'ABC',null union all
select 1,2,'XYZ','Requires Review' union all
select 2,1,'Foobar',null union all
select 2,2,'Id10t',null
;

select * from #Orders;

with OrderResults(
OrderNum
) as (
select distinct
OrderNum
from
#Orders
where
Result is not null
)
select
o.OrderNum,
o.LineNum,
o.Product,
o.Result
from
#Orders o
left outer join OrderResults ors
on (o.OrderNum = ors.OrderNum)
where
ors.OrderNum is null;

drop table #Orders;



Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
timothy.merridew
timothy.merridew
SSC Journeyman
SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)SSC Journeyman (89 reputation)

Group: General Forum Members
Points: 89 Visits: 100
Hi Ken and Lynn

Thank you both for your responses. I'm kicking myself now over the answer, I can see clearly what you are doing with both of these methods, for the script I need to make I will be using Ken's method at this time, however the CTE method by Lynn may be of use for a future itteration of the application. Again thank you both for your time.

Regards

Timothy Merridew
tfifield
tfifield
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1309 Visits: 2890
There is an easier way to do it. Using the COUNT(ColumnName) function counts only those rows that aren't null.

Here's an example:

create table #Orders (
OrderNum int,
LineNum int,
Product varchar(10),
Result varchar(25)
)
;
insert into #Orders
select 1,1,'ABC',null union all
select 1,2,'XYZ','Requires Review' union all
select 2,1,'Foobar',null union all
select 2,2,'Id10t',null union all
select 3,1, 'ABC', 'Not Null' union all
select 3,2, 'DEF', 'Not Null'
;



-- Get the orders where all Result is null
SELECT OrderNum
FROM #Orders
GROUP BY OrderNum
HAVING COUNT(Result) = 0



-- You can also get all of the orders that don't
-- have any nulls in Result
SELECT OrderNum
FROM #Orders
GROUP BY OrderNum
HAVING COUNT(*) - COUNT(Result) = 0


Todd Fifield
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search