look for records with consecutive dates and know the number of days-CodePudding

I have a table containing the fields:

cazi, cdip, date

1 2 13/03/2021
1 2 14/03/2021
1 2 15/03/2021
1 2 18/03/2021
1 2 19/03/2021
1 3 13/03/2021
1 3 14/03/2021
1 3 15/03/2021
1 3 20/03/2021
1 3 21/03/2021

I can't get the result with the columns:

cazi, cdip, date1, date2, num_dd

1 2 13/03/2021 15/03/2021 3
1 2 18/03/2021 19/03/2021 2
1 3 13/03/2021 15/03/2021 3
1 3 20/03/2021 21/03/2021 2

Can you help me ?

With the following code I get the min and max of the records, but I need the consecutive records:

WITH
dateGroup AS
(  
SELECT DISTINCT
       UniqueDate = [date]
      ,DateGroup  = DATEADD(dd, - ROW_NUMBER() OVER (ORDER BY [date]), [date])
FROM malt
GROUP BY [date]
)

SELECT distinct
     StartDate = MIN(UniqueDate)
    ,EndDate   = MAX(UniqueDate)
    ,Days      = DATEDIFF(dd,MIN(UniqueDate),MAX(UniqueDate)) 1
    ,cazi
              ,cdip
FROM dateGroup JOIN 
     malt u ON u.date = UniqueDate


GROUP BY 
DateGroup
,cazi
,cdip

CodePudding user response：

This is traditional GAPS & ISLAND problem. You can try below query to achieve the desired result -

SELECT cazi, cdip, MIN(T.[date]), MAX(T.[date])
  FROM (SELECT M.*, ROW_NUMBER() OVER(PARTITION BY cdip ORDER BY [date]) RN
          FROM malt M) T
 GROUP BY cazi, cdip, DATEADD(DAY, - RN, [date]);

Demo.