T-SQL get actions done between two specific actions

Question

HI,
I am trying lot to get this query right, can you please help?

Input data:

Expected output:

I am trying to get all actions taken place between actions TEST1/TEST2 (this is tracker report which identify how much time user took to do certain actions, start or end can be TEST1 or TEST2), different scenarios in the sample:

Between ID = 1 and 4 there are two actions (action1 and action2) happened (between TEST1/TEST2, basically there will be two specific actions which determine the start or end) and it should be reported with datetime of start of the activity (id = 1 in this case)
Between ID = 4 and 5 there are no actions happened so nothing to report
Between ID = 5 and 7 (next day start of the activity) there is Action1 happened so it should be reported with datetime from ID = 5
Between ID = 7 and 8 (next day start of the activity) there are no actions happened so nothing to report
Between ID = 8 and 11 there are two actions Action1/2 happened so it should be reported with datetime from ID = 8
ID = 11 is last in the data and nothing to report
Repeats for different userid's

Hope it is clear, Query as below:

DECLARE @INPUT TABLE  
(  
	ID INT,  
	USERID INT,  
	CUSTOMERID INT,  
	ACTIONATE DATETIME,  
	ACTION VARCHAR(10),  
	COMMENTS VARCHAR(100)  
)  
INSERT INTO @INPUT  
SELECT '1','1','1','2021-01-01 10:30:01','TEST1','Start' UNION ALL  
SELECT '2','1','2','2021-01-01 10:31:01','ACTION1','' UNION ALL  
SELECT '3','1','1','2021-01-01 10:31:16','ACTION2','' UNION ALL  
SELECT '4','1','3','2021-01-01 10:32:02','TEST2','End/Start' UNION ALL  
SELECT '5','1','1','2021-01-01 11:32:02','TEST1','End/Start' UNION ALL  
SELECT '6','1','3','2021-01-01 11:34:02','ACTION1','' UNION ALL  
SELECT '7','1','2','2021-01-02 10:44:02','TEST1','Start of new day' UNION ALL  
SELECT '8','1','1','2021-01-03 10:30:01','TEST1','Start of new day' UNION ALL  
SELECT '9','1','2','2021-01-03 10:31:01','ACTION1','' UNION ALL  
SELECT '10','1','1','2021-01-03 10:31:16','ACTION2','' UNION ALL  
SELECT '11','1','3','2021-01-03 10:32:02','TEST2','End/Start' UNION ALL  
SELECT '12','2','1','2021-01-01 9:30:01','TEST1','Start' UNION ALL  
SELECT '13','2','2','2021-01-01 9:31:01','ACTION1','' UNION ALL  
SELECT '14','2','1','2021-01-01 9:31:16','ACTION2','' UNION ALL  
SELECT '15','2','1','2021-01-01 9:31:16','ACTION3','' UNION ALL  
SELECT '16','2','3','2021-01-01 9:32:02','TEST2','End/Start' UNION ALL  
SELECT '17','2','1','2021-01-01 9:40:02','TEST1','End/Start' UNION ALL  
SELECT '18','2','3','2021-01-01 9:41:02','ACTION1','' UNION ALL  
SELECT '19','2','2','2021-01-02 8:44:02','TEST1','Start of new day' UNION ALL  
SELECT '20','2','1','2021-01-03 8:30:01','TEST1','Start of new day' UNION ALL  
SELECT '21','2','2','2021-01-03 8:31:01','ACTION1','' UNION ALL  
SELECT '22','2','1','2021-01-03 8:31:16','ACTION2','' UNION ALL  
SELECT '23','2','3','2021-01-03 8:32:02','TEST2','End/Start'  
  
SELECT *FROM @INPUT  
  
DECLARE @OUTPUT TABLE  
(  
	USERID INT,  
	ACTIONATE DATETIME,  
	ACTION VARCHAR(10)  
)  
INSERT INTO @OUTPUT  
SELECT '1','2021-01-01 10:30:01','ACTION1' UNION ALL  
SELECT '1','2021-01-01 10:30:01','ACTION2' UNION ALL  
SELECT '1','2021-01-01 11:32:02','ACTION1' UNION ALL  
SELECT '1','2021-01-03 10:30:01','ACTION1' UNION ALL  
SELECT '1','2021-01-03 10:30:01','ACTION2' UNION ALL  
SELECT '2','2021-01-01 9:30:01','ACTION1' UNION ALL  
SELECT '2','2021-01-01 9:30:01','ACTION2' UNION ALL  
SELECT '2','2021-01-01 9:30:01','ACTION3' UNION ALL  
SELECT '2','2021-01-01 9:40:02','ACTION1' UNION ALL  
SELECT '2','2021-01-03 8:30:01','ACTION1' UNION ALL  
SELECT '2','2021-01-03 8:30:01','ACTION2'  
  
SELECT *FROM @OUTPUT

Accepted Answer

