CODEWITHSUNDEEP
sqlsql-serversql-server-2012window-functions
zimdanen
zimdanen

Reputation: 5626

Partition a table to get date ranges

I have a table of history for a user, and I'm trying to find date ranges that the user held a specific username. The table is an audit table that gets filled by a trigger, so it has entries every time there's a change to a user, not just to the username. I can do this to get the 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, what I'd really like to do is to collapse each username duration so that there's one row per date range that a user held a username. Basically, I'm looking for this output:

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 would I go about collapsing these rows correctly?

Upvotes: 6

Views: 2012

Answers (1)

Giorgos Betsos
Giorgos Betsos

Reputation: 72205

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 you identify consecutive rows having the same LoginID, Username values.

Upvotes: 4

Related Questions