Having an AND operator function 'simultaneously' on a per row basis SQL

user_12738857 0
Viewed 39

I have the following table as a starting point, we will call it table1:

id Key Some_Event
01 1 Event1
02 1 Event2
03 1 Event2
04 2 Event1
05 2 Event2
06 3 Event1
07 3 Event2
08 3 Event1
09 3 Event2
10 4 Event1
11 4 Event1
12 4 Event2

What I want to do is to display the event and which event succeeds that event on a per Key basis. However, I also want to filter out scenarios where an Event2 is succeeded by another Event2 for example if you look at the records with a Key of 1. The desired output would be as follows:

id Key Some_Event Next_Event
01 1 Event1 Event2
02 1 Event2 NULL
04 2 Event1 Event2
05 2 Event2 NULL
06 3 Event1 Event2
07 3 Event2 Event1
08 3 Event1 Event2
09 3 Event2 NULL
10 4 Event1 Event1
11 4 Event1 Event2
12 4 Event2 NULL

I have written the following query which has some flaws. I am not sure if I have went the best route but I am struggling to filter out the scenarios where an Event2 is succeeded by another Event2:

SELECT    id, 
          Key, 
          IIF(t.Some_Event = 'Event1', t.NEXT_EVENT, NULL) AS Next_Event
FROM      (
            SELECT LEAD(Some_Event, 1, 0) OVER (ORDER BY Key) AS NEXT_EVENT
            FROM table1
          ) t
WHERE NOT `Some_Event` = `Event2` AND t.Some_Event = t.NEXT_EVENT
ORDER BY  t.Key

With the way I have done it, the issue is in my where clause. The AND operator is functioning in a seperate manner, what I want is if Some_Event = Event2 then if Some_event = Next_Event filter it out. I am not sure how to solve for this, I assume I am using the AND operator in the wrong way.

sql-server sql
1 Answers

Given the application of the LEAD window function that you've already done. You can use a CASE statement to assign NULL values where Some_Event = Next_Event = 'Event2'.

WITH cte AS (
    SELECT *, 
           LEAD(Some_Event) OVER (
               PARTITION BY [Key] 
               ORDER     BY [id]) AS Next_Event
    FROM table1
)
SELECT [id], 
       [Key],
       [Some_Event],
       CASE WHEN NOT ([Next_Event] = 'Event2' AND [Some_Event] = 'Event2')
            THEN [Next_Event]
       END AS [Next_Event]
FROM cte

Check the demo here.

user_124928900
Related