SQL query to get records of a timeframe with sequence of events related to the same identifier-CodePudding

I am learning SQL and I stuck on this:

I have this example table with date fomat (dd/mm/yyyy):

Date	ID	Status
10/09/2022	xxx	opened
07/07/2022	xxx	delivered
01/06/2022	xxx	sent
10/08/2022	yyy	opened
08/08/2022	yyy	delivered
01/08/2022	yyy	sent

I want to get the records for the last 3 months:

 SELECT * FROM TABLE WHERE  Date >= DATEADD(DAY, -90, GETDATE())
 
 AND
 
 Date <= DATEADD(DAY, 0, GETDATE())

Which worked fine but my issue that all Status (sent, delivered & opened) should included per ID in the time frame.

So the above query will get me ID yyy perfectly but for xxx it will break it because the sent status row is on 01/06/2022

I tried to use group by ID and having Status IN ('sent','delivered','opened')

But it does not work and show all the records!

Wanted output is just the same table with records of yyy as one record of xxx status 'sent' is out of the timeframe.

CodePudding user response：

Which worked fine but my issue that all Status (sent, delivered & opened) should included per ID in the time frame.

Adding condition to filter only Ids that have all 3 statuses in the timeframe:

SELECT * 
FROM TABLE 
WHERE Date >= DATEADD(DAY, -90, GETDATE())
  AND Date <= DATEADD(DAY, 0, GETDATE())
QUALIFY COUNT(DISTINCT CASE WHEN Status IN ('sent','delivered','opened') 
                       THEN status END) 
        OVER(PARTITION BY Id) = 3