May 19, 2021 at 10:10 pm
I have two tables:
OrderItems tabel
OrderId OrderDate ItemNo ItemDescrepation Unit Price
1001 03-15-2020 91570 Charger 12V $50
1205 05-06-2020 91570 Charger 12V $80
3020 04-20-2021 91570 Charger 12V $100
1050 04-18-2020 89002 Hard Drive 1GB $200
2045 06-08-2020 89002 Hard Drive 1GB $180
3350 05-03-2021 89002 Hard Drvie 1GB $200
4590 05-18-2021 89002 Hard Drive 1GB $220
Orders table
OrderId Supplier Buyer
1001 BestBuy George
1050 Microcenter George
1205 BestBuy George
3020 Microcenter George
3350 BestBuy George
4590 BestBuy George
There are many orders in the Orders table and many different items for each order.
Same items may be repeated in different orders.
I would like to get the last(latest) two prices and item no by the buyer for each item joining these two tables.
The result should be like
Date ItemNo Price Supplier
04-20-2021 91570 $100 MicroCenter
05-06-2021 91570 $80 BestBuy
05-18-2021 89002 $220 BestBuy
05-03-2021 89002 $200 BestBuy
May 19, 2021 at 11:56 pm
You need to use CROSS APPLY for this... the standard way of doing it (I'm gonna cheat and use Products and SalesLineItems) is like this:
SELECT p.ProductName, ca.SalePrice, ca.SalesQty, ca.SaleDate
FROM Products p
CROSS APPLY (SELECT TOP 2 SalePrice, SalesQty, SaleDate
FROM SalesLineItems sli
WHERE sli.ProductID = p.ProductID
ORDER BY SaleDate DESC) ca
ORDER BY p.ProductName,
ca.SaleDate
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply