选择上一个操作之后的第一个操作实例

Question

所以，这个主题非常令人困惑，因为我不知道如何用语言解释我想要什么。我正试图通过一个操作从一个表中提取数据。下面是我的数据的一个小样本。

我需要每个名字的前一个操作之后的每个OptIn和OptOut的第一个实例。我也在下面发布了我想要的结果。

我正试图在SQL 2014中做到这一点。我对此一无所知。我想也许我可以做一些滞后的事情，但我不完全明白如何使用它们，所以我很困惑自己。

样本数据：

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

期望结果：

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

Answer 1

您可以使用lag()来识别activity与"以前"记录不同的记录：

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   

DB Fiddle上的演示：

Inst|Account|First|MI|Last|Activty|ActivityDate|lag_activity---：|------：|:-----|：-----|-----------1|11111111|EDWARD|null|BICKLE|OptIn|2019-05-25 12:02:00.000|null1|11111111| EDWARD|null| BICKLE| OptOut|2019-06-20 11:57:00.000|OptIn1|1111111|EDWARD|null|BICKLE|OptIn|2019-06-27 10:34:00.000|OptOut1|1111111|EDWARD|null|BICKLE|OptOut|2019-06-27 10:35:00.000|OptIn1|1111111|KIM|null|BICKLE|OptIn|2019-07-02 11:35:42.000|OptOut1|1111111|EDWARD|null|BICKLE|OptOut|2019-07-05 15:04:00.000|OptIn1|1111111|EDWARD|null|BICKLE|OptIn|2019-07-06 07:57:00.000|OptOut0|2222222|DAWN|M|ADAMS|OptOut|2019-03-03 20:51:00.000|null0|2222232|DAWN|M|ADAMS|OptIn|2019-003-08 19:10:00.000|OptOut0|2222222|DAWN|M|ADAMS|OptOut|2019-03-18 16:21:00.000|OptIn
0|2222232|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| OptIn0|2222222|DAWN|M|ADAMS|OptIn|2019-04-01 15:39:00.000|OptOut0|2222222|DAWN|M|ADAMS|OptOut|2019-04-02 04:06:00.000|OptIn
0|2222232|DAWN|M|ADAMS|OptIn|2019-004-06 11:53:00.000|OptOut0|2222222|DAWN|M|ADAMS|OptOut|2019-04-09 12:51:00.000|OptIn
0|2222232|DAWN|M|ADAMS|OptIn|2019-04-20 15:21:00.000|OptOut