I have a Product table and I join it with OrderTracking table
SELECT Product.ProductCode,
FROM Product LEFT OUTER JOIN OrderTracking
ON Product.ProductCode=OrderTracking.ProductCode
GROUP BY ProductCode
There are past and future order infos of products in my OrderTracking table. My goal is If there are future orders of a product I want to list first 2 of them(closest 2 orders to today) in same row. If there is no order in the future, columns will be null or if there is just one order in future, just second column will be null. Example row will be like that:
ProductCode
1stClosestDeliveryDate
2ndShippingNumber
SELECT Product.ProductCode, 1stClosestDeliveryDate,1tClosestOrderAmount,
1stShippingNumber,2stClosestOrderDate, 2stClosestDeliveryAmount,
2stShippingNumber
FROM Product LEFT OUTER JOIN OrderTracking
ON Product.ProductCode = OrderTracking.ProductCode
GROUP BY Product.ProductCode
I should use GETDATE() function and compare with other date values but I couldn't figure out how to get these values from OrderTracking table. Any help will be appreciate...
use a CTE with ROW_NUMBER:
WITH orders AS
(
SELECT ROW_NUMBER() OVER (PARTITION BY ProductCode ORDER BY OrderDate ASC) AS RNumber
, ProductCode
, OrderDate
, OrderAmount
, OrderShippingNumber
FROM OrderTracking
WHERE OrderDate > GETDATE()
)
SELECT Product.ProductCode
, 1stClosestOrderDate = O1.OrderDate
, 1tClosestOrderAmount = O1.OrderAmount
, 1stShippingNumber = O1.OrderShippingNumber
, 2stClosestOrderDate = O2.OrderDate
, 2stClosestOrderAmount = O2.OrderAmount
, 2stShippingNumber = O2.OrderShippingNumber
FROM Product
LEFT OUTER JOIN orders AS O1 ON Product.ProductCode = O1.ProductCode AND O1.RNumber = 1
LEFT OUTER JOIN orders AS O2 ON Product.ProductCode = O2.ProductCode AND O2.RNumber = 2;
in the structure you post there is some column missing so i guessed some name but the above should be a starting point to get the final result.
the first SELECT (the CTE) gathers the data about the orders adding row numbers by product: rows with row number = 1 are the next order for that product and rows with number = 2 are the second next order.
the second SELECT is there to join table & CTE to get the required output.
WARNING: the CTE syntax requires that the previous statement (if any) is terminated by a ;. since the closing semicolon is not mandatory it's easy to forget it and get syntax errors when running the whole statement in a script with multiple statements.