Find if event happened within N days after every record using SQL-CodePudding

Im trying to figure out a way to in SQL find if an event had a different event happening after it within a N days period

So for example, if my data looks as follows:

User_ID Code    Date
1122    A   01-01-2020
1122    A   02-01-2020
1122    A   02-01-2020
2233    A   04-01-2020
2233    A   04-01-2020
1122    B   04-01-2020
3344    A   07-01-2020
3344    B   07-01-2020
2233    A   08-01-2020

and say N=2, meaning i need to find if an event B happened within 2 days an event A for each user, and if that is the case, i need to filter out the event A

So in my data, there is one event A on day 1 and two events A for user 1122 on day 2 and then an event B on day 4. The event B happened 3 days after the first event A for user 1122, and 2 days after the two events A for user 1122. So those two A event should be tagged: (same for user 3344 who had an event B in the same day as an A, so complies with the 2 days as well)

  User_ID   Code    Date    B_within_2_days
1122    A   01-01-2020  NO
1122    A   02-01-2020  YES
1122    A   02-01-2020  YES
2233    A   04-01-2020  NO
2233    A   04-01-2020  NO
1122    B   04-01-2020  NO
3344    A   07-01-2020  YES
3344    B   07-01-2020  NO
2233    A   08-01-2020  NO

CodePudding user response：

So generally, there are two approaches. You can make a scalar function (or UDF) that takes in the needed parameters to run a query against that same table looking for the related event B, then it just returns a true or false. This will not be great performance.

Alternatively, you could use a stored procedure and go thru them using a cursor, assembling the final result set to be returned.

CodePudding user response：

select *,
    case when Code = 'A' and exists (
          select 1 from T where Code = 'B'
             and "Date" between t1."Date" and date_add(t1."Date", 2)
      ) T then 'YES' else 'NO' end
from T t1