Group Records By Consecutive Dates When Dates Are Not Exactly Consecutive

I have some data that contains dates. I'm trying to group the data by consecutive dates, however, the dates are not exactly consecutive. Here is an example: DateColumn

Solution 1:

Try this:

selectmin(t.dateColumn) date1, max(t.dateColumn) date2, count(*)
from (
    select t.*, sum(val) over (
            orderby t.dateColumn
            ) grp
    from (
        select t.*, casewhen datediff(ms, lag(t.dateColumn, 1, t.dateColumn) over (
                            orderby t.dateColumn
                            ), t.dateColumn) >60000then1else0end val
        from your_table t
        ) t
    ) t
groupby grp;

Produces:

uses the analytic function lag() to mark starting of next batch based on the difference of datecolumn from the last one and then use analytic sum() on it to create group of batches and then group by it to find required aggregates.

There may be some misclassification in groups due to rounding issues with DATETIME. From MSDN,

datetime values are rounded to increments of .000, .003, or .007 seconds, as shown in the following table.

---

Here is the same query rewritten using CTEs:

WITH cte1(DateColumn, ValueColumn) AS (
    -- Insert your query that returns a datetime column and any other columnSELECT
        SomeDate,
        SomeValue
    FROM SomeTable
    WHERE SomeColumn ISNOTNULL
), cte2 AS (
    -- This query adds a column called "val" that contains-- 1 when current row date - previous row date > 1 minute-- 0 otherwiseSELECT
        cte1.*,
        CASEWHEN DATEDIFF(MS, LAG(DateColumn, 1, DateColumn) OVER (ORDERBY DateColumn), DateColumn) >60000THEN1ELSE0ENDAS val
    FROM cte1
), cte3 AS (
    -- This query adds a column called "grp" that numbers -- the groups using running sum over the "val" columnSELECT
        cte2.*,
        SUM(val) OVER (ORDERBY DateColumn) AS grp
    FROM cte2
)
SELECTMIN(DateColumn) Date1,
    MAX(DateColumn) Date2,
    COUNT(ValueColumn) [Count]
FROM cte3
GROUPBY grp

Solution 2:

Remove seconds and milliseconds from DateColumn and do the grouping

  select min(DateColumn), 
         max(DateColumn), 
         count(*)
  from Yourtable
  group by DATEADD(MINUTE, DATEDIFF(MINUTE, 0, DateColumn), 0)

Here is some questions on truncating seconds for datetime

Truncate seconds and milliseconds in SQL

A way to extract from a DateTime value data without seconds

Solution 3:

This does not works, if youre comparing gaps between dates (60s). But you can try this, if you need to get records, that belongs to same minute X.

SELECT
     [Date1]    =MIN([DateColumn])
    ,[Date2]    =MAX([DateColumn])
    ,[Count]    =COUNT([DateColumn]) 
FROM
    [my_table]
GROUPBY
    DATEADD(mi, DATEDIFF(mi, 0, [DateColumn]), 0);