SELECT
ROW_NUMBER() OVER (PARTITION BY O.orders_id ORDER BY O.orders_id, OP.tktSPAmount DESC) [Rank], OP.orders_products_id ID,
O.date_purchased [DocDate], O.usps_track_num2 [Formula Drive By], REPLACE(REPLACE(O.remarks, CHAR(13), ”), CHAR(10), ”) [Remarks], REPLACE(REPLACE(O.terms, CHAR(13), ”), CHAR(10), ”) [Sample Text], REPLACE(REPLACE(O.[days], CHAR(13), ”), CHAR(10), ”) [Boxcode], O.documentTextId DocumentId,
OP.products_id ItemID, OP.tktSPAmount RequiredQuantity, hotelArrivalBy FilledQuantity, hotelDateTimeTo FilledOn
FROM orders O
LEFT JOIN orders_products OP ON O.orders_id = OP.orders_id
WHERE date_purchased >= ‘2022-08-15’ AND docTypeId = 1771
ORDER BY [DocDate], O.orders_id, RequiredQuantity DESC
