SQL to get all other actions between two actions

Question

SQL to get all other actions between two actions

Eshwar 216

HI,
I need help in getting query for below requirement.

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

Input:

Output:

Data Queries:
149643-queries.txt

Appreciate your inputs!

Thanks,
Eshwar

Accepted answer

1 additional answer

Your answer

Answer 1

Viorel 122.6K

Check the next query:

select * from @OUTPUT order by USERID, CUSTOMERID, ACTION_DATE

;
with I as
(
    select *, d = cast(ACTION_DATE as date)
    from @INPUT
), 
T as
(
    select *,
        s = (select top(1) ACTION_DATE from I 
                where USERID = t.USERID and CUSTOMERID = t.CUSTOMERID 
                and [ACTION] in ('ACSCEVNT', 'NEXTCNSM') and d = t.d and ACTION_DATE < t.ACTION_DATE
                order by ACTION_DATE desc),
        e = (select top(1) ACTION_DATE from I 
                where USERID = t.USERID 
                and [ACTION] in ('ACSCEVNT', 'NEXTCNSM') and d = t.d and ACTION_DATE > t.ACTION_DATE
                order by ACTION_DATE asc)
    from I t
)
select USERID, CUSTOMERID, 
    ACTION_DATE = ISNULL(s, ACTION_DATE),
    ACTION_END_DATE = case when s is null or e is null then '' else e end,
    [ACTION]
from T
where [ACTION] not in ('ACSCEVNT', 'NEXTCNSM')
order by USERID, CUSTOMERID, ACTION_DATE

For convenience, the outputs are ordered.

Eshwar 216 Reputation points

2021-11-16T19:26:57.247+00:00

Awesome man.
I have written it using ranking but this is more efficient way, better than mine!
Thanks again!
Eshwar 216 Reputation points

2021-11-17T11:49:52.34+00:00

HI @Viorel
It is working for the scenario I gave but some how it is not working for this scenario:

Queries:
150189-queries-new.txt

Can you please check?

Thanks,
Eshwar

Viorel 122.6K

Check if the next query guesses the rules more successful:

;
with I as
(
    select *, d = cast(ACTION_DATE as date)
    from @INPUT
), 
T as
(
    select *,
        ms = (select top(1) ACTION_DATE from I 
                where USERID = t.USERID and CUSTOMERID = t.CUSTOMERID 
                and [ACTION] in ('ACSCEVNT', 'NEXTCNSM') and d = t.d and ACTION_DATE < t.ACTION_DATE
                order by ACTION_DATE desc),
        os = (select top(1) ACTION_DATE from I 
                where USERID = t.USERID and CUSTOMERID <> t.CUSTOMERID 
                and [ACTION] in ('ACSCEVNT', 'NEXTCNSM') and d = t.d and ACTION_DATE < t.ACTION_DATE
                order by ACTION_DATE desc),
        e = (select top(1) ACTION_DATE from I 
                where USERID = t.USERID
                and [ACTION] in ('ACSCEVNT', 'NEXTCNSM') and d = t.d and ACTION_DATE > t.ACTION_DATE
                order by ACTION_DATE asc)
    from I t
)
select USERID, CUSTOMERID, 
    ACTION_DATE = isnull(case when os < ms then ms end, ACTION_DATE),
    ACTION_END_DATE = isnull(case when ms is null then '' when ms < os then '' else e end, ''),
    [ACTION]
from T
where [ACTION] not in ('ACSCEVNT', 'NEXTCNSM')
order by USERID, CUSTOMERID, ACTION_DATE

Eshwar 216 Reputation points

2021-11-18T08:02:06.103+00:00
The first scenario's are not working:

first input, new output and old output.. it should be old output.

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

Thanks,
Ram.
Viorel 122.6K Reputation points

2021-11-18T08:44:34.697+00:00
Maybe this modification will work:

. . . ACTION_DATE = isnull(case when os is null or os < ms then ms end, ACTION_DATE), . . .

Or use the part that worked in previous versions.
Eshwar 216 Reputation points

2021-11-18T09:59:10.683+00:00

Thanks it is coming correctly for old but small issue with new:

It should be next start of action.
Viorel 122.6K Reputation points

2021-11-18T12:22:53.42+00:00

After the last modification, the query seems to give the output which is shown as "Expected output" in one of the previous pictures for new data.

Answer 2

Hi @Eshwar ,

