Have results split into two colums (3 Viewers)

[Ken Sheridan]

I'm trying to say that it cannot be decided which of these is correct:
A14212 19/06/2025 111 444242 5 --- 1
A14212 19/06/2025 111 444242 9 --- 2
or
A14212 19/06/2025 111 444242 5 --- 2
A14212 19/06/2025 111 444242 9 --- 1

Maybe the OP does not care. That would then be the additional rule.

In the 28 years in which I have been answering newsgroup and forum posts I have always based my replies on the same basis as I would in my professional life, that any conclusion can not be more than a working hypothesis. If this can be tested by observation then the hypothesis stands until contrary evidence becomes available.

Firstly what are the known facts? In this case the OP has categorically stated that there can be no more than two bins per order. I would not presume to think I know their job better than they do, and doubt that this is the case.

Secondly what can be reasonably inferred from the available evidence? In the original post the OP shows the Pick_Bin_1 column to contain the lower values per order and Pick_Bin_2 the higher value.

The OP's example includes no rows with the same value in each Pick_Bin column.

On the basis of the available evidence, therefore, the simple aggregating query I posted earlier provides a solution. If the evidence should change, so that the hypothesis does not stand up to observation, then the hypothesis would be revised or discarded. That's how science works.

 

[CJ_London]

I'm late jumping into this, but can't you use a simple aggregating query:

Would only ever be max of 2

Which implies there can be 1

So your aggregate query would need to be modified to be conditional on the max <> min for bin 2

Iif(max <>min,max) as bin2

 

[Pat Hartman]

We should probably take a poll of how many developers here have been burned by the client telling them - there will NEVER be more than two of these things.

 

[Ken Sheridan]

Which implies there can be 1

That had occurred to me. The following should handle it:

SELECT
    S1.Customer_ID,
    S1.Order_Date,
    S1.Order_No,
    S1.Item_Code,
    MIN(S1.Pick_Bin) AS Pick_Bin_1,
    IIF(
        (
            SELECT
                COUNT(*)
            FROM
                Sales AS S2
            WHERE
                S2.Order_No = S1.Order_No
        ) = 2,
        MAX(S1.Pick_Bin),
        0
    ) AS Pick_Bin_2
FROM
    Sales AS S1
GROUP BY
    S1.Customer_ID,
    S1.Order_Date,
    S1.Order_No,
    S1.Item_Code;

I'd prefer to return zero in Pick_Bin_2 in the event of there being only one item in the order, rather than NULL, due to the semantic ambiguity of the latter.