CODEWITHSUNDEEP
sqlsql-servert-sqlwindow-functionsgaps-and-islands
Rhonda
Rhonda

Reputation: 21

Select the first instance of an action after the previous action

So, the subject is super confusing, because I just don't know how to explain with words what I want. I am trying to pull data from a single table with an action. Below is a small sample of what my data would look like.

I need the first instance of each OptIn and OptOut after the previous action for each name. I have posted my desired results below also.

I'm trying to do this in SQL 2014. I am pulling a blank on this. I thought maybe I could something with a lag and over but I don't fully understand how to use them, so I am confusing myself.

Sample Data:

Inst,Acct,First,MI,Last,Activty,ActivtyDate

001,00000000001111111,EDWARD,,BICKLE,OptIn,2019-05-25 12:02:00.000
001,00000000001111111,EDWARD,,BICKLE,OptIn,2019-05-25 12:37:00.000
001,00000000001111111,KIM,,BICKLE,OptIn,2019-06-17 08:43:00.000
001,00000000001111111,EDWARD,,BICKLE,OptOut,2019-06-20 11:57:00.000
001,00000000001111111,EDWARD,,BICKLE,OptOut,2019-06-20 23:10:00.000
001,00000000001111111,EDWARD,,BICKLE,OptOut,2019-06-21 08:46:00.000
001,00000000001111111,EDWARD,,BICKLE,OptIn,2019-06-27 10:34:00.000
001,00000000001111111,EDWARD,,BICKLE,OptOut,2019-06-27 10:35:00.000
001,00000000001111111,EDWARD,,BICKLE,OptOut,2019-06-27 23:04:00.000
001,00000000001111111,KIM,,BICKLE,OptIn,2019-07-02 11:35:42.000
001,00000000001111111,EDWARD,,BICKLE,OptOut,2019-07-05 15:04:00.000
001,00000000001111111,EDWARD,,BICKLE,OptIn,2019-07-06 07:57:00.000
001,00000000001111111,EDWARD,,BICKLE,OptIn,2019-07-06 08:16:00.000
001,00000000001111111,EDWARD,,BICKLE,OptIn,2019-07-09 08:17:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-03-03 20:51:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-03-03 20:51:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-03-08 19:10:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-03-16 06:34:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-03-18 16:21:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-03-21 16:38:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-03-22 05:26:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-04-01 15:39:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-04-02 04:06:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-04-06 11:53:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-04-09 12:51:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-04-20 15:21:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-04-20 15:21:00.000

Desired Result:

Inst,Acct,First,MI,Last,Activty,ActivtyDate

001,00000000001111111,EDWARD,,BICKLE,OptIn,2019-05-25 12:02:00.000
001,00000000001111111,EDWARD,,BICKLE,OptOut,2019-06-20 11:57:00.000
001,00000000001111111,EDWARD,,BICKLE,OptIn,2019-06-27 10:34:00.000
001,00000000001111111,EDWARD,,BICKLE,OptOut,2019-06-27 10:35:00.000
001,00000000001111111,KIM,,BICKLE,OptIn,2019-07-02 11:35:42.000
001,00000000001111111,EDWARD,,BICKLE,OptOut,2019-07-05 15:04:00.000
001,00000000001111111,EDWARD,,BICKLE,OptIn,2019-07-06 07:57:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-03-03 20:51:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-03-08 19:10:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-03-18 16:21:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-03-21 16:38:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-03-22 05:26:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-04-01 15:39:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-04-02 04:06:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-04-06 11:53:00.000
000,00000000002222222,DAWN,M,ADAMS,OptOut,2019-04-09 12:51:00.000
000,00000000002222222,DAWN,M,ADAMS,OptIn,2019-04-20 15:21:00.000

Upvotes: 0

Views: 79

Answers (1)

GMB
GMB

Reputation: 222512

You can use lag() to identify records whose activity is different than the "previous" record:

select t.*
from (
    select 
        t.*,
        lag(activity) over(partition by inst, acct order by activityDate) lag_activity
    from mytable t
) t
where lag_activity is null or activity <> lag_activity
order by inst, acct, activityDate

Demo on DB Fiddle:

Inst |    Acct | First  | MI   | Last   | Activty | ActivityDate            | lag_activity
---: | ------: | :----- | :--- | :----- | :------ | :---------------------- | :-----------
   1 | 1111111 | EDWARD | null | BICKLE | OptIn   | 2019-05-25 12:02:00.000 | null
   1 | 1111111 | EDWARD | null | BICKLE | OptOut  | 2019-06-20 11:57:00.000 | OptIn
   1 | 1111111 | EDWARD | null | BICKLE | OptIn   | 2019-06-27 10:34:00.000 | OptOut
   1 | 1111111 | EDWARD | null | BICKLE | OptOut  | 2019-06-27 10:35:00.000 | OptIn
   1 | 1111111 | KIM    | null | BICKLE | OptIn   | 2019-07-02 11:35:42.000 | OptOut
   1 | 1111111 | EDWARD | null | BICKLE | OptOut  | 2019-07-05 15:04:00.000 | OptIn
   1 | 1111111 | EDWARD | null | BICKLE | OptIn   | 2019-07-06 07:57:00.000 | OptOut
   0 | 2222222 | DAWN   | M    | ADAMS  | OptOut  | 2019-03-03 20:51:00.000 | null
   0 | 2222222 | DAWN   | M    | ADAMS  | OptIn   | 2019-03-08 19:10:00.000 | OptOut
   0 | 2222222 | DAWN   | M    | ADAMS  | OptOut  | 2019-03-18 16:21:00.000 | OptIn

   0 | 2222222 | DAWN   | M    | ADAMS  | OptIn   | 2019-03-21 16:38:00.000 | OptOut

   0 | 2222222 | DAWN   | M    | ADAMS  | OptOut  | 2019-03-22 05:26:00.000 | OptIn
   0 | 2222222 | DAWN   | M    | ADAMS  | OptIn   | 2019-04-01 15:39:00.000 | OptOut
   0 | 2222222 | DAWN   | M    | ADAMS  | OptOut  | 2019-04-02 04:06:00.000 | OptIn

   0 | 2222222 | DAWN   | M    | ADAMS  | OptIn   | 2019-04-06 11:53:00.000 | OptOut
   0 | 2222222 | DAWN   | M    | ADAMS  | OptOut  | 2019-04-09 12:51:00.000 | OptIn

   0 | 2222222 | DAWN   | M    | ADAMS  | OptIn   | 2019-04-20 15:21:00.000 | OptOut

Upvotes: 1

Related Questions