Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE #patient (PatientID INT, Name VARCHAR(50));
- INSERT INTO #patient (PatientID, Name)
- SELECT 1, 'Bob' UNION ALL
- SELECT 2, 'Scott' UNION ALL
- SELECT 3, 'Mary' UNION ALL
- SELECT 4, 'Sue';
- CREATE TABLE #tmp (ID INT IDENTITY (1,1), PatientID INT, ActualDate DATETIME);
- INSERT INTO #tmp (PatientID, ActualDate)
- SELECT 1, '2012-01-20' UNION ALL
- SELECT 1, '2012-02-20' UNION ALL
- SELECT 1, '2012-03-20' UNION ALL
- SELECT 1, '2011-04-20' UNION ALL
- SELECT 2, '2012-04-19' UNION ALL
- SELECT 2, '2012-04-20' UNION ALL
- --SELECT 3, '2012-04-19' UNION ALL
- --SELECT 3, '2011-04-20' UNION ALL
- SELECT 4, '2011-04-20';
- WITH CTE AS
- (
- SELECT #patient.PatientID, #patient.Name, MAX(ActualDate) AS LastVisit
- FROM #patient LEFT JOIN #tmp ON #patient.PatientID = #tmp.PatientID
- GROUP BY #patient.PatientID, #patient.Name
- )
- SELECT CTE.PatientID, CTE.Name, COUNT(1)-1 AS RepeatVisits
- FROM CTE LEFT JOIN #tmp ON CTE.PatientID = #tmp.PatientID
- AND #tmp.ActualDate BETWEEN DATEADD(month, -6, LastVisit) AND LastVisit
- GROUP BY CTE.PatientId, CTE.Name
- ORDER BY PatientID;
- DROP TABLE #tmp;
- DROP TABLE #patient;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement