A query to find patients with any 4 visits within a 365 day period I need to find our high utilizers. I have this query:

with Visitlist as(
select v.PatientID, a.VisitID,  a.AdmitDateTime,
rank() over (partition by PatientID order by AdmitDateTime desc) as VisitNumber
from AdmittingData a
inner join MriPatientVisitEvents v on a.VisitID = v.VisitID
where 
a.AdmitDateTime &gt;= '2016'
and v.PatientID is not null)

select distinct v1.PatientID, 
v1.AdmitDateTime, v1.VisitNumber, v2.AdmitDateTime, v2.VisitNumber, 
v3.AdmitDateTime, v3.VisitNumber, v4.AdmitDateTime, v4.VisitNumber
from Visitlist v1
inner join Visitlist v2 with (nolock) on v1.VisitNumber = v2.VisitNumber - 1 and v1.PatientID = v2.PatientID
inner join Visitlist v3 with (nolock) on v1.VisitNumber = v3.VisitNumber - 2 and v1.PatientID = v3.PatientID
inner join Visitlist v4 with (nolock) on v1.VisitNumber = v4.VisitNumber - 3 and v1.PatientID = v4.PatientID
where 
v1.VisitNumber = 1 
and datediff(day, v4.AdmitDateTime, v1.AdmitDateTime) &lt;= 365 
and v1.AdmitDateTime &gt;= '2017'


The Problem: This query will only return patients whose last 4 visits happened within 1 year.

Question: How would I find all the patients with any 4 visits that happened within 1 year of each other?

Credit to Jonathan Fite, I used his suggestion to rewrite the query like so:

with VisitEvents as(select distinct PatientID, VisitID from MriPatientVisitEvents),

VisitList2 as (
select v.PatientID, a.VisitID, a.AdmitDateTime, rank() over (partition by v.PatientID order by a.AdmitDateTime desc) as VisitNumber
from AdmittingData a
inner join VisitEvents v on a.VisitID = v.VisitID 
where a.AdmitDateTime is not null),

VisitList as(
select v.*, 
lag(AdmitDateTime,1) over (partition by PatientID order by AdmitDateTime) as Delta1AdmitDateTime,
lag(VisitNumber,1) over (partition by PatientID order by AdmitDateTime) as D1VisitNum,
lag(AdmitDateTime,2) over (partition by PatientID order by AdmitDateTime) as Delta2AdmitDateTime,
lag(VisitNumber,2) over (partition by PatientID order by AdmitDateTime) as D2VisitNum,
lag(AdmitDateTime,3) over (partition by PatientID order by AdmitDateTime) as Delta3AdmitDateTime,
lag(VisitNumber,3) over (partition by PatientID order by AdmitDateTime) as D3VisitNum,
lag(AdmitDateTime,4) over (partition by PatientID order by AdmitDateTime) as Delta4AdmitDateTime,
lag(VisitNumber,4) over (partition by PatientID order by AdmitDateTime) as D4VisitNum  

from VisitList2 v
)

select * from VisitList
where datediff(day,Delta3AdmitDateTime, AdmitDateTime) &lt;= 365
and AdmitDateTime &gt;= '2017'
order by PatientID, VisitNumber
Here is a possibility for you to look at.  It uses the Lag function with the offset option set.  It doesn't show what all the visits were or when they occurred, but it does only show the most recent visit date for each patient and then when the fourth prior visit occurred within the year.

Test Data

IF OBJECT_ID('tempdb..#TestData') IS NOT NULL
BEGIN
    DROP TABLE #TestData;
END

CREATE TABLE #TestData
    (
    TestDataID INT IDENTITY(1,1) PRIMARY KEY NOT NULL
    , PatientName VARCHAR(40) NOT NULL
    , VisitDate DATETIME NOT NULL
    );

INSERT INTO #TestData
(PatientName, VisitDate)
VALUES ('Smith', '11/30/2017')
    , ('Smith', '1/1/2018')
    , ('Smith', '12/5/2017')
    , ('Smith', '2/1/2015')
    , ('Smith', '9/5/2017')
    , ('Doe', '9/8/2017')
    , ('Smith', '9/12/2017');


Query

/** CTE_Visitor
    Limits data to only where all visits happened in 2017
    Determines if most recent (MostRecentDesc) will be 1 for most recent.
    Determines the fourth prior visit ID by using LAG with an offset.
    **/
;WITH CTE_Visitor AS
    (
    SELECT S.TestDataID
        , S.PatientName
        , S.VisitDate
        , FourVisitsAgo = LAG(S.TestDataID, 3) OVER (PARTITION BY S.PatientName ORDER BY S.VisitDate)
        , MostRecentDesc = ROW_NUMBER() OVER (PARTITION BY S.PatientName ORDER BY S.VisitDate DESC)
    FROM #TestData AS S
    WHERE YEAR(S.VisitDate) &gt;= 2017
    )
/** Joins test data back on to the FourVistsAgo to get the date.
    - Only displays for the most recent visit and the fourth prior visit.
    - Must have had at least four visits in the past
    - Fourth prior visit must be within a year of most recent visit
    **/
SELECT S.TestDataID
    , S.PatientName
    , S.VisitDate 
    , Prev.VisitDate AS DateFourVisitsAgo
    , DiffDays = DATEDIFF(DAY, Prev.VisitDate, S.VisitDate)
FROM CTE_Visitor AS S
    INNER JOIN #TestData AS Prev ON Prev.TestDataID = S.FourVisitsAgo
WHERE MostRecentDesc = 1
    AND FourVisitsAgo IS NOT NULL
    AND DATEDIFF(DAY, Prev.VisitDate, S.VisitDate) &lt;= 365;