Divide the table to get date ranges

Question

I have a history table for the user, and I'm trying to find date ranges in which the user has a specific username. The table is an audit table that is populated with a trigger, so it has entries every time there is a change for the user, and not just for the username. I can do this to get a date range for each row:

CREATE TABLE #LoginHistory ( LoginHistoryID INT IDENTITY(1,1), LoginID INT, Username VARCHAR(32), StartDate DATETIME ) INSERT INTO #LoginHistory (LoginID, Username, StartDate) VALUES (1, 't', '2016-01-01'), (1, 't', '2016-01-02'), (1, 't', '2016-01-04'), (1, 'test', '2016-01-05'), (2, 't', '2016-01-08'), (2, 'tom', '2016-01-09'), (1, 'test', '2016-01-15'), (1, 't', '2016-02-01') SELECT LoginID, Username, StartDate, EndDate = LEAD(StartDate) OVER (PARTITION BY LoginID ORDER BY StartDate ASC) FROM #LoginHistory WHERE LoginID = 1 ORDER BY StartDate ASC DROP TABLE #LoginHistory 

Output:

 LoginID Username StartDate EndDate 1 t 2016-01-01 00:00:00.000 2016-01-02 00:00:00.000 1 t 2016-01-02 00:00:00.000 2016-01-04 00:00:00.000 1 t 2016-01-04 00:00:00.000 2016-01-05 00:00:00.000 1 test 2016-01-05 00:00:00.000 2016-01-15 00:00:00.000 1 test 2016-01-15 00:00:00.000 2016-02-01 00:00:00.000 1 t 2016-02-01 00:00:00.000 NULL 

However, I would really like to do this in order to reduce each username duration so that one line per date range contains the username. Basically, I am looking for this conclusion:

 LoginID Username StartDate EndDate 1 t 2016-01-01 00:00:00.000 2016-01-05 00:00:00.000 1 test 2016-01-05 00:00:00.000 2016-02-01 00:00:00.000 1 t 2016-02-01 00:00:00.000 NULL 

How can I collapse these lines correctly?

Answer 1

You can use the following query:

 SELECT LoginID, Username, MIN(StartDate) AS StartDate, MAX(EndDate) AS EndDate FROM ( SELECT LoginID, Username, StartDate, EndDate = LEAD(StartDate) OVER (PARTITION BY LoginID ORDER BY StartDate ASC), ROW_NUMBER() OVER (ORDER BY StartDate) - ROW_NUMBER() OVER (PARTITION BY LoginID, Username ORDER BY StartDate) AS grp FROM #LoginHistory WHERE LoginID = 1) AS t GROUP BY LoginID, Username, grp ORDER BY StartDate ASC 

grp helps identify consecutive lines with the same LoginID, Username .