Solved How to get Min of two fields of a table/query? (1 Viewer)

K

KitaYama

Well-known member
Local time
Today, 13:40
Joined
Jan 6, 2022
Messages
1,893
Here's my situation.
I have a query that returns all orders that are not yet delivered.
This query contains two date fields : Shipping & Delivery.
Shipping can be null or empty. We don't fill this field in certain situations.
Delivery is always a yyyy/mm/dd date, never null, never empty.
Delivery can be in past. We have delays. Today is 05/27 but we are still manufacturing orders that should be delivered a month ago.

Here's some sample data:

CustomerProductFKShippingDelivery
Customer1
1​
2025/02/03​
2025/02/13​
Customer1
4​
2023/08/12​
2023/08/13​
Customer2
3​
2023/06/05​
2023/06/08​
Customer3
1​
2023/08/08​
2023/08/13​
Customer1
3​
2023/04/22​
2023/04/25​
Customer5
4​
​
2023/04/18​
Customer5
3​
2023/07/16​
2023/07/19​
Customer3
4​
​
2023/08/10​
Customer2
2​
2023/06/03​
2023/06/10​
Customer1
2​
​
2024/03/03​
Customer1
1​
​
2023/05/28​
Customer5
2​
​
2023/07/07​

I'm trying to write a query based on this query that looks at Shipping and Delivery fields for each customer, and finds/shows the record that contains the smallest date.
This is the above data filtered for Customer1

1.png


There are 8 dates and the smallest is 2023/04/22. So the final query should show ONLY the third record (marked in red) for Customer1.
And do the same for other customers. One record per customer that contains the smallest date for that customer (either in shipping or Delivery).
The whole result should be :

CustomerProductFKShippingDelivery
Customer132023/04/222023/04/25
Customer222023/06/032023/06/10
Customer312023/08/082023/08/13
Customer542023/04/18

I hope I'm clear enough in what I'm trying to do here.
A list grouped by Customers that has the smallest date in either shipping or delivery.

Million thanks.
A small database is attached. To simplify the process, I've changed the query to a table.
Thanks again.
 

Attachments

Last edited:
Does this work?
SQL: 
SELECT t.Customer, t.ProductFK, t.Shipping, t.Delivery
FROM tbl AS t
WHERE EXISTS (
    SELECT 1
    FROM tbl AS sub
    WHERE sub.Customer = t.Customer
    GROUP BY sub.Customer
    HAVING MIN(sub.Shipping) = t.Shipping
)
ORDER BY t.Customer

Edit:Never mind, it does not work
 
Last edited:
Unfortunately No.
It's mostly where my attempts were failing. Null shipping were ruining my queries.
This is the result of your query.

2.png


thanks for trying to help though.
 
Except for customer2 the other customers also have null shipping, what does this depend on again?
 
561414 said:
null shipping, what does this depend on again?
Click to expand...
Null shipping?
It may be based on a million conditions. The type of contract. The size of the parts. Delivery addresses. We don't cover shipping for some countries or even prefectures within our country.
In some cases, we don't trust the product be delivered safe. So we carry them ourselves.
There's a long list and it's decided when signing the contract or in some cases the order sheet demands for shipping or asks not to be shipped.
In some cases the customer's truck come to pick up the products to cut the shipping fee.

There's no specific rule for that. And it's out of my reach to decide on that eara.


561414 said:
Except for customer2 the other customers also have null shipping
Click to expand...
Any or all customers may have null shipping.

The order table has more than a million records and maybe half of it contains null shipping. If I filter and show only those that are not delivered, it maybe around 5K records that 1/3d of them have blank shipping.

Thanks again.
 
Shipping is always smaller than Delivery?
With a unique ID in the records:
SQL: 
SELECT
   T.ID,
   T.Customer,
   T.ProductFK,
   T.Shipping,
   T.Delivery
FROM
   tbl AS T
WHERE
   T.ID IN
      (
         SELECT TOP 1
            T0.ID
         FROM
            tbl AS T0
         WHERE
            T0.Customer = T.Customer
         ORDER BY
            Nz(T0.Shipping, T0.Delivery)
      )
 
I'm not sure, this does return your output. I still don't get it though.
SQL: 
SELECT t.*
FROM tbl AS t
INNER JOIN (
    SELECT Customer, MIN(IIF(ISNULL(Shipping), Delivery, IIF(ISNULL(Delivery), Shipping, IIF(Shipping < Delivery, Shipping, Delivery)))) AS MinDate
    FROM tbl
    WHERE Shipping IS NOT NULL OR Delivery IS NOT NULL
    GROUP BY Customer
) AS sub
ON t.Customer = sub.Customer AND (IIF(ISNULL(t.Shipping), t.Delivery, IIF(ISNULL(t.Delivery), t.Shipping, IIF(t.Shipping < t.Delivery, t.Shipping, t.Delivery)))) = sub.MinDate
ORDER BY t.Customer
 
