我创建了一些代码，允许我在特定子症状之后显示数据中的每条记录
SELECT
ID,
Received,
Closed,
Subsymptom,
DATEDIFF(HOUR, received, COALESCE(closed, GETDATE())) AS 'HoursOpen'
FROM
vw_UserView_OpenAndClosed A
WHERE closed >
(SELECT MAX(closed)
FROM
vw_UserView_OpenAndClosed SUB
WHERE
Subsymptom = 'INSTALL'
AND received >= '2017-06-01'
AND SUB.name = A.name)
数据如下所示
ID received closed Subsymptom HoursOpen
1306438 2017-06-20 2017-06-21 ISSUES 19
1305393 2017-06-18 2017-06-19 REQUEST 12
1299203 2017-06-05 2017-06-05 REQUEST 0
1299415 2017-06-05 2017-06-06 SPECIFIC FAILURE 2
我希望能够做的是创建一个新列，告诉我自引发名为"INSTALL"的原始子症状以来已经过去了多少时间。
ID 可能有间隙，我将有多个"名称"
我已经尝试了选择 Max(已关闭(加入，但我根本无法让它工作，我认为我走在完全错误的轨道上并且有一些头脑模糊。
示例数据
ID Received Closed Subsymptom Name
1306438 2017-06-20 2017-06-21 ISSUES BROOM
1299203 2017-06-05 2017-06-05 REQUEST BROOM
1305393 2017-06-18 2017-06-19 REQUEST CHIVE
1299415 2017-06-06 2017-06-06 SPECIFIC FAILURE BROOM
1299222 2017-06-05 2017-06-05 ISSUES BROOM
1299112 2017-06-05 2017-06-05 CONTACTLESS BROOM
1279168 2017-06-01 2017-06-03 INSTALL BROOM
1287965 2017-06-10 2017-06-10 INSTALL CHIVE
期望的结果
ID Received Closed Subsymptom Name HoursOpen Days Received since Install Closed
1306438 2017-06-20 2017-06-21 ISSUES BROOM 19 17
1299203 2017-06-05 2017-06-05 REQUEST BROOM 0 2
1305393 2017-06-18 2017-06-19 REQUEST CHIVE 12 21
1299415 2017-06-06 2017-06-06 SPECIFIC FAILURE BROOM 2 3
1299222 2017-06-05 2017-06-05 ISSUES BROOM 0 2
1299112 2017-06-05 2017-06-05 ISSUES BROOM 0 2
安装关闭后的接收天数 - 计算从安装结束日期到接收日期的天数。
(稍后将绘制图表，以显示安装后在第 0、1、2、3 天等收到新记录(
编辑：感谢您澄清您的评论并添加所需的示例数据。 一旦我有了它，它就是微风。 因此，将来请务必在发布问题时添加它，以确保最快的响应！
这是代码：
DECLARE @Table TABLE (ID bigint, Received DATETIME, Closed DATETIME, Subsymptom varchar(50), [Name] varchar(50))
DECLARE @Now DateTime = GETDATE()
INSERT INTO @Table
VALUES
(1306438 , '2017-06-20', '2017-06-21', 'ISSUES', 'BROOM'),
(1299203 , '2017-06-05', '2017-06-05', 'REQUEST', 'BROOM'),
(1305393 , '2017-06-18', '2017-06-19', 'REQUEST', 'CHIVE'),
(1299415 , '2017-06-06', '2017-06-06', 'SPECIFIC FAILURE', 'BROOM'),
(1299222 , '2017-06-05', '2017-06-05', 'ISSUES', 'BROOM'),
(1299112, '2017-06-05', '2017-06-05', 'CONTACTLESS', 'BROOM'),
(1279168 , '2017-06-01', '2017-06-03', 'INSTALL', 'BROOM'),
(1287965 , '2017-06-10', '2017-06-10', 'INSTALL', 'CHIVE')
SELECT T.ID,
T.Received,
T.Closed,
T.Subsymptom,
T.[Name],
DATEDIFF(HH, T.Received, COALESCE(T.Closed, @Now)) AS 'HoursOpen',
DATEDIFF(DD, T2.Closed, T.Received) AS [Days Received since Install Closed]
FROM @Table T
JOIN @Table T2
ON T.[Name] = T2.[Name]
AND T2.Subsymptom = 'INSTALL'
WHERE T.Closed > T2.Closed
以下是结果：
(6 row(s) affected)
ID Received Closed Subsymptom Name HoursOpen Days Received since Install Closed
-------------------- ----------------------- ----------------------- -------------------------------------------------- -------------------------------------------------- ----------- ----------------------------------
1306438 2017-06-20 00:00:00.000 2017-06-21 00:00:00.000 ISSUES BROOM 24 17
1299203 2017-06-05 00:00:00.000 2017-06-05 00:00:00.000 REQUEST BROOM 0 2
1305393 2017-06-18 00:00:00.000 2017-06-19 00:00:00.000 REQUEST CHIVE 24 8
1299415 2017-06-06 00:00:00.000 2017-06-06 00:00:00.000 SPECIFIC FAILURE BROOM 0 3
1299222 2017-06-05 00:00:00.000 2017-06-05 00:00:00.000 ISSUES BROOM 0 2
1299112 2017-06-05 00:00:00.000 2017-06-05 00:00:00.000 CONTACTLESS BROOM 0 2
(6 row(s) affected)
您保证的是，每个名称只有一个"安装"子症状实例。 如果是这种情况，此查询将始终有效。 我的结果与您的结果不同的原因是因为您只提供了日期(而不是日期时间(，因此计算会略有偏差。 但是，基本上，您只需将每条记录连接到其原始安装中 - 获取原始记录的关闭时间 - 并将其与当前记录的接收时间进行比较。 非常简单 - 而且直截了当。
如果这不是您要找的，请告诉我。