I have the following sql that works great:
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
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
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!
--===== 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
INSERT INTO #TestTable WITH (TABLOCK)
VALUES (100,'Doe, John','1/1/2019' )
,(100,'Doe, John','1/2/2019' )
,(100,'Doe, John','1/3/2019' )
--===== 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_Date = MIN(Date)
,To_Date = MAX(Date)
,Consecutive_Days = DATEDIFF(dd,MIN(Date),MAX(Date))+1
GROUP BY GroupNum, EmpID, Name
ORDER BY EmpID, Name, From_Date