T-SQL get actions done between two specific actions

Question

question

Eshwar-8614 asked Nov 2, '21 Viorel-1 edited Nov 16, '21

T-SQL get actions done between two specific actions

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

sql-server-transact-sql

image.png (44.5 KiB)

image.png (13.8 KiB)

image.png (14.4 KiB)

· 2

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

MelissaMa-msft · Nov 04, 2021 at 01:51 AM

Hi @Eshwar-8614,

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

0 ·

Eshwar-8614 · Nov 08, 2021 at 02:35 PM

@Viorel-1 @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!

0 ·

input.jpg (189.4 KiB)

output.jpg (129.4 KiB)

queries.txt (3.9 KiB)

Answer 1 · 2021-11-08T16:47:48.337Z

Viorel-1 answered Nov 8, '21 Viorel-1 edited Nov 16, '21

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

· 9

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Eshwar-8614 · Nov 09, 2021 at 04:57 AM

@Viorel-1
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

0 ·

input.jpg (192.5 KiB)

output.jpg (121.3 KiB)

Eshwar-8614 · Nov 09, 2021 at 09:06 AM

@Viorel-1
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

0 ·

Viorel-1 Eshwar-8614 · Nov 09, 2021 at 10:47 AM

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

0 ·

Eshwar-8614 Viorel-1 · Nov 09, 2021 at 12:21 PM

@Viorel-1
Attaching
147719-queries.txt

0 ·

queries.txt (4.8 KiB)

Show more comments

Answer 2 · 2021-11-02T12:12:26.103Z

Viorel-1 answered Nov 2, '21 Eshwar-8614 edited Nov 8, '21

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

· 3

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Eshwar-8614 · Nov 08, 2021 at 11:18 AM

HI @Viorel-1 ,
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

0 ·

Viorel-1 Eshwar-8614 · Nov 08, 2021 at 12:01 PM

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.

0 ·

Eshwar-8614 Viorel-1 · Nov 08, 2021 at 12:21 PM

Thanks @Viorel-1,

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

0 ·

Answer 3 · 2021-11-03T02:39:08.88Z

MelissaMa-msft answered Nov 3, '21 Eshwar-8614 commented Nov 8, '21

Hi @Eshwar-8614,

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.

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Eshwar-8614 · Nov 08, 2021 at 11:22 AM

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

0 ·

Answer 4 · 2021-11-03T08:25:08.837Z

Viorel-1 answered Nov 3, '21

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

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Answer 5 · 2021-11-08T16:17:44.657Z

Viorel-1 answered Nov 8, '21 Eshwar-8614 commented Nov 8, '21

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

· 1

5 |1600 characters needed characters left characters exceeded

Attachments: Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Eshwar-8614 · Nov 08, 2021 at 04:33 PM

Thanks @Viorel-1 a lot it works as expected.

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

0 ·

image.png (40.5 KiB)

question