Deteremine latest date, with NULL's being the latest

[gwunta1907]

Hi all, Im not sure if this is possible. Any gurus out there might be able to answer this one.

I have a table with, amongst other information, delivery dates for consignments. Each consignment consists of one or more containers. Where there are multiple containers. As each container arrives at its destination, the arrival date is recorded in the table. I need to update another table that has consignment arrival dates (it doesn't show container level detail). The consignment arrivals table needs to store the date of the last container to arrive for the specific consignment. To create a query that will accomplish this is easy (its just a max query essentially).

The problem comes where all containers in a consignment have not yet been delivered. The are some consignments where, for example, half of the containers have arrived so there are arrival dates for some containers, but for other containers there are Null values (since they haven't arrived yet). So my question is this: Is it possible to write a query that, where all containers have arrived for a consignment, select the date of the last container to arrive AND if all container have not yet been delivered for the consignment (ie there are null values still in the consignment's container records arrival date field), do not select the last container arrival date.

Essentially, the NULL value acts as a wild card. If a null is present, then that becomes the 'latest date' otherwise, if no nulls are found then the last container arrival date is used.

Below is a cut down example of data in each table:

Containers Table

consNum contNum arrivalDate
12345 0001 1/2/2012
12345 0002
23456 1111 17/5/2012
23456 1112 18/6/2012
23456 1113 19/5/2012
99901 1001 16/7/2011
99901 1002 18/7/2012
40345 2222 19/7/2012

The query should be able to return the following based on the data on the table above

consNum arrivalDate
12345
23456 18/6/2012
99901 18/7/2012
40345 19/7/2012

Many thanks in advance

 

[Guus2005]

When you sort on arrivaldate it start with the NULL values.
Isn't that what you want?

HTH

 

[Brianwarnock]

I don't think that this can be done with one query, but if you have you max query and run that against the table to update the dates then have a query selecting containers with a null date and do a second update with that leaving the containers not selected unchanged.

Brian

 

[Guus2005]

Here's your query

SELECT Container.id, Container.consNum, Container.contNum, Container.arrivalDate
FROM [Container]
WHERE (((Container.id) Not In (SELECT Container.id
   FROM [Container]
   WHERE (((Container.consNum) In (SELECT Container.consNum
       FROM [Container]
       WHERE (((Container.arrivalDate) Is Null)))) AND ((Container.arrivalDate) Is Not Null))
)));

It uses two nested subqueries.

One to select the consNum with a Null value
The second uses the first to get the id from the record where the arrivaldate is not null.
The main query uses the second to create a selection without the one you don't want.

Share & Enjoy!