CODEWITHSUNDEEP
sqlsql-server
Pegaz
Pegaz

Reputation: 377

Using sub query result in multiple exist conditions

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)

Upvotes: 1

Views: 54

Answers (2)

forpas
forpas

Reputation: 164064

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

Upvotes: 1

vc 74
vc 74

Reputation: 38179

The below query should do it

select ProductId
from
(
    select
        ProductId,
        Event,
        Lag(Event, 1) over (partition by ProductId order by Date) as PrevEvent
    from AuditLog
    where Event in ('ApproveProduct', 'ViewProduct')
)
where PrevEvent = 'ApproveProduct' and Event = 'ViewProduct'

I didn't add a join with the Product table to make the SQL clearer but it should be trivial.

Upvotes: 1

Related Questions