T-SQL get actions done between two specific actions

Question

T-SQL get actions done between two specific actions

Eshwar 176

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

MelissaMa-MSFT 24,131 Reputation points

2021-11-04T01:51:07.693+00:00

Hi @Eshwar ,

Could you please validate all the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa
Eshwar 176 Reputation points

2021-11-08T14:35:59.257+00:00

@Viorel @MelissaMa-MSFT

Some real data added customerid and actionenddate(last record in that flow) in the output:
Input:

Expected Output:

Query:
147491-queries.txt

Hope it make sense!
Thanks a lot for your help!

MelissaMa-MSFT 24,131 Reputation points

2021-11-04T01:51:07.693+00:00

Hi @Eshwar ,

Could you please validate all the answers and provide any update?

Please remember to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.

Thank you for understanding!

Best regards,
Melissa
Eshwar 176 Reputation points

2021-11-08T14:35:59.257+00:00

@Viorel @MelissaMa-MSFT

Some real data added customerid and actionenddate(last record in that flow) in the output:
Input:

Expected Output:

Query:
147491-queries.txt

Hope it make sense!
Thanks a lot for your help!

Answer 1

Viorel 88,571

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

Eshwar 176 Reputation points

2021-11-09T04:57:25.253+00:00

@Viorel
One last scenario.. looking on converting datetime to date will not work as there will scenario when user1 worked on a customer then he went to a new customer and came back to old customer then those should be considered as separate records for that customer

Input:

Expected Output:

Query:
147622-queries.txt

Please help!

Thanks,
Eshwar
Eshwar 176 Reputation points

2021-11-09T09:06:33.51+00:00

@Viorel
Can you help here... it basically should consider it as start of new record if it is one of the Action's and not same customer and end if the customer changes (don't include those two action records)?

@MelissaMa-MSFT ?

Thanks,
Eshwar
Viorel 88,571 Reputation points

2021-11-09T10:47:38.427+00:00

It seems that the sample script (147622-queries.txt) cannot be opened.
Eshwar 176 Reputation points

2021-11-09T12:21:17.477+00:00

@Viorel
Attaching
147719-queries.txt

Viorel 88,571

Try the adjusted query:

;
with Q as
(
    select *,
        g = dense_rank() over (partition by USERID, CUSTOMERID order by ID) -
                dense_rank() over (partition by USERID order by ID)
    from @INPUT 
)
select i.USERID, i.CUSTOMERID, 
    ActionDate = s.ACTION_DATE,
    ActionEndDate = e.ACTION_DATE,
    i.ACTION
from Q i
cross apply 
(
    select top(1) * 
    from Q
    where USERID = i.USERID and CUSTOMERID=i.CUSTOMERID and g=i.g
    and ID < i.ID and ACTION in ( 'ACSCEVNT', 'NEXTCNSM' )
    order by ID 
) s
cross apply 
(
    select top(1) * 
    from Q
    where USERID = i.USERID and CUSTOMERID=i.CUSTOMERID and g=i.g
    and ID >= i.ID and ACTION not in ( 'ACSCEVNT', 'NEXTCNSM' )
    order by ID desc
) e
where i.ACTION not in ( 'ACSCEVNT', 'NEXTCNSM' )
order by i.ID

Eshwar 176 Reputation points

2021-11-13T09:15:41.107+00:00
@Viorel
Sorry there are some changes in the requirements again!!
Can you please help?

Input:

Expected output:

Requirement:

Start/End will be with ACSCEVNT/NEXTCNSM

Start date is datetime of that ACSCEVNT/NEXTCNSM

End date is datetime of next ACSCEVNT/NEXTCNSM record

Display all records in between start and end

If it is end of the day (last events) the end date will be blank for those last record(s)

Additional display all records where start is not ACSCEVNT/NEXTCNSM with its corresponding start date and end date is blank

It should be evaluated for each user

Query:
149879-queries.txt
Eshwar 176 Reputation points

2021-11-15T06:53:04.65+00:00

@Viorel
Can you pls help here?
Regards,
Eshwar.
Viorel 88,571 Reputation points

2021-11-16T17:44:52.76+00:00

What do you mean by "These are additional events for 13137"?
Eshwar 176 Reputation points

2021-11-16T18:10:47.76+00:00

Thanks a lot @Viorel

So for that customer ID flow there is no start event (ACSCEVNT/NEXTCNSM) so those are orphans and it should be reported with start date as date in that record and end date as null.
FYI.. I have updated sample.

Regards,
Eshwar.

Answer 2

Viorel 88,571

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

Eshwar 176 Reputation points

2021-11-08T11:18:55.31+00:00

HI @Viorel ,
Apologies for late response
Solution doesn't work if I replace TEST1 with TEST2 and TEST2 with TEST1 in the input data.
Basically the start pointing can be TEST1 or TEST2 and I need to consider it as new record when TEST1/TEST2 comes.
Also it need not be TEST1, TEST2 my actual values are ACSCEVNT and NEXTCNSM
Thanks,
Eshwar
Viorel 88,571 Reputation points

2021-11-08T12:01:10.997+00:00

Did you try to adjust the queries according to your real details? For example, you can use ACTION in ('ACSCEVNT', 'NEXTCNSM') instead of ACTION = 'TEST1'. If it does not work, then maybe give a new example of your current data.
Eshwar 176 Reputation points

2021-11-08T12:21:09.967+00:00

Thanks @Viorel ,

Yes I have tried as mentioned it need not be TEST1 as start/end it can TEST2 also based on above data. I tried by replacing TEST1 with TEST2 and TEST2 with TEST1 in the input data and it didn't gave expected results.
Also I need to group on CUSTOMERID as in it should be considered as new/end of the record if action is ACSCEVNT/NEXTCNSM or CUSTOMERID is changed from previous record

Regards,
Eshwar

Answer 3

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.

Eshwar 176 Reputation points

2021-11-08T11:22:57.767+00:00

Hi @MelissaMa-MSFT

Apologies for late response

Thanks, solution works even if I replace TEST1 with TEST2 and TEST2 with TEST1 in the input data.
Also my actual values are ACSCEVNT and NEXTCNSM, how can I replace them in the query and I need to group on CUSTOMERID as in it should be considered as new/end of the record if action is ACSCEVNT/NEXTCNSM or CUSTOMERID is changed from previous record

Appreciate your inputs!

Thanks,
Eshwar

Answer 4

Viorel 88,571

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

Eshwar 176 Reputation points

2021-11-08T16:33:37.887+00:00

Thanks @Viorel a lot it works as expected.

Can we add ACTION_END_DATE also?
It is the last date in the flow!

Answer 5

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

Activity