Assign a counter in SQL Server for recording with consecutive dates and only increment when dates are not consecutive

Question

I try to assign Trip #entries for Clients with consecutive days and increase the trip identifier if they have a gap on consecutive days, and come later in the month, for example. The data structure is as follows:

CustomerID    Date
1             2014-01-01
1             2014-01-02
1             2014-01-04
2             2014-01-01
2             2014-01-05
2             2014-01-06
2             2014-01-08

The desired result, based on the above data set example, would be:

CustomerID    Date          Trip
1             2014-01-01    1
1             2014-01-02    1
1             2014-01-04    2
2             2014-01-01    1
2             2014-01-05    2
2             2014-01-06    2
2             2014-01-08    3

So, if the Dates for this Client are reverse, it is considered the same trip and has the same Trip #. Is there a way to do this in SQL Server? I am using MSSQL 2012.

LAG, ROW_NUMBER OVER/PARTITION BY Recursive Table Variable Function. , , , . , , .

.

Answer 1

Date Date (.. ), , , DENSE_RANK() by Date - ROW_NUMBER() days, , :

WITH cte AS (
  SELECT CustomerID, Date, 
    DATEADD(DAY, 
            -ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY Date), 
            Date) dt
  FROM trips
)
SELECT CustomerID, Date, 
  DENSE_RANK() OVER (PARTITION BY CustomerID ORDER BY dt)
FROM cte;

SQLfiddle .