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

[The_Doc_Man]

I would be interested in the reasoning.

My approach divides the problem into discrete parts.

The IIF() expression gives you which of the two fields in the same record was earlier but not null. The bizarre date simply prevents nulls from popping up in an ugly way.

From there, you can build your 2nd-layer query as a list, ORDER BY and GROUP BY as the problem warrants, without so many convoluted steps involving sub-queries.

Look at it this way. A simple layered query may be the same number of internal query elements as a query/sub-query combo. But it is FAR easier to see and understand as a way to get the minimum meaningful date on a given record AND THEN be able to compare those dates across records in a list, sort them, group them, or whatever.

All of the complex sub-queries jump through complex hoops for what is a simple two-part problem... MIN within a row, then MIN across rows.

 

[ebs17]

But it is FAR easier to see and understand ...

That's what I would judge for myself when I see a complete solution. The devil is in the details.

 

[The_Doc_Man]

Ah, ye of little faith. I just showed half of the full solution, enough to take it in any direction needed for step two. What is NOT to see?

 

[ebs17]

Those of little faith believe that it is simple and understandable and, in order to reduce the amount of calculation required, to do without superfluous calculations.

If it's defined that Delivery is never NULL and Shipping is always less than Delivery, then I won't check and handle that and save code for it.
That's simplicity.
At the end, another checks whether a date really comes from a field of the data type and not "xxx".

Solutions have already been shown and some have already been accepted. Your solution would be in demand for me if it were simpler (formulation) or more performant. Your hints make it clear that you have understood the task. However, the proof of "simpler" would have to be provided explicitly.

 

[raziel3]

Never and I mean NEVER leave date fields null in queries. That causes so many problems use a date placeholder as suggested by @The_Doc_Man

Alternatively, you can introduce a helper column

IIF(ISNULL(Shipping), Delivery, Shipping)

and run a subquery on that field.

 

[KitaYama]

Just a heads up:

I tested different situations in my test database with given suggestions. So far seems both @ebs17 and @561414 queries are working perfect except some minor problems.
@561414 query is not editable. Not that we need to edit the result, but having an editable query may be more efficient in future (just in case).

The solution offered by @ebs17 needs a unique ID and I'm not sure if there's one in the query that's going to be used as the source data set of this query. I HOPE OrderPK can be used as the necessary unique field.

I also wasn't able to work on Doc's suggestion and gave up very soon. Mostly because of
1-"The devil is in details" and I believe in ebs17's power in queries and when he says about some possible problems in a solution, I think that's for sure.
2-The solution has got out of my depth.

I passed the given solution in #6 by ebs17 to the guys responsible for our database along with his suggestion in #13 on how to prevent the duplicates. They are far better than me and I'm sure they can work on it. I'm really looking forward for the next version up of our software.

My sincere thanks to all who shared their knowledge and put their time to give their solution and help.
Much appreciated.