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

KitaYama · May 27, 2023

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:

Customer	ProductFK	Shipping	Delivery
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

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 :

Customer	ProductFK	Shipping	Delivery
Customer1	3	2023/04/22	2023/04/25
Customer2	2	2023/06/03	2023/06/10
Customer3	1	2023/08/08	2023/08/13
Customer5	4		2023/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.

561414 · May 27, 2023

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

KitaYama · May 27, 2023

Unfortunately No.
It's mostly where my attempts were failing. Null shipping were ruining my queries.
This is the result of your query.

thanks for trying to help though.

561414 · May 27, 2023

Except for customer2 the other customers also have null shipping, what does this depend on again?

KitaYama · May 27, 2023

561414 said:
null shipping, what does this depend on again?

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

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.

ebs17 · May 27, 2023

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)
      )

561414 · May 27, 2023

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

561414 · May 27, 2023

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.

KitaYama · May 27, 2023

ebs17 said:
Shipping is always smaller than Delivery?

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

Customer ProductFK Shipping Delivery
Customer2
2
2023/06/03
2023/06/10
Customer2
3
2023/06/05
2023/06/08

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.

KitaYama · May 27, 2023

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.

Will test and report back.
Thanks.

KitaYama · May 27, 2023

@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:

Customer ProductFK Shipping Delivery
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

this is the result :

Problems:
1- Two records for Customer4

Thanks again.

561414 · May 27, 2023

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.

Those two records have the same shipping date, what do you do in this case?

ebs17 · May 27, 2023

Problems:
1- Two records for Customer4

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.

KitaYama · May 27, 2023

561414 said:
Those two records have the same shipping date, what do you do in this case?

In this case, the smaller delivery needs to be shown.

Thank you.

KitaYama · May 27, 2023

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).

While I'm testing here's another problem.

tbl tbl

Customer ProductFK Shipping Delivery
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

This is the result

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

ebs17 · May 27, 2023

Customer 4 should show the second record not the last one.

Why?
2023/02/02 is smaller as 2023/02/01?

Rules should be kept away from a lottery wheel.

KitaYama · May 27, 2023

ebs17 said:
Why?
2023/02/02 is smaller as 2023/02/01?

Rules should be kept away from a lottery wheel.

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.

The_Doc_Man · May 27, 2023

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.

KitaYama · May 27, 2023

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.

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.

ebs17 · May 27, 2023

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

Customer	ProductFK	Shipping	Delivery
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

Customer	ProductFK	Shipping	Delivery
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

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

Well-known member

Attachments

Active member

Well-known member

Active member

Well-known member

Well-known member

Active member

Active member

Well-known member

Well-known member

Well-known member

Active member

Well-known member

Well-known member

Well-known member

Well-known member

Well-known member

Immoderate Moderator

Well-known member

Well-known member

Similar threads

Users who are viewing this thread