对表进行分区以获取日期范围

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?

我该如何正确折叠这些行?

1 个解决方案

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

grp可帮助您识别具有相同LoginID,Username值的连续行。

#1