Max () is driving me batty
MasterPart Component EFFDATE QTYPER
1111 zzzz 2006-10-5 20
1111 zzzz 2007-9-1 0
1111 zaza 2007-9-1 20
I need to return components that are active. In the above table the only item that should return is the component zaza (In other words on Oct 5 2006 a part(zzzz) was added, however on Sept 1 2007 it was zero'd out and a new part(zaza) was added)
By getting the Max(EFFDATE) from the table I need to then see if the QTYPER is greater than 0 if not then I do not need it to show.
Will this work?
SELECT MasterPart,MAX(EFFDATE)as EFFDATE, QTYPER
FROM partDates
GROUPBY MasterPart, QTYPER
HAVING QTYPER>0
