How to get count of consecutive dates

Question

For example there is some table with dates:

2015-01-01
2015-01-02
2015-01-03
2015-01-06
2015-01-07
2015-01-11

I have to write ms sql query, which will return count of consecutive dates starting from every date in the table. So the result will be like:

2015-01-01   1
2015-01-02   2
2015-01-03   3
2015-01-06   1
2015-01-07   2
2015-01-11   1

It seems to me that I should use LAG and LEAD functions, but now I even can not imagine the way of thinking.

Answer 1

CREATE TABLE #T ( MyDate DATE) ;
INSERT #T VALUES ('2015-01-01'),('2015-01-02'),('2015-01-03'),('2015-01-06'),('2015-01-07'),('2015-01-11')

SELECT 
    RW=ROW_NUMBER() OVER( PARTITION BY GRP  ORDER BY MyDate) ,MyDate
FROM
(
SELECT 
    MyDate, DATEDIFF(Day, '1900-01-01' , MyDate)- ROW_NUMBER() OVER( ORDER BY MyDate ) AS GRP
FROM #T 
) A

DROP TABLE #T;

Answer 2

You can use this CTE:

;WITH CTE AS (
   SELECT [Date],        
          ROW_NUMBER() OVER(ORDER BY [Date]) AS rn,
          CASE WHEN DATEDIFF(Day, PrevDate, [Date]) IS NULL THEN 0
               WHEN DATEDIFF(Day, PrevDate, [Date]) > 1 THEN 0
               ELSE 1
          END AS flag
   FROM (
      SELECT [Date], LAG([Date]) OVER (ORDER BY [Date]) AS PrevDate
      FROM #Dates ) d
)

to produce the following result:

Date        rn  flag
===================
2015-01-01  1   0
2015-01-02  2   1
2015-01-03  3   1
2015-01-06  4   0
2015-01-07  5   1
2015-01-11  6   0

All you have to do now is to calculate a running total of flag up to the first occurrence of a preceding zero value:

;WITH CTE AS (
   ... cte statements here ...
)
SELECT [Date], b.cnt + 1
FROM CTE AS c
OUTER APPLY (
   SELECT TOP 1 COALESCE(rn, 1) AS rn 
   FROM CTE
   WHERE flag = 0 AND rn < c.rn
   ORDER BY rn DESC
) a 
CROSS APPLY (
   SELECT COUNT(*) AS cnt
   FROM CTE 
   WHERE c.flag <> 0 AND rn < c.rn AND rn >= a.rn
) b

OUTER APPLY calculates the rn value of the first zero-valued flag that comes before the current row. CROSS APPLY calculates the number of records preceding the current record up to the first occurrence of a preceding zero valued flag.