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

Question

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!

Answer 1

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.

Answer 2

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

Answer 3

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?