Forum Discussion
Counting distinct values
I’m trying to create a view but I’m struggling to find a solution to this issue.
I need a method that counts multiple visits to the same location as one if they occur within 14 days of each other. If there are multiple visits to the same location and the gap between them is more than 14 days, then each should be counted separately.
For example, in the attached screenshot:
Brussels had visits on 08/05 and 15/05, which are less than 14 days apart, so this should be counted as one visit.
Dublin had visits more than a month apart, so these should be counted as two separate visits.
Could someone please guide me on how to achieve this?
Thanks.
3 Replies
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.
What if Brussels had visits on 08/05, 15/05, and 25/05. Is this considered one visit?
