SQL select the last datetime the product has been tradable-CodePudding

I'm using SQL Server, and I'm having a difficult time trying to get the results from a SELECT query. I have 4 tables.

Product
Selling
Customer (data about customers - buyers and sellers).
Matched_Orders

select * from Product;

id(PK) | name_product  | Status
--------------------------------
1      | apple         | 0
2      | orange        | 0
3      | juice         | 1

with :

Status = 0 --> Product no longer tradable
Status = 1 --> Product still tradable

select * from Selling;

OrderID (PK)   | id_seller     | id_product | product_placement_date
--------------------------------------------------------------------
1              | 45            | 3          | 2020-01-09
2              | 46            | 3          | 2020-01-09
3              | 58            | 2          | 2020-02-08
4              | 49            | 2          | 2020-01-04
5              | 43            | 1          | 2020-01-06

select * from Customer;

id_customer(PK) | name_customer
---------------------------
43          | Alice
45          | Sam
46          | Katy
49          | Soul
58          | Fab

select * from Matched_Orders

OrderID(PK) | SellOrderID  | ProductID  | OrderUpdatedDate  
----------------------------------------------------------------
33          | 3            | 2          | 2020-02-09 13:49:07.783     
44          | 1            | 3          | 2020-01-05 14:12:18.310
55          | 5            | 1          | 2020-01-08 08:23:53.730

Where OrderUpdatedDate is the datetime when the product has been confirmed and the example table above is for the last matched orders executed.

In all that, I'm trying to select a bunch of data the last OrderUpdatedDate = LastTradeDate when the product has been tradable. i.e LastTradeDate Date and time when the product ceases to be traded. i.e it only show up when the product is no longer tradable, if product is still tradable obviously we don't know what will be the last date.

I was trying this query :

select
    p.name_product,
    S2.product_placement_date,
    LastTradeDate = MAX(S3.OrderUpdatedDate),
    c.name_customer
from
    (select 
         id_product,
         min(OrderID) as FirstListedID
     from
         selling
     group by
         id_product) First
join 
    selling S2 on First.FirstListedID = s2.OrderID
join 
    Matched_Orders S3 on S4.SellOrderID = S2.OrderID
join 
    customer c on S2.id_seller = c.id_customer
join 
    product p on S2.id_product = p.id
group by 
    p.status 
having 
    p.Status = 0

But I keep having errors such as Column 'p.id' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.

The expected result is the following :

name_product | product_placement_date | LastTradeDate           |  name_customer
-----------------------------------------------------------------------
apple        | 2020-01-06             | 2020-01-08 08:23:53.730 |  Alice
orange       | 2020-01-04             | 2020-02-09 13:49:07.783 |  Soul
juice        | 2020-01-09             | NULL                    |   Sam

Please advise

CodePudding user response：

You can use OUTER APPLY to find your first product placement and the corresponding seller and a second time to find the last trade date.

SELECT p.name_product, s.product_placement_date, mo.OrderUpdatedDate AS LastTradeDate, c.name_customer
FROM Product p
OUTER APPLY (SELECT TOP 1 product_placement_date, id_seller
             FROM Selling
             WHERE id_product = p.id
             ORDER BY product_placement_date, OrderID ASC) s
OUTER APPLY (SELECT TOP 1 OrderUpdatedDate
             FROM Matched_Orders
             WHERE ProductId = p.id AND p.Status = 0
             ORDER BY OrderUpdatedDate DESC) mo
LEFT JOIN Customer c ON c.id_customer = s.id_seller