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

Question

Post reply

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

kat35601

SSC Eights!

Points: 816
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: 76776 More actions · Answer 1

drew.allen

SSC Guru

Points: 76776

December 14, 2015 at 1:56 pm

#1845926

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: 816 More actions · Answer 2

kat35601

SSC Eights!

Points: 816

December 14, 2015 at 2:00 pm

#1845929

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: 63198 More actions · Answer 3

pietlinden

SSC Guru

Points: 63198

December 14, 2015 at 2:08 pm

#1845931

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: 816 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: 76776 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: 63198 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: 100262 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) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!

Sergiy SSC Guru Points: 110192 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: 110192 More actions · Answer 9

Sergiy

SSC Guru

Points: 110192

December 14, 2015 at 6:03 pm

#1845966

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