Filtering down SQL To one value?

Question

I'm writing a query for SAP B1,using Microsoft SQL Server Management Studio 17.

I am wanting to return the "MIN" DocDate, but show the field "ShipDate" alongside it, with only one result...

SELECT T0.ItemCode, 
    T3.ShipDate AS 'Next Date',
    MIN(T3.DocDate) AS 'OrderDate'
FROM OITM T0
LEFT OUTER JOIN POR1 T3 ON T0.ItemCode = T3.ItemCode

WHERE T3.LineStatus = 'O' AND T3.ShipDate > GETDATE()

Group By T0.Itemcode, T3.ShipDate

This returns the following results;

ItemCode	ShipDate	OrderDate
VT3021026	2021-05-14	2021-05-04
VT3021026	2021-06-01	2021-05-10

This is an example of what one single item is returning, however I want to return only one value for each item. Almost like a "TOP 1" for each individual item. (obviously top 1 won't work in the overall select clause because it will be required to produce a list of multiple items)

Answer 1

If I understand you correctly, then this code will solve the problem.

DECLARE @date datetime2 = GETDATE();

WITH T(ItemCode, OrderDate) AS
(
    SELECT 
        T0.ItemCode, 
        MIN(T3.DocDate) AS 'OrderDate'
    FROM OITM T0
        LEFT JOIN POR1 T3 
            ON T0.ItemCode = T3.ItemCode
    WHERE T3.LineStatus = 'O' 
        AND T3.ShipDate > @date
    GROUP BY T0.Itemcode, T3.ShipDate
)
SELECT T.ItemCode, 
    T4.ShipDate AS 'Next Date',
    T.OrderDate
FROM T
    LEFT JOIN POR1 T4
        ON T.[OrderDate] = T4.DocDate
         AND T.ItemCode = t4.ItemCode;