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

Selecting orders based on orderline values Expand / Collapse
Author
Message
Posted Monday, April 27, 2009 10:05 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 6:57 AM
Points: 51, 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.

Post #705079
Posted Monday, April 27, 2009 11:05 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, May 9, 2013 8:07 AM
Points: 1,220, Visits: 2,614
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
Post #705141
Posted Monday, April 27, 2009 11:50 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 3:27 PM
Points: 20,734, Visits: 32,505
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;




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)
Post #705170
Posted Tuesday, April 28, 2009 1:57 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Wednesday, June 12, 2013 6:57 AM
Points: 51, 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
Post #705562
Posted Thursday, April 30, 2009 4:13 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: Sunday, September 28, 2014 1:41 PM
Points: 959, Visits: 2,888
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
Post #708149
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse