Query Problems 1

[Mighty]

Hi Folks,

I have a slightly strange one. I have a table that stores a history of all order shipments. It is possible to ship orders on more than one day.

What I need to be able to do is get any number of orders and find out the last day that they all shipped. So if I have 5 orders, I need to be able to find out the last date that all 5 of those orders shipped on the same day.

Date Orders Shipped

Aug 1st 1, 2, 5
Aug 2nd 1,2,3,4,5
Aug 3rd 2,4,5
Aug 4th 2,3,4,5
Aug 6th 1,2,3,4

So if I run a query to find the last time all orders shipped on the same day, it should return August 2nd.

Any ideas??

Mighty

 

[Crowley16]

is that exactly how the table stores the orders shipped?

if so, then your main problem is separating all those order numbers...

I suggest you normalise your table and have some sort of 1-many link between date and orders shipped...

--------------------
Procrastinate Now!

 

[Mighty]

Sorry , the table is not structure like that. The records would be like below:

Date Order Number
Aug 1st 1
Aug 1st 2
Aug 1st 5
Aug 2nd 1
Aug 2nd 2
Aug 2nd 3
Aug 2nd 4
Aug 2nd 5
Aug 3rd 2

etc, etc, etc

Mighty

 

[Crowley16]

I see, that makes it much easier...

just select all orders, grouped by date, and in the where condition, test that your choosen order numbers are in the selection...

e.g.

SELECT [Date], OrderNum FROM tbl WHERE OrderNum IN(1,2,3) GROUP BY [Date]

I think that will work, play about with the groupings a bit

--------------------
Procrastinate Now!

 

[Mighty]

Will that query not just give me the last date that any of the orders shipped. Of the data in my first post, that query will return the last date. But I want August 2nd because it was the last date that all orders shipped.

Mighty

 

[Crowley16]

does it?

you might also try using lots of ANDS...

select date from tbl where order num = 1 AND order num = 2...

this will have to be dynamically generated though...

--------------------
Procrastinate Now!

 

[Mighty]

Tried that an it didn't return any records even though there is data there.

Your first suggestion:

SELECT [Date], OrderNum FROM tbl WHERE OrderNum IN(1,2,3) GROUP BY [Date]

will just return the date that any of the orders in (1,2,3) were shipped - not the dates where they all shipped!!

Mighty

 

[Crowley16]

hmm, looks like it'll take something more complicated...

what about a subquery?

select date from tbl
where Num1 IN(select orderNum from tbl group by date)
AND Num2 IN(select orderNum from tbl group by date)
AND Num3 IN(select orderNum from tbl group by date)
...



--------------------
Procrastinate Now!

 

[Crowley16]

Oops, the sub queries should be...

Num1 IN(select orderNum from tbl where date = tbl.Date group by date)

tbl.Date is the alias of the table in your main query...

--------------------
Procrastinate Now!

 

[PHV]

A starting point:
SELECT [Date] FROM tbl WHERE OrderNum IN (1,2,3,4,5) GROUP BY [Date] HAVING COUNT(*)=5;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[Mighty]

That worked a treat PH. Thanks a million.

Mighty