How to solve for orders where the status on every item is greater than status of 3

Question

How to solve for orders where the status on every item is greater than status of 3

kat35601

SSC Eights!

Points: 825
More actions
December 14, 2015 at 1:13 pm

#314242

How solve for orders where the status on every item is greater than status of 3 or don't show that order.
so a good order would have 5 items all with a status greater then 3
a bad order would be 4 item with a status of 4 and one item with a status of 2 so don't show that order at all.
Select 0rder_number, serial_number, status
from orders
where order_closed !=-1 and ?????????
order by Order_number

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

drew.allen SSC Guru Points: 76985 More actions · Answer 1

You don't say whether the items are in a separate table. (They probably should be, but that doesn't mean that they are.) It would help if you followed Forum Etiquette[/url] for posting questions.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

kat35601 SSC Eights! Points: 825 More actions · Answer 2

I am sorry I left out the item

Select 0rder_number,item_number, serial_number, status

from orders

where order_closed !=-1 and ?????????

order by Order_number

everything is in the same table

pietlinden SSC Guru Points: 63503 More actions · Answer 3

Got sample data? If you want a tested answer, please read this article: How to post data/code to get the best help[/url].

Maybe something like this?

SELECT *

FROM OrderList ol

WHERE NOT EXISTS

(SELECT 1

FROM OrderList

WHERE OrderNo = ol.OrderNo

AND OrderStatus<3);

kat35601 SSC Eights! Points: 825 More actions · Answer 4

I could offer this

order,item,serial,status

'1234','s1','112341','6'

'1234','s2','1512431','6'

'1234','s3','1512331,'6'

'1234','s3','1512371','6'

'1234','s4','1612331','6'

'1234','s4','1622331','6'

'1234','s5','1612631','6'

'1234','s6','1612334','6'

'1234','s7','1512131','6'

'1234','s8','1511331','6'

'1234','s9','1512441','6'

'1278','s1','112342','4'

'1278','s2','1512531','4'

'1278','s2','1512931','4'

'1278','s3','1592331','2'

'1278','s3','1518331','2'

'1278','s5','1517331','4'

'1297','s7','1511631','3'

'1297','s1','112442','3'

'1297','s2','1512111','3'

drew.allen SSC Guru Points: 76985 More actions · Answer 5

Unfortunately that doesn't fit the format that we both posted a link to. In fact, I'm beginning to think that this is homework, because you have used the number 0 (zero) and the letter O (oh) interchangeably, which I think is more likely to occur when copying from a book than if this were from a live database, and because you haven't formatted the data as requested, indicating that you may not have access to live data.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

pietlinden SSC Guru Points: 63503 More actions · Answer 6

I'll see you and raise...

CREATE TABLE OrderList (OrderNo INT, SerialNo INT, OrderStatus TINYINT);

GO

INSERT INTO OrderList

SELECT 1 AS OrderNo, 100 AS SerialNo, 1 AS OrderStatus

UNION ALL

SELECT 1, 200, 3

UNION ALL

SELECT 2, 100, 3

UNION ALL

SELECT 2, 240, 4

UNION ALL

SELECT 2, 400, 5;

Not a whole lot of data, but enough to test the result.

ScottPletcher SSC Guru Points: 101099 More actions · Answer 7

Select order_number,

min(serial_number) AS serial_number_min,

max(serial_number) AS serial_number_max,

min(status) AS status_min,

max(status) AS status_max

from orders

where order_closed !=-1

group by order_number

having min(status) > 3

order by Order_number

SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".

Sergiy SSC Guru Points: 110208 More actions · Answer 8

ScottPletcher (12/14/2015)
Select order_number, min(serial_number) AS serial_number_min, max(serial_number) AS serial_number_max, min(status) AS status_min, max(status) AS status_max from orders where order_closed !=-1 group by order_number having min(status) > 3 order by Order_number

Scott, what was that?

_____________
Code for TallyGenerator

Sergiy SSC Guru Points: 110208 More actions · Answer 9

kat35601 (12/14/2015)
so a good order would have 5 items all with a status greater then 3

None of the orders from your data set fits this description.

Does it mean all of them are not good?

_____________
Code for TallyGenerator