MAX Date Applied

[abetancur]

I have the following code:

SELECT     MAX(INVENTORY_TRANSACTION_HIST.DATE_APPLIED) AS LAST_TRANS, INVENTORY_TRANSACTION_HIST.PART_NO
 
FROM         INVENTORY_TRANSACTION_HIST INNER JOIN
                      INVENTORY_PART_IN_STOCK ON INVENTORY_TRANSACTION_HIST.PART_NO = INVENTORY_PART_IN_STOCK.PART_NO 
 
WHERE     (INVENTORY_TRANSACTION_HIST.TRANSACTION_CODE IN ('PICK-IN', 'PICK-OUT', 'NREC', 'ARRIVAL')) AND 
                      (INVENTORY_TRANSACTION_HIST.CONTRACT = :site) AND (INVENTORY_PART_IN_STOCK.QTY_ONHAND > 0) AND 
                      (INVENTORY_TRANSACTION_HIST.DIRECTION <> '0')
 
GROUP BY INVENTORY_TRANSACTION_HIST.PART_NO

The report works fine but, when a record identified with max date applied has a trasaction code not included in the code, the report drops it. I was hoping it would select the next applicable max date applied according to my transaction code criteria. So my question is, how can I get SQL to grab the next 'max date applied' based on the transaction codes identified in the code.

[pbmods]

Heya, abetancur. Welcome to TSDN!

Please use CODE tags when posting source code. See the REPLY GUIDELINES on the right side of the page next time you post.

Try adding to the WHERE clause:

SELECT
        MAX(`INVENTORY_TRANSACTION_HIST`.`DATE_APPLIED`)
            AS `LAST_TRANS`,
        `INVENTORY_TRANSACTION_HIST`.`PART_NO`
    FROM
    (
            `INVENTORY_TRANSACTION_HIST`
        INNER JOIN
            `INVENTORY_PART_IN_STOCK`
                ON `INVENTORY_TRANSACTION_HIST`.`PART_NO` = `INVENTORY_PART_IN_STOCK`.`PART_NO`
    )
    WHERE
    (
            `INVENTORY_TRANSACTION_HIST`.`TRANSACTION_CODE`
                IN
                (
                    'ARRIVAL'
                    'NREC',
                    'PICK-IN',
                    'PICK-OUT',
                )
        AND
            `INVENTORY_TRANSACTION_HIST`.`CONTRACT` = :site
        AND
            `INVENTORY_PART_IN_STOCK`.`QTY_ONHAND` > 0
        AND
            `INVENTORY_TRANSACTION_HIST`.`DIRECTION` <> '0'
        AND    -- ADD THIS:
            `INVENTORY_TRANSACTION_HIST`.`DATE_APPLIED` < '{last value}'
    )
    GROUP BY
        `INVENTORY_TRANSACTION_HIST`.`PART_NO`
 

Note also that when listing values for an 'IN' subclause, you should put them in alphabetical order so that MySQL doesn't have to sort them (MySQL uses a binary search on IN subclauses).