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';

SELECT PatientID, ActualDate FROM #tmp ORDER BY PatientID, ActualDate;

WITH CTE AS
(
    SELECT #patient.PatientID, #patient.Name, MAX(ActualDate) AS LastVisit
    FROM #patient INNER 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 INNER JOIN #tmp ON CTE.PatientID = #tmp.PatientID
WHERE ActualDate BETWEEN DATEADD(month, -6, LastVisit) AND LastVisit
GROUP BY CTE.PatientId, CTE.Name;

DROP TABLE #tmp;
DROP TABLE #patient;