T-SQL get actions done between two specific actions

asked 2021-11-02T09:06:08.123+00:00
Eshwar 176 Reputation points

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

Input data:
145708-image.png

Expected output:
145784-image.png

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  
{count} votes

Accepted answer
  1. answered 2021-11-08T16:47:48.337+00:00
    Viorel 82,381 Reputation points

    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
    

4 additional answers

Sort by: Most helpful
  1. answered 2021-11-02T12:12:26.103+00:00
    Viorel 82,381 Reputation points

    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
    

  2. answered 2021-11-03T02:39:08.88+00:00
    MelissaMa-MSFT 24,116 Reputation points

    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.


  3. answered 2021-11-08T16:17:44.657+00:00
    Viorel 82,381 Reputation points

    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
    

  4. answered 2021-11-03T08:25:08.837+00:00
    Viorel 82,381 Reputation points

    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
    
    No comments