SQL group by date difference

Question

i am getting stuck with writing below sql query. I have a table as:

id | C1 | C2 | date

1  | 1  | 1  | 1/10/2010 9:30:10
2  | 1  | 1  | 1/10/2010 9:31:10
3  | 1  | 1  | 1/10/2010 9:32:10
4  | 1  | 1  | 1/10/2010 9:37:10
5  | 1  | 2  | 1/10/2010 9:38:10
6  | 2  | 3  | 1/10/2010 9:39:10
7  | 2  | 3  | 1/10/2010 9:45:10
8  | 2  | 3  | 1/10/2010 9:46:10

Now i want to fetch the records like :
group records by date difference(latest date) of 5 minutes and same C1 and same C2
(i.e. records like latset date within 5 minutes of each C2 of same C1).
All i want the output as:

id | C1 | C2 | date

3  | 1  | 1  | 1/10/2010 9:32:10
4  | 1  | 1  | 1/10/2010 9:37:10
5  | 1  | 2  | 1/10/2010 9:38:10
6  | 2  | 3  | 1/10/2010 9:39:10
7  | 2  | 3  | 1/10/2010 9:46:10

explanation:
1. first 3 records are grouped (same C1 & same C2 within 5 minutes) and 3rd record taken (latest of these 3).
2. 4rth record fetched. (it was the only record within 5 mins backward & 5 mins forward of same C1 and same C2).
3. same as above
likewise up to ... 8th record.
I tried various group by and partition by queries, but got stucked. Any help would be appreciated. Thank you

Answer 1

There might be an easier solution, but I used a common table expression, checktimesagainsttimes, to check forward and backward one record joined against the same table. If my data was in a table called test3, this would look at the data as table m2, join it to itself for the record before it as table m1 and the record after it as table m3.

It would compare the dates in m1, m2, and m3 and return a table checktimesagainsttimes that has a column WithinFiveMinutes with 1 if it is within five minutes of the previous or next record.

Then I use another common table expression to query checktimesagainsttimes, twice. The first time I get the maximum date for groups where they are within five minutes. I then union the results to the results from checktimesagainsttimes that are not within five minutes of each other. These results are returned to maxdatesforwithin5minutes.

The final thing to do is take these results and join back to my original table based on the c1, c2, and the date to pull back the actual rowed.

WITH    checktimesagainsttimes
          AS ( SELECT   m2.id AS id2 ,
                        m1.id AS id1 ,
                        m3.id AS id3 ,
                        m2.date ,
                        m2.c1 ,
                        m2.c2 ,
                        CASE WHEN ( DATEADD(mi, 5, m2.date) > m3.date
                                    AND m2.date < m3.date
                                  )
                                  OR ( DATEADD(mi, -5, m2.date) < m1.date
                                       AND m2.date > m1.date
                                     ) THEN 1
                             ELSE 0
                        END AS WithinFiveMinutes
               FROM     test3 m2
                        LEFT OUTER JOIN test3 m1 ON m2.c1 = m1.c1
                                                    AND m2.c2 = m1.c2
                                                    AND ( m2.id = m1.id
                                                          + 1 )
                        LEFT OUTER JOIN test3 m3 ON m2.c1 = m3.c1
                                                    AND m2.c2 = m3.c2
                                                    AND ( m2.id = m3.id
                                                          - 1 )
             ),
        maxdatesforwithin5minutes
          AS ( SELECT   c1 ,
                        c2 ,
                        MAX(date) AS date
               FROM     checktimesagainsttimes
               WHERE    WithinFiveMinutes = 1
               GROUP BY c1 ,
                        c2 ,
                        withinFiveMinutes
               UNION
               SELECT   c1 ,
                        c2 ,
                        date
               FROM     checktimesagainsttimes
               WHERE    WithinFiveMinutes = 0
             )
    SELECT  t.id ,
            t.c1 ,
            t.c2 ,
            t.date
    FROM    maxdatesforwithin5minutes m
            INNER JOIN test3 t ON m.c1 = t.c1
                                  AND m.c2 = t.c2
                                  AND m.date = t.date

Answer 2

Try

SELECT MAX(id), C1, C2, MAX([date])
FROM tbl
GROUP BY C1, C2, DATEADD(MINUTE, DATEDIFF(MINUTE,0,[date]) / 5 * 5, 0)