query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

query

Author

Topic

inbs
Aged Yak Warrior

860 Posts

Posted - 2010-11-25 : 06:40:30

i have this table

orderno ststus date
AA1 11 01012010
AA1 13 01022010
AA1 33 01022010
AA1 11 01032010
AA2 11 01012010
AA2 11 01012010
AA2 11 01012010
AA3 11 01022010
A3 22 01022010
AA3 33 01032010

i want to get
AA2

i want to know what is orders that have all rows with status=11

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 06:54:52

something like

select Orderno ,
case
when date33 is not null and max11 > date33 then max11
when date33 > date22 then date22
else mindate
from
(SELECT Orderno, mindate=min(date), date33 = max(case when status = 33 then date end), date22 = max(case when status = 22 then date end), max11 = max(case when status = 11 then date end)
from tbl
group by Orderno
having max(status) <> 11
) t2

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2010-11-25 : 06:56:43

and for the edited question
select Orderno
from tbl
group by Orderno
having max(status) = 11 and min(status) = 11

or
having count(distinct status) = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-11-26 : 10:02:07

quote:
Originally posted by nigelrivett

and for the edited question
select Orderno
from tbl
group by Orderno
having max(status) = 11 and min(status) = 11

or
having count(distinct status) = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Second part should be

select Orderno
from tbl
where status=11
group by Orderno
having count(distinct status) = 1

Madhivanan

Failing to plan is Planning to fail

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-11-27 : 03:30:17

quote:
Originally posted by madhivanan

quote:
Originally posted by nigelrivett

and for the edited question
select Orderno
from tbl
group by Orderno
having max(status) = 11 and min(status) = 11

or
having count(distinct status) = 1

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Second part should be

select Orderno
from tbl
where status=11
group by Orderno
having count(distinct status) = 1

Madhivanan

Failing to plan is Planning to fail

Nope this wont work. This will exclude records with other status for Orderno if exists and will always return count as 1 so that they will get included always.

it should be


select Orderno
from tbl
group by Orderno
having count(distinct status) = 1
and min(status) = 11

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-11-29 : 19:10:23

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

Let's try to clean up the mess:

CREATE TABLE Orders
(order_nbr CHAR(3) NOT NULL,
foo_status INTEGER NOT NULL,
posting_date DATE NOT NULL,
PRIMARY KEY (order_nbr, posting_date));

Unfortunately, you have dirty data; no proper key!

INSERT INTO Orders
VALUES (AA1 11, '2010-01-01'),
('AA1', 13, '2010-01-02'),
('AA1', 33, '2010-01-02'),
('AA1', 11, '2010-01-03'),
('AA2', 11, '2010-01-01'), -- bad data
('AA2', 11, '2010-01-01'), --bad data
('AA2', 11, '2010-01-01'),
('AA3', 11, '2010-01-02'),
('A3 ', 22, '2010-01-02'),
('AA3', 33, '2010-01-03');

>> I want to know what is orders that have all rows with status=11 <<

SELECT order_nbr
FROM Orders
GROUP BY order_nbr
HAVING MIN(foo_status) = MAX(foo_status)
AND MIN(foo_status) = 11;

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources