Counting distinct values

This is a gaps-and-islands problem. Use LAG() to compare each visit with the previous visit for the same location, mark a new visit group when the gap is more than 14 days, then count the groups.

Example pattern:

WITH ordered AS (

SELECT

Location,

VisitDate,

CASE

WHEN LAG(VisitDate) OVER (PARTITION BY Location ORDER BY VisitDate) IS NULL

OR DATEDIFF(day,

LAG(VisitDate) OVER (PARTITION BY Location ORDER BY VisitDate),

VisitDate) > 14

THEN 1 ELSE 0

END AS NewVisitGroup

FROM dbo.YourTable

),

grouped AS (

SELECT

Location,

VisitDate,

SUM(NewVisitGroup) OVER (

PARTITION BY Location

ORDER BY VisitDate

ROWS UNBOUNDED PRECEDING

) AS VisitGroupId

FROM ordered

)

SELECT

Location,

COUNT(DISTINCT VisitGroupId) AS DistinctVisitCount

FROM grouped

GROUP BY Location;

If multiple rows can have the same date/time, add a stable tie-breaker column to the ORDER BY.