Could you please describe the rules in detail?Please first check if the following code is helpful for you:

    DECLARE @INPUT TABLE  
    (  
     ID INT,  
     USERID VARCHAR(128),  
     CUSTOMERID INT,  
     ACTION_DATE DATETIME,  
     ACTION VARCHAR(10)  
    )  
    INSERT INTO @INPUT  
    SELECT '1','User1','13704','2021-11-08 07:49:49.600','ACSCEVNT' UNION ALL  
    SELECT '2','User1','13704','2021-11-08 07:50:52.397','TAGACCT' UNION ALL  
    SELECT '3','User1','13704','2021-11-08 07:50:52.400','COMMENT' UNION ALL  
    SELECT '4','User1','13705','2021-11-08 07:51:03.090','ACSCEVNT' UNION ALL  
    SELECT '5','User1','13706','2021-11-08 08:50:52.400','ACSCEVNT' UNION ALL  
    SELECT '6','User1','13706','2021-11-08 08:51:49.857','ATTYCLL' UNION ALL  
    SELECT '7','User1','13706','2021-11-08 08:52:49.858','COMMENT' UNION ALL  
    SELECT '8','User1','13706','2021-11-08 12:50:52.400','ACSCEVNT' UNION ALL  
    SELECT '9','User1','13706','2021-11-08 12:51:49.857','ATTYCLL' UNION ALL  
    SELECT '10','User1','13706','2021-11-08 12:52:49.858','COMMENT' UNION ALL  
    SELECT '11','User1','13707','2021-11-08 13:05:49.858','ACSCEVNT' UNION ALL  
    SELECT '12','User1','13707','2021-11-08 13:15:49.858','ATTYCLL' UNION ALL  
    SELECT '13','User1','13707','2021-11-08 13:20:49.858','COMMENT' UNION ALL  
    SELECT '14','User1','13707','2021-11-08 14:05:49.858','ACSCEVNT' UNION ALL  
    SELECT '15','User1','13707','2021-11-08 14:15:49.858','ATTYCLL' UNION ALL  
    SELECT '16','User1','13707','2021-11-09 07:05:49.858','ACSCEVNT' UNION ALL  
    SELECT '17','User1','13707','2021-11-09 07:15:49.858','COMMENT' UNION ALL  
    SELECT '18','User1','13707','2021-11-09 07:20:49.858','CHNGCR' UNION ALL  
    SELECT '19','User1','13708','2021-11-09 07:25:49.858','ACSCEVNT' UNION ALL  
    SELECT '20','User1','13708','2021-11-09 07:35:49.858','COMMENT' UNION ALL  
    SELECT '21','User1','13708','2021-11-09 07:40:49.858','CHNGCR' UNION ALL  
    SELECT '22','User1','13709','2021-11-09 09:25:49.858','ACSCEVNT' UNION ALL  
    SELECT '23','User1','13709','2021-11-09 11:25:49.858','COMMENT' UNION ALL  
    SELECT '24','User1','13709','2021-11-09 12:25:49.858','CHNGCR' UNION ALL  
    SELECT '25','User1','13709','2021-11-09 18:25:49.858','MWADJTX' UNION ALL  
    SELECT '26','User1','13710','2021-11-10 08:25:49.858','ACSCEVNT' UNION ALL  
    SELECT '27','User1','13136','2021-11-10 08:30:49.858','USCHDPAY' UNION ALL  
    SELECT '28','User1','13136','2021-11-10 08:35:49.858','USCHDPAY' UNION ALL  
    SELECT '29','User1','13136','2021-11-10 08:38:49.858','USCHDPAY' UNION ALL  
    SELECT '30','User1','13136','2021-11-10 08:40:49.858','MWADJTX' UNION ALL  
    SELECT '31','User1','13136','2021-11-10 08:45:49.858','CNSMRPMT' UNION ALL  
    SELECT '32','User1','13136','2021-11-10 08:50:49.858','MSCHDPAY' UNION ALL  
    SELECT '33','User1','13710','2021-11-10 08:52:49.858','COMMENT' UNION ALL  
    SELECT '34','User1','13710','2021-11-10 08:54:49.858','MWADJTX' UNION ALL  
    SELECT '35','User1','13136','2021-11-10 08:55:49.858','USCHDPAY' UNION ALL  
    SELECT '36','User1','13137','2021-11-10 08:56:49.858','USCHDPAY' UNION ALL  
    SELECT '37','User1','13137','2021-11-10 08:57:49.858','USCHDPAY' UNION ALL  
    SELECT '38','User1','13136','2021-11-10 08:58:49.858','USCHDPAY' UNION ALL  
    SELECT '39','User1','13711','2021-11-10 08:59:49.297','ACSCEVNT' UNION ALL  
    SELECT '40','User1','13711','2021-11-10 09:00:49.297','COMMENT'  
      
      
    ;WITH cte  
    as(SELECT *,ROW_NUMBER() OVER(ORDER BY ID )rr FROM @INPUT  
    WHERE ACTION='ACSCEVNT' OR ACTION='NEXTCNSM'  
    UNION ALL   
    SELECT *,NULL FROM @INPUT  
    WHERE ACTION NOT IN('ACSCEVNT','NEXTCNSM'))  
    ,cte2 as  
    (SELECT c1.*,ISNULL(c2.ACTION_DATE,c1.rr) ACTION_DATE2 FROM cte c1   
    LEFT JOIN cte c2 ON c1.rr=c2.rr-1   
    AND CAST(c1.ACTION_DATE AS DATE)=CAST(c2.ACTION_DATE AS DATE))  
      
    --SELECT * FROM cte2  
      
    ,cte3 as(SELECT DISTINCT cte2.*,t.ACTION AS ACTION2  
    FROM cte2  
    CROSS APPLY (SELECT  ACTION FROM cte2 c WHERE cte2.CUSTOMERID=c.CUSTOMERID   
    AND c.ACTION_DATE2 IS NULL ) t  
    WHERE ACTION_DATE2 IS NOT NULL)  
      
    --SELECT * FROM cte3  
      
    SELECT *,CASE WHEN ACTION_DATE2 LIKE '%1900%' THEN NULL  
    ELSE ACTION_DATE2 END ACTION_DATE2  
    FROM cte3  
    ORDER BY ID

Echo

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.

Share via

SQL to get all other actions between two actions

1 additional answer

Your answer