To include the additional ActionEndDate:

select i.USERID, i.CUSTOMERID, s.ACTION_DATE, e.ACTION_DATE as ActionEndDate, i.ACTION
from @INPUT i
cross apply 
(
    select top(1) * 
    from @INPUT 
    where USERID = i.USERID and CUSTOMERID=i.CUSTOMERID 
    and cast(ACTION_DATE as date) = cast(i.ACTION_DATE as date)
    and ACTION_DATE <= i.ACTION_DATE and ACTION in ( 'ACSCEVNT', 'NEXTCNSM' )
    order by ACTION_DATE, ID desc
) s
cross apply 
(
    select top(1) * 
    from @INPUT 
    where USERID = i.USERID and CUSTOMERID=i.CUSTOMERID 
    and cast(ACTION_DATE as date) = cast(i.ACTION_DATE as date)
    and ACTION_DATE >= i.ACTION_DATE and ACTION not in ( 'ACSCEVNT', 'NEXTCNSM' )
    order by ACTION_DATE desc, ID desc
) e
where i.ACTION not in ( 'ACSCEVNT', 'NEXTCNSM' )
order by USERID, ACTION_DATE

Answer

Check a query that uses joins:

select i1.USERID, i2.ACTIONATE, i1.ACTION
from @input i1
inner join @input i2 on i2.USERID = i1.USERID and i2.ACTION = 'TEST1' 
            and i2.ACTIONATE < i1.ACTIONATE and cast(i2.ACTIONATE as date) = cast(i1.ACTIONATE as date)
left join @input i3 on i3.USERID = i1.USERID and i3.ACTION = 'TEST1' 
            and i3.ACTIONATE < i1.ACTIONATE and cast(i3.ACTIONATE as date) = cast(i1.ACTIONATE as date)
            and i3.ACTIONATE > i2.ACTIONATE
where i1.ACTION not in ( 'TEST1', 'TEST2' )
and i3.ID is null
order by USERID, ACTIONATE

Answer

Hi @Eshwar ,

Please also refer to below:

;with cte as (  
select id,userid,ACTIONATE,ACTION,  
case when ACTION='test' then (ROW_NUMBER() over (partition by userid,ACTION order by id)-1)/2+1 end groupid  
from (SELECT id,USERID,ACTIONATE,case when ACTION like 'test%' then 'test' else ACTION end ACTION FROM @INPUT) a)  
,cte1 as (  
select userid,ACTIONATE,ACTION,groupid=max(groupid) over (partition by userid order by c) from (  
select *,c= count(groupid) over (partition by userid order by id)  
from cte ) a)  
,cte2 as (  
select userid,min(ACTIONATE) over (partition by userid,groupid order by ACTIONATE) ACTIONATE,ACTION,groupid   
from cte1)  
select USERID,ACTIONATE,ACTION  
from cte2   
where ACTION<>'test'

Output:

USERID	ACTIONATE	ACTION  
1	2021-01-01 10:30:01.000	ACTION1  
1	2021-01-01 10:30:01.000	ACTION2  
1	2021-01-01 11:32:02.000	ACTION1  
1	2021-01-03 10:30:01.000	ACTION1  
1	2021-01-03 10:30:01.000	ACTION2  
2	2021-01-01 09:30:01.000	ACTION1  
2	2021-01-01 09:30:01.000	ACTION2  
2	2021-01-01 09:30:01.000	ACTION3  
2	2021-01-01 09:40:02.000	ACTION1  
2	2021-01-03 08:30:01.000	ACTION1  
2	2021-01-03 08:30:01.000	ACTION2

Best regards,
Melissa

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer

Check an adjusted query:

select i.USERID, i.CUSTOMERID, s.ACTION_DATE, i.ACTION
from @INPUT i
cross apply 
(
    select top(1) * 
    from @INPUT 
    where USERID = i.USERID and CUSTOMERID=i.CUSTOMERID 
    and cast(ACTION_DATE as date) = cast(i.ACTION_DATE as date)
    and ACTION_DATE <= i.ACTION_DATE and ACTION in ( 'ACSCEVNT', 'NEXTCNSM' )
    order by ACTION_DATE, ID desc
) s
where i.ACTION not in ( 'ACSCEVNT', 'NEXTCNSM' )
order by USERID, ACTION_DATE

Answer

Also check an alternative that uses TOP(1):

select i.USERID, s.ACTIONATE, i.ACTION
from @INPUT i
cross apply 
(
    select top(1) * 
    from @INPUT 
    where USERID = i.USERID and cast(ACTIONATE as date) = cast(i.ACTIONATE as date)
    and ACTIONATE < i.ACTIONATE and ACTION = 'TEST1' 
    order by ACTIONATE desc
) s
where i.ACTION not in ( 'TEST1', 'TEST2' )
order by USERID, ACTIONATE

T-SQL get actions done between two specific actions

4 additional answers