sql - 时间重叠时如何从SQL获取持续时间？另外，如何从sql中获取空闲时间

Question

两个问题:

1. 当时间重叠时如何从 sql 查询中获取持续时间？
2. 我如何获得空闲时间(一个人不工作的时间，或开始/停止时间的间隔)

下面是一个创建临时表来演示问题的查询。

示例 SQL:

IF OBJECT_ID('tempdb..#Work') IS NOT NULL 
   DROP TABLE #Work

IF OBJECT_ID('tempdb..#Employee') IS NOT NULL 
   DROP TABLE #Employee

-- Create tables:
CREATE TABLE #Work 
(
    WorkID int NOT NULL IDENTITY(1,1),
    TaskID int NOT NULL,
    EmployeeID int NOT NULL,
    StartTime datetime NOT NULL,
    EndTime datetime
) ON [PRIMARY]

CREATE TABLE #Employee 
(
    EmployeeID int NOT NULL IDENTITY(1,1),
    FirstName varchar(255) NOT NULL,
    LastName varchar(255) NOT NULL
) ON [PRIMARY]

-- Insert test data:
DECLARE @TomID int=0
DECLARE @JaneID int=0

INSERT INTO #Employee (FirstName, LastName) VALUES ('Tom','McTester')
SET @TomID = @@IDENTITY

INSERT INTO #Employee (FirstName, LastName) VALUES ('Jane','Testerson')
SET @JaneID = @@IDENTITY

-- Tom worked 1:45, with some overlap of time:
INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) 
VALUES (@TomID,1,'1/1/2014 10:00','1/1/2014 11:00')

INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) 
VALUES (@TomID,4,'1/1/2014 10:30','1/1/2014 11:45')

INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) 
VALUES (@TomID,5,'1/1/2014 10:05','1/1/2014 11:22')

-- Jane work 3:00, with no overlap of time:
INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) 
VALUES (@JaneID, 1, '1/1/2014 10:00', '1/1/2014 11:35') -- 1 minute idle

INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) 
VALUES (@JaneID, 1, '1/1/2014 11:36', '1/1/2014 14:54') -- 11 minutes idle

INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) 
VALUES (@JaneID, 1, '1/1/2014 15:05', '1/1/2014 15:45') -- 15 minutes idle

INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) 
VALUES (@JaneID, 1, '1/1/2014 16:00', '1/1/2014 17:30')

-- QUESTION 1:
-- How do I get the total time worked for each employee?
-- 
-- The query below is correct for Jane, but Tom had overlapping time so his number should be 1.75, not 3.553
SELECT
    e.FirstName + ' ' + e.Lastname as Employee,
    ROUND(SUM(CAST(DateDiff("n",StartTime, EndTime) AS float)/60),3) AS Duration
FROM
    #Work w
INNER JOIN 
    #Employee e on e.EmployeeID=w.EmployeeID
GROUP BY
    e.FirstName, e.LastName

-- QUESTION 2:
-- Next, how can I get the idle time? (That would be time an employee is not working on a task)
-- Tom does not have any idle time, but Jane does.

Answer 1

问题 1 的答案:采样日期:

IF OBJECT_ID('tempdb..#Work') IS NOT NULL DROP TABLE #Work
IF OBJECT_ID('tempdb..#Employee') IS NOT NULL DROP TABLE #Employee

-- Create tables:
CREATE TABLE #Work (
    WorkID int NOT NULL IDENTITY(1,1),
    TaskID int NOT NULL,
    EmployeeID int NOT NULL,
    StartTime datetime NOT NULL,
    EndTime datetime
    ) ON [PRIMARY]

CREATE TABLE #Employee (
    EmployeeID int NOT NULL IDENTITY(1,1),
    FirstName varchar(255) NOT NULL,
    LastName varchar(255) NOT NULL
    ) ON [PRIMARY]


-- Insert test data:

DECLARE @TomID int=0
DECLARE @JaneID int=0

INSERT INTO #Employee (FirstName, LastName) VALUES ('Tom','McTester')
SET @TomID=@@IDENTITY
INSERT INTO #Employee (FirstName, LastName) VALUES ('Jane','Testerson')
SET @JaneID=@@IDENTITY

-- Tom worked 1:45, with some overlap of time:
INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) VALUES (@TomID,1,'1/1/2014 10:00','1/1/2014 11:00')
INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) VALUES (@TomID,4,'1/1/2014 10:30','1/1/2014 11:45')
INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) VALUES (@TomID,5,'1/1/2014 10:05','1/1/2014 11:22')

-- Jane work 3:00, with no overlap of time:
INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) VALUES (@JaneID,1,'1/1/2014 10:00','1/1/2014 11:35') -- 1 minue idle
INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) VALUES (@JaneID,1,'1/1/2014 11:36','1/1/2014 14:54') -- 11 minues idle
INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) VALUES (@JaneID,1,'1/1/2014 15:05','1/1/2014 15:45') -- 15 minutes idle
INSERT INTO #Work (EmployeeID, TaskID, StartTime, EndTime) VALUES (@JaneID,1,'1/1/2014 16:00','1/1/2014 17:30')

查询:

;WITH cte_RemoveOverlaps AS
(
SELECT w1.WorkID,
      w1.TaskID,
      w1.EmployeeID,
      CASE
      WHEN w1.StartTime < w2.EndTime THEN w2.EndTime
      ELSE w1.StartTime
      END AS StartTime,
      CASE
      WHEN w1.EndTime < CASE
                    WHEN w1.StartTime < w2.EndTime THEN w2.EndTime
                    ELSE w1.StartTime
                    END THEN CASE
                            WHEN w1.StartTime < w2.EndTime THEN w2.EndTime
                            ELSE w1.StartTime
                            END
      ELSE w1.EndTime
      END AS EndTime
FROM #Work AS w1
    LEFT OUTER JOIN #Work AS w2
    ON w1.WorkID = w2.WorkID + 1
    AND w1.EmployeeID = w2.EmployeeID
)
SELECT
    e.FirstName + ' ' + e.Lastname as Employee,
    ROUND(SUM(CAST(DateDiff("n",StartTime, EndTime) AS float)/60),3) AS Duration,
    CONVERT(CHAR(15), DATEADD(second, SUM(DATEDIFF(s, StartTime , EndTime)), 0), 114) [TotalWorkhours],
    CONVERT(CHAR(15), DATEADD(second, SUM(DATEDIFF(s, EndTime,StartTime)), 0), 114) [TotalBreaks]
FROM
    cte_RemoveOverlaps w
    INNER JOIN #Employee e on e.EmployeeID=w.EmployeeID
GROUP BY
    e.FirstName, e.LastName