How to select first expiry items in a select query
DECLARE @tblProduct AS TABLE (PCode Int, GName Nvarchar(50), OnHand Int)
INSERT INTO @tblProduct (PCode, GName, OnHand) VALUES (1, 'Product 1', 40)
INSERT INTO @tblProduct (PCode, GName, OnHand) VALUES (2, 'Product 2', 80)
INSERT INTO @tblProduct (PCode, GName, OnHand) VALUES (3, 'Product 3', 50)
INSERT INTO @tblProduct (PCode, GName, OnHand) VALUES (4, 'Product 4', 10)
DECLARE @tblStockInventory AS TABLE (ID Int, CCode Int, SelRate Decimal(18,2), ExpDate Datetime)
INSERT INTO @tblStockInventory (ID, CCode, SelRate, ExpDate) VALUES (1, 1, 15.47, '12-Feb-2019 12:00:00 AM')
INSERT INTO @tblStockInventory (ID, CCode, SelRate, ExpDate) VALUES (2, 1, 18.52, '20-Mar-2019 12:00:00 AM')
INSERT INTO @tblStockInventory (ID, CCode, SelRate, ExpDate) VALUES (3, 2, 6.32, '15-Feb-2019 12:00:00 AM')
INSERT INTO @tblStockInventory (ID, CCode, SelRate, ExpDate) VALUES (4, 3, 10.52, '20-Feb-2019 12:00:00 AM')
INSERT INTO @tblStockInventory (ID, CCode, SelRate, ExpDate) VALUES (5, 4, 8.00, '10-Feb-2019 12:00:00 AM')
INSERT INTO @tblStockInventory (ID, CCode, SelRate, ExpDate) VALUES (5, 4, 12.00, '25-Feb-2019 12:00:00 AM')
SELECT CCode, GName, SelRate, OnHand, tblStockInventory.ExpDate, ID FROM
tblStockInventory INNER JOIN tblProduct ON tblProduct.PCode = tblStockInventory.CCode WHERE OnHand > 0
AND tblStockInventory.ExpDate IN (SELECT TOP 1 ExpDate FROM [tblStockInventory] AS Temp WHERE Temp.CCode = [tblStockInventory].CCode AND OnHand > 0
AND tblStockInventory.ExpDate > GETDATE() ORDER By ExpDate ASC) --AND tblStockInventory.ExpDate > GETDATE()
GROUP BY CCode, GName, OnHand, SelRate, ID, tblStockInventory.ExpDate
