469,930 Members | 1,831 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,930 developers. It's quick & easy.

MAX Date Applied

I have the following code:
Expand|Select|Wrap|Line Numbers
  1. SELECT     MAX(INVENTORY_TRANSACTION_HIST.DATE_APPLIED) AS LAST_TRANS, INVENTORY_TRANSACTION_HIST.PART_NO
  2.  
  3. FROM         INVENTORY_TRANSACTION_HIST INNER JOIN
  4.                       INVENTORY_PART_IN_STOCK ON INVENTORY_TRANSACTION_HIST.PART_NO = INVENTORY_PART_IN_STOCK.PART_NO 
  5.  
  6. WHERE     (INVENTORY_TRANSACTION_HIST.TRANSACTION_CODE IN ('PICK-IN', 'PICK-OUT', 'NREC', 'ARRIVAL')) AND 
  7.                       (INVENTORY_TRANSACTION_HIST.CONTRACT = :site) AND (INVENTORY_PART_IN_STOCK.QTY_ONHAND > 0) AND 
  8.                       (INVENTORY_TRANSACTION_HIST.DIRECTION <> '0')
  9.  
  10. 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.
Aug 8 '07 #1
1 1652
pbmods
5,821 Expert 4TB
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:
Expand|Select|Wrap|Line Numbers
  1. SELECT
  2.         MAX(`INVENTORY_TRANSACTION_HIST`.`DATE_APPLIED`)
  3.             AS `LAST_TRANS`,
  4.         `INVENTORY_TRANSACTION_HIST`.`PART_NO`
  5.     FROM
  6.     (
  7.             `INVENTORY_TRANSACTION_HIST`
  8.         INNER JOIN
  9.             `INVENTORY_PART_IN_STOCK`
  10.                 ON `INVENTORY_TRANSACTION_HIST`.`PART_NO` = `INVENTORY_PART_IN_STOCK`.`PART_NO`
  11.     )
  12.     WHERE
  13.     (
  14.             `INVENTORY_TRANSACTION_HIST`.`TRANSACTION_CODE`
  15.                 IN
  16.                 (
  17.                     'ARRIVAL'
  18.                     'NREC',
  19.                     'PICK-IN',
  20.                     'PICK-OUT',
  21.                 )
  22.         AND
  23.             `INVENTORY_TRANSACTION_HIST`.`CONTRACT` = :site
  24.         AND
  25.             `INVENTORY_PART_IN_STOCK`.`QTY_ONHAND` > 0
  26.         AND
  27.             `INVENTORY_TRANSACTION_HIST`.`DIRECTION` <> '0'
  28.         AND    -- ADD THIS:
  29.             `INVENTORY_TRANSACTION_HIST`.`DATE_APPLIED` < '{last value}'
  30.     )
  31.     GROUP BY
  32.         `INVENTORY_TRANSACTION_HIST`.`PART_NO`
  33.  
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).
Aug 8 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

2 posts views Thread by androtech | last post: by
5 posts views Thread by Gord | last post: by
4 posts views Thread by Tony | last post: by
8 posts views Thread by John Wildes | last post: by
1 post views Thread by Paul Brady | last post: by
2 posts views Thread by bml337 via AccessMonster.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.