Access likes this better
SQL: 
SELECT
  t.Customer,
  t.ProductFK,
  t.Shipping,
  t.Delivery
FROM
  tbl AS t
WHERE
  (
    (
      (
        IIf(
          IsNull([t].[Shipping]),
          [t].[Delivery],
          IIf(
            IsNull([t].[Delivery]),
            [t].[Shipping],
            IIf(
              [t].[Shipping] < [t].[Delivery], [t].[Shipping],
              [t].[Delivery]
            )
          )
        )
      )=(
        SELECT
          MIN(
            IIF(
              ISNULL(Shipping),
              Delivery,
              IIF(
                ISNULL(Delivery),
                Shipping,
                IIF(
                  Shipping < Delivery, Shipping, Delivery
                )
              )
            )
          )
        FROM
          tbl
        WHERE
          Customer = t.Customer
          AND (
            Shipping IS NOT NULL
            OR Delivery IS NOT NULL
          )
      )
    )
  )
ORDER BY
  t.Customer;

Anyway, I need to sleep, hope someone comes up with a better one.
 
ebs17 said:
Shipping is always smaller than Delivery?
Click to expand...
Sure. Shipping is always smaller than delivery. But I can not check for only delivery.
Because there are overlapping dates that makes it harder to work with.
If you look at Customer2 data
CustomerProductFKShippingDelivery
Customer2
2​
2023/06/03​
2023/06/10​
Customer2
3​
2023/06/05​
2023/06/08​
Click to expand...
First records shipping is sooner than the second's delivery. Even though the second record's delivery is smaller.
So in this case I need the first record as the result.
Because between the four dates, the first one's shipping is the smallest.
We should first start on first record even if the delivery is later.

Thanks for helping.
I'll check your try and will be back.
 
561414 said:
Access likes this better
SQL: 
SELECT
  t.Customer,
  t.ProductFK,
  t.Shipping,
  t.Delivery
FROM
  tbl AS t
WHERE
  (
    (
      (
        IIf(
          IsNull([t].[Shipping]),
          [t].[Delivery],
          IIf(
            IsNull([t].[Delivery]),
            [t].[Shipping],
            IIf(
              [t].[Shipping] < [t].[Delivery], [t].[Shipping],
              [t].[Delivery]
            )
          )
        )
      )=(
        SELECT
          MIN(
            IIF(
              ISNULL(Shipping),
              Delivery,
              IIF(
                ISNULL(Delivery),
                Shipping,
                IIF(
                  Shipping < Delivery, Shipping, Delivery
                )
              )
            )
          )
        FROM
          tbl
        WHERE
          Customer = t.Customer
          AND (
            Shipping IS NOT NULL
            OR Delivery IS NOT NULL
          )
      )
    )
  )
ORDER BY
  t.Customer;

Anyway, I need to sleep, hope someone comes up with a better one.
Click to expand...
Will test and report back.
Thanks.
 
@ebs17 @561414
First experiments were promising. For a second I thought we have the answer, but my colleague's tests proved me wrong.
He added several new records for Customer4 as following:
CustomerProductFKShippingDelivery
Customer4
6​
2024/01/01​
2024/01/02​
Customer4
1​
​
2023/03/12​
Customer4
2​
2023/02/01​
2023/04/12​
Customer4
6​
2023/02/01​
2023/02/02​
Click to expand...
this is the result :

3.png


Problems:
1- Two records for Customer4

Thanks again.
 
Last edited:
KitaYama said:
@ebs17 @561414
First experiments were promising. For a second I thought we have the answer, but my colleague's tests showed I'm wrong.
He added several new records for Customer4 as following:

this is the result :

View attachment 108150

Problems:
1- Two records for Customer4

Thanks again.
Click to expand...
Those two records have the same shipping date, what do you do in this case?
 
Problems:
1- Two records for Customer4
Click to expand...
Naturally. If 2023/02/01 occurs five times as the smallest date, there are five records due to equality.

You have to differentiate more clearly in the sorting:
SQL: 
         ORDER BY
            Nz(T0.Shipping, T0.Delivery),
            T0.Delivery


         ORDER BY
            Nz(T0.Shipping, T0.Delivery),
            T0.ID

