sql - Using sub query result in multiple exist conditions

Question

I'm trying to get all products based on two conditions on another table. See my example db data.

Product
--------------
ProductID     ProductName
1             P1
2             P2
3             P3

AuditLog
------------
Event             Date                 ProductId
ApproveProduct    2016-01-27 16:00     1
ViewProduct       2016-01-27 17:00     1
ViewProduct       2016-01-27 15:00     2
ApproveProduct    2016-01-27 17:00     2

Based on the example db data, I would like to get all products that have ViewProduct auditlog entry AFTER ApproveProduct entry based on the Date. I would like to return first product since second product has the AuditLog entries in wrong order:

ProductID        ProductName
1                P1

I'm trying to achieve something like below, but obviously that code is not valid:

SELECT p.* FROM Product p
WHERE EXISTS (SELECT TOP 1 Date 
FROM AuditLog WHERE Event = 'ApproveProduct' AND ProductId = p.ProductID) ap
AND EXISTS (SELECT 1 
FROM AuditLog WHERE Event = 'ViewProduct' AND Date > ap.Date AND ProductId = p.ProductID)

Answer 1

You can use conditional aggregation to get the ProductIDs that you want:

SELECT ProductId 
FROM AuditLog
GROUP BY ProductId
HAVING MAX(CASE WHEN Event = 'ViewProduct' THEN Date END) >
       MAX(CASE WHEN Event = 'ApproveProduct' THEN Date END)

and also the operator IN to get the product details:

SELECT *
FROM Product
WHERE ProductId IN (
  SELECT ProductId 
  FROM AuditLog
  GROUP BY ProductId
  HAVING MAX(CASE WHEN Event = 'ViewProduct' THEN Date END) >
         MAX(CASE WHEN Event = 'ApproveProduct' THEN Date END)
)

See the demo.
Results:

> ProductID | ProductName
> --------: | :----------
>         1 | P1