0

I am reposting this question. I have cleaned up the SQL but there are still a few duplicate results. I need to find the most recent data for each itemnum in each location. Here's what I have:

SELECT invbalances.itemnum, 
invbalances.curbal, 
inventory.maxlevel, 
inventory.minlevel2, 
inventory.sstock,
inventory.deliverytime, 
invbalances.location, 
inventory.category
FROM MX7PROD.dbo.invbalances invbalances, 
MX7PROD.dbo.inventory inventory
WHERE invbalances.itemnum = inventory.itemnum 
AND invbalances.location = inventory.location 
AND ((inventory.category<>'cstk' 
And inventory.category<>'nore' 
And inventory.category<>'sp') 
AND (invbalances.itemnum Not Like '%n%'))
ORDER BY invbalances.itemnum
tshepang
  • 12,111
  • 21
  • 91
  • 136
user3341199
  • 7
  • 3
  • No one can answer this question until you explain what logic constitutes the "latest" rows of data. In other words, the highest value of X, for each itemnum at each location. Unfortunately only you know what X is. – Brian DeMilia Feb 22 '14 at 19:04

1 Answers1

0

Assuming the "most recent data" is determined by the highest curbal value for each itemnum in each location this should work: (it seemed that you reached that conclusion the last time you asked this question)

SELECT invbalances.itemnum,
       invbalances.curbal,
       inventory.maxlevel,
       a_inventory52.minlevel,
       inventory.sstock,
       inventory.deliverytime,
       inventory.category,
       inventory.lastissuedate
  FROM MX7PROD.dbo.a_inventory52 a_inventory52,
       MX7PROD.dbo.invbalances   invbalances,
       MX7PROD.dbo.inventory     inventory
 WHERE invbalances.itemnum = inventory.itemnum
   AND a_inventory52.itemnum = invbalances.itemnum
   AND inventory.category <> 'cstk'
   And inventory.category <> 'nore'
   And inventory.category <> 'sp'
   and invbalances.itemnum not like '%n%'
   and invbalances.curbal =
       (SELECT MAX(x.curbal)
          FROM MX7PROD.dbo.inventory x
         where x.itemnum = invbalances.itemnum
           and x.location = invbalances.location)

Note that your grouping of criteria in the where clause using parentheses has no effect on the result because you're using AND on every line, so I ungrouped them.

Brian DeMilia
  • 13,103
  • 1
  • 23
  • 33
  • Unfortunately the max curbal isn't always the actual one. Thanks for the help with the parentheses – user3341199 Feb 22 '14 at 18:55
  • So what determines the most recent data? You still haven't said what fields determine what is "most recent" for your purpose. – Brian DeMilia Feb 22 '14 at 18:56
  • It seems that I can't find a field that will determine the most current data. The lastissuedate field I was working with is applied universally to every entry, so that's no good. I'm going to have to search through other tables (I had nothing to do with the database creation) to find a unique identifier. Thank you so much for your help. – user3341199 Feb 22 '14 at 19:06
  • Okay, well it can be more than one field. For instance if the latest data was the highest curbal for each item at each location, but on top of that logic, the last issue date for each row where the curbal is the max, that could be done (not saying that's the case, it probably isn't). If you give provide some example rows of data and indicate which rows you actually want in the result (are the most recent) we'll probably be able to better help. – Brian DeMilia Feb 22 '14 at 19:14