I am trying to query only the Max date dependant on quantityCreate Table dbo.TestParts(Part char(30), Desc1 char(50), Desc2 char(50));Create Table dbo.TestStructure(Model char(30), Part char(30), EDATE smalldatetime, QtyPer float);GOInsert INTO dbo.TestParts Values('101111','Widget A', 'Batteries Not Included'), ('101112','Widget B', 'Batteries Included'), ('101113','Widget C', 'USB'), ('101114','Widget D', 'Serial');Insert INTO dbo.TestStructure Values ('AAA1', '101111','02/01/2012','5'), ('AAA1', '101111','07/01/2013', '0'), ('AAA1', '101112', '12/01/2012', '1'), ('AAA1', '101112', '12/02/2012', '2'), ('AAA1', '101112', '12/03/2012', '3'), ('AAA1', '101113', '12/01/2012', '1'), ('AAA1', '101113', '12/02/2012', '0'), ('AAA1', '101113', '12/03/2012', '5'), ('AAA1', '101114', '12/01/2012', '1');GOSelect ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description, ts.EDATE, ts.QtyPer FROM testing.dbo.TestStructure ts Inner Join testing.dbo.TestParts tp ON ts.Part = tp.Part WHERE ts.Model = 'AAA1'
Looking for a return of:PART Description EDATE QtyPer101112 Widget B ..... 2012-12-03 3101113 Widget C ..... 2012-12-03 5101114 Widget D ..... 2012-12-01 1---------------Widget A should not show because the last date the qty was changed to ZeroClosest that I have come..........(which is pulling the part 101111 which should be incorrect)[CODE] Select ts.Part, RTRIM(tp.Desc1) + ' ' + RTRIM(tp.Desc2) as Description, ts.EDATE, ts.QtyPer FROM testing.dbo.TestStructure ts Inner Join (Select Part,MAX(EDATE) as Date FROM testing.dbo.TestStructure WHERE QTYPER <> '0' Group By Part) m Inner Join testing.dbo.TestParts tp on m.Part=tp.Part on ts.Part = m.Part AND ts.EDATE = m.Date where ts.Model='AAA1' Order By ts.Part[/CODE]