How to find if a user rejoined within 30 days after termed?

mdhar 41 Reputation points
2020-09-18T13:04:45.64+00:00

Hi All,
I have a use case, we have list of users with a start date and end date. Here the PK of the table is user,start date, end date. Having said that, the user can have multiple records. I would like to get list of all users who are ended and started again within 30 days.
Basically trying to see, How many users rejoined within 30 days and how many times.

Any help is appreciated!

{count} votes

Accepted answer
  1. MelissaMa-MSFT 24,116 Reputation points Microsoft Employee
    2020-09-21T02:01:56.53+00:00

    Hi @MuralidharTangudu-6301,

    Please refer below:

    DROP TABLE IF EXISTS [user]  
      
    create table [user]  
    (username varchar(20),  
    startdate date,  
    enddate date  
    )  
      
    insert into [user] values   
    ('A','2020-02-03','2020-03-16'),  
    ('A','2020-03-23','2020-03-28'),  
    ('A','2020-04-03','2020-05-16'),  
    ('A','2020-08-03','2020-09-16'),  
    ('B','2020-01-01','2020-01-16'),  
    ('B','2020-04-03','2020-04-16'),  
    ('B','2020-05-03','2020-06-16'),  
    ('C','2020-03-03','2020-03-10'),  
    ('C','2020-04-03','2020-05-16'),  
    ('C','2020-05-23','2020-08-10'),  
    ('C','2020-09-23','2020-10-10')  
      
    ;WITH CTE AS (  
    SELECT  
    username  
    , StartDate,enddate  
    , LEAD(StartDate) OVER (PARTITION BY USERNAME  
    ORDER BY StartDate ASC  
    ) NextStartDate  
    FROM [user])  
      
    SELECT username,COUNT(*) COUNT FROM CTE   
    WHERE DATEDIFF(DD,enddate,NextStartDate)<=30  
    GROUP BY username  
    

    Output:

     username COUNT  
        A 2  
        B 1  
        C 2  
    

    Best regards
    Melissa


    If the answer is helpful, please click "Accept Answer" and upvote it.
    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.

    No comments

2 additional answers

Sort by: Most helpful
  1. Jayson Sainsbury 26 Reputation points
    2020-09-18T14:55:27.84+00:00

    Something like this should meet your needs.

    SELECT
    USER
    , COUNT(*)
    FROM (
    SELECT
    USER
    , StartDate
    , LEAD(StartDate) OVER (PARTITION BY USER
    ORDER BY StartDate ASC
    ) NextStartDate
    FROM <Table>
    ) AS Dates
    WHERE DATEDIFF(dd, StartDate, NextStartDate) <= 30
    GROUP BY USER

    No comments

  2. mdhar 41 Reputation points
    2020-09-18T19:51:48.403+00:00

    Thanks @Jayson Sainsbury . So in my case I need to do datedeff from first time end date to next visit's start date for the same user. will the above works for it?