I am attempting to write SQL query that will return either the customers or just a number of students that have attended a class... and then later have returned and attended another class - within 2 years.

I am racking my head trying to figure out how to do this. I have a SQL database that stores students - and their training classes attended (and other information).

The students attend classes over time. Some leave and return to take classes at a later time. I have a student/start/end dates of each class they have attended.

How would I find out which students have attended a class and then returned within 2 years to attend another class - which the 2 years is important.

Results Wanting:

Any help would be very appreciated.

CodePudding user response：

I believe using the LAG function to get the End date of the last class attended will work. Just compare that to the current start date and see if they are 2 years or less apart.

CREATE TABLE #tmp(STUDENTID varchar(10), CLASS varchar(20), STARTDT date, ENDDT date)
INSERT INTO #tmp VALUES
('Joe', 'English',  '11/01/2016','12/16/2016'),
('Joe', 'AdvEnglish',   '03/01/2020','07/21/2020'),
('Pete',    'Math', '11/01/2019','11/15/2019'),
('Pete',    'Writing',  '02/03/2020','03/03/2020'),
('Randy',   'BasicLit', '05/23/2018','07/21/2018'),
('Randy',   'Physics',  '09/11/2018','10/12/2018'),
('Randy',   'BasicGeo', '01/05/2019','02/10/2019');

WITH CTE AS
(
    SELECT *, LAG(ENDDT,1) OVER(PARTITION BY STUDENTID ORDER BY STARTDT) LastEnd
    FROM #tmp
) 
SELECT STUDENTID, COUNT(*) NumberOfTimesReturned
FROM CTE 
WHERE DATEDIFF(YEAR,LastEnd,STARTDT) <= 2
GROUP BY STUDENTID

CodePudding user response：

Slightly different:

SELECT Visit1.STUDENTID, NumberOfTimesReturned=COUNT(distinct Visit2.StartDt) 
FROM #tmp Visit1
     inner join
     #tmp Visit2
     on Visit1.studentid=Visit2.studentId
     and Visit2.StartDt > Visit1.EndDt   -- Must be a visit in the future
     and DateDiff(YEAR,Visit1.EndDt, Visit2.startDt) <=2     -- must start within 2 years of first visit ending
GROUP BY Visit1.STUDENTID