Other sorting criteria differentiate more precisely. At the latest, the unique ID then only allows one record (per customer).

The problem will rather be the performance, due to the system correlated subquery.
Good indexing can mitigate the problem somewhat, but doesn't really fix it.
 
ebs17 said:
Naturally. If 2023/02/01 occurs five times as the smallest date, there are five records due to equality.

You have to differentiate more clearly in the sorting:
SQL: 
         ORDER BY
            Nz(T0.Shipping, T0.Delivery),
            T0.Delivery


         ORDER BY
            Nz(T0.Shipping, T0.Delivery),
            T0.ID

Other sorting criteria differentiate more precisely. At the latest, the unique ID then only allows one record (per customer).
Click to expand...
While I'm testing here's another problem.

tbl tbl
CustomerProductFKShippingDelivery
Customer4
6​
2024/01/01​
2024/02/02​
Customer4
1​
​
2023/02/02​
Customer4
2​
​
2023/02/05​
Customer4
6​
2023/02/01​
2023/02/05​
Click to expand...

This is the result

6.png


I don't think it's an sort as mentioned above.
Customer 4 should show the second record not the last one.

Sorry I was wrong. The result is correct.
Still testing.
Thanks
 
Last edited:
Customer 4 should show the second record not the last one.
Click to expand...
Why?
2023/02/02 is smaller as 2023/02/01?

Rules should be kept away from a lottery wheel.
 
ebs17 said:
Why?
2023/02/02 is smaller as 2023/02/01?

Rules should be kept away from a lottery wheel.
Click to expand...
Sorry you are correct.
I was in a hurry and 0 concentrated.
Saturday evening and the weight and stress of a whole week on my shoulder.

My Apologies.
 
Last edited:
My approach might be simpler. I tend to use Divide and Conquer techniques. Start this way:

Code: 
SELECT Customer, ProductFK, Shipping, Delivery, 
   IIF( NZ( Shipping, #1-Jan-2099#) < NZ( Delivery, #1-Jan-2099# ), Shipping, Delivery ) As LeastDate
FROM tbl

The NZ sequence picks an arbitrary future date that would be unlikely to be less than any applicable date. If you REALLY want to assure you can't get caught on that date, you can go as high as #31-Dec-9999# as a future date place-holder.

Then write a simple layered query that gives you the record that has MIN( LeastDate ). You could even use the first query in a DLookup if you only wanted one date and know the customer and product.
 
Doc, This is Saturday night and I'm home. Our server is scheduled to be down for maintenance for the weekend and I don't have remote desktop access to my PC at work. Unfortunately I don't have Access at home.
I'll test it Monday morning as soon as I'm back to my desk.


The_Doc_Man said:
Then write a simple layered query that gives you the record that has MIN( LeastDate ). You could even use the first query in a DLookup if you only wanted one date and know the customer and product.
Click to expand...
No, I need a list. This list will be used for the priority for manufacturing line. So we have to see the whole list to decide which job will be the next one.

Thank you.
 
Code: 
IIF( NZ( Shipping, #1-Jan-2099#) < NZ( Delivery, #1-Jan-2099# ), Shipping, Delivery )

' is easier than
Nz(Shipping, Delivery)
I would be interested in the reasoning.

As a solution via a JOIN:
SQL: 
SELECT
   T.Customer,
   T.ProductFK,
   T.Shipping,
   T.Delivery
FROM
   tbl AS T
      INNER JOIN
         (
            SELECT
               Customer,
               MIN(Nz(Shipping, Delivery)) AS MinDate,
               MIN(Delivery) AS MinDelivery
            FROM
               tbl
            GROUP BY
               Customer
         ) AS X
         ON T.Customer = X.Customer
            AND
         Nz(T.Shipping, T.Delivery) = X.MinDate
            AND
         T.Delivery = X.MinDelivery
 
You must log in or register to reply here.

Similar threads

K
Total and Average of Inventory Sold by rolling Daily, 30Day, 90Day, 365Day
Replies
9
Views
694
CJ_London
C
K
Solved Report Group by Day with a Start time of 4:00:00am, End time 3:59:59AM the next Day
Replies
5
Views
526
kjbrack
K
B
SQL Query Challenge: Finding the Latest Records
Replies
4
Views
622
ByteMyzer
B
W
Solved Duplicate Items in Query
Replies
5
Views
542
wmix
W
L
Auto Populate a few date fields
Replies
7
Views
834
XPS35
XPS35

Users who are viewing this thread

Total: 2 (members: 0, guests: 2)
Back
Top Bottom