SQL Select Records separated by an hour

I am trying to write a query that returns values with a unique person id, place id, and only the first occurrance of the visit within 60 minutes.

Here is close to what I need..

SELECT * FROM
(
SELECT  
    RANK() OVER (PARTITION BY A.PersonId, A.PlaceId,
    DATEDIFF(hh, A.Visit_Datetime, GETDA开发者_如何学JAVATE()) ORDER BY A.Visit_Datetime) AS RNK,
    A.RecordId,
    A.PersonId,
    A.PlaceId
FROM
    Table A
) A
WHERE
    A.Rnk = 1

The problem with is that if a person visits at 10:50AM and then a second record is created at 11:10 AM, both records are Ranked as 1. What I need is to rank these records by person, place, and 60 minute range.

I hope this makes sense. Thanks for the help.

Try this

SELECT * FROM 
( 
SELECT   
    RANK() OVER (PARTITION BY A.PersonId, A.PlaceId, 
    DATEDIFF(hh, 0, A.Visit_Datetime) ORDER BY A.Visit_Datetime) AS RNK, 
    A.RecordId, 
    A.PersonId, 
    A.PlaceId 
FROM 
    Table A 
) A 
WHERE 
    A.Rnk = 1 

Currently you are grouping fixed hour periods, determined by the current time. You want a moving hour period. This can't be based on the current date or a literal like 0. This implies you would need to group the hour period for all possible times. Fortunately you don't need to calculate them all. Instead group the hour period for each visit time. i.e 10:50 is grouped with all visits between 10:50 and 11:50 and 11:10 with visits between 11:10 and 12:10

FROM Table as A 
JOIN Table as B ON A.Visit_Datetime BETWEEN DATEADD(HOUR,-1,B.Visit_Datetime) 
    AND B.Visit_Datetime 

This will produce overlapping groups, similar to having multiple rank of 1. B is joined to all occurrences but you want the first occurrence. Group the occurrences together and use min to get the first one. Something like this:

SELECT DISTINCT MIN(A.Visit_Datetime) as InitVisit, B.RecordId, B.PersonId, B.PlaceID, 
FROM Table as A 
JOIN Table as B ON A.Visit_Datetime BETWEEN DATEADD(HOUR,-1,B.Visit_Datetime) AND B.Visit_Datetime 
AND B.RecordId = A.RecordId AND B.PersonId = A.PersonId AND B.PlaceID = A.PlaceId 
GROUP BY B.RecordId, B.PersonId, B.PlaceID, B.Visit_Datetime 

This should give you visit time for each Record, Person, Place. The visit period is actually of any length but each occurrence is within 60 minutes of another. This sounds like what you where trying to do.