Combine multiple rows for a date range into one row

Transact-SQL
1

Hello,

I have the following sql that works great:

SELECT

   CTE.PERSONNUM, CTE.PERSONFULLNAME, CTE.LOCATION, CTE.LOCATION_DESCRIPTION, CTE.COST_CENTER, CTE.COST_CENTER_DESCRIPTION, CTE.REPORTS_TO_MGR, CTE.APPLYDATE,

   ROW_NUMBER() OVER (PARTITION BY PERSONNUM, ConsecutiveGrouping ORDER BY APPLYDATE) as CONSECUTIVEDAYS

FROM CTE

ORDER BY

LOCATION, PERSONFULLNAME, APPLYDATE

However, it produces one row for each date in the consecutive days date range. I want to produce one row for each consecutive day date range.

Instead of this (shortened example with some of the columns removed):

EmpID Name Date Consecutive Days
100 Doe, John 1/1/2019 1
100 Doe, John 1/2/2019 2
100 Doe, John 1/3/2019 3
100 Doe, John 1/10/2019 1
100 Doe, John 1/11/2019 2
100 Doe, John 1/12/2019 3
100 Doe, John 1/13/2019 4
etc.

I want the output to look like this:
EmpID Name From_Date To_Date Consecutive_Days
100 Doe, John 1/1/2019 1/3/2019 3
100 Doe, John 1/10/2019 1/13/2019 4

Any help would be greatly appreciated!
Thanks!

2 
--===== This is how you should present data for such questions.
     -- It helps people help you more quickly and accurately
     -- because it defines the data and makes it readily consumable
     -- for people to test their code with before posting an answer.
     -- Also note that we don't need the "Consecutive_Days" column
     -- for this.
 CREATE TABLE #TestTable
        (
         EmpID  INT
        ,Name   VARCHAR(20)
        ,Date   DATE
        )
;
 INSERT INTO #TestTable WITH (TABLOCK)
 VALUES  (100,'Doe, John','1/1/2019' )
        ,(100,'Doe, John','1/2/2019' )
        ,(100,'Doe, John','1/3/2019' )
        ,(100,'Doe, John','1/10/2019')
        ,(100,'Doe, John','1/11/2019')
        ,(100,'Doe, John','1/12/2019')
        ,(100,'Doe, John','1/13/2019')
;
GO
--===== Here's one solution to the problem that can also handle more than 1 employee.
     -- I home that Name is actually consistent per EmpID. If it's not, let us know.
   WITH cteGroup AS
(
 SELECT GroupNum = DATEADD(dd,-ROW_NUMBER() OVER (PARTITION BY EmpID ORDER BY Date),Date)
        ,*
   FROM #TestTable
)
 SELECT  EmpID
        ,Name
        ,From_Date = MIN(Date)
        ,To_Date = MAX(Date)
        ,Consecutive_Days = DATEDIFF(dd,MIN(Date),MAX(Date))+1
   FROM cteGroup
  GROUP BY GroupNum, EmpID, Name
  ORDER BY EmpID, Name, From_Date
;
GO