SQL查詢以獲取每個狀態的總持續時間

我正在研究與這個問題類似的問題，但略有不同。

這是在 SQL Server 2012 Management Studio 中重新創建的腳本：

CREATE TABLE #Tickets 
(
    TId1 varchar(6), 
    TId2 varchar(6),
    TSubmitBy varchar(200), 
    TDate DATE
);

CREATE TABLE #TiktProcess
(
    TId1 VARCHAR(6), 
    TId2 VARCHAR(6),   
    TProcessDesc VARCHAR(100), 
    TProcessStatus varchar(50),   
    TProcessFullName varchar(200),
    TProcessRecDate Date
);

INSERT INTO #Tickets (TId1, TId2, TSubmitBy, TDate) 
VALUES ('Tikt22', '111111', 'ASmith', '2022-05-01'), 
       ('Tikt22', '111112', 'BSmith', '2022-05-01');

INSERT INTO #TiktProcess (TId1, TId2, TProcessDesc, TProcessStatus, TProcessFullName, TProcessRecDate) 
VALUES  ('Tikt22', '111111', 'Ticket Submitted', 'Submitted', 'ASmith', '2022-05-01'), 
        ('Tikt22', '111111', 'Ticket Submitted', 'Accepted', 'AJohnson', '2022-05-02'), 
        ('Tikt22', '111111', 'Review New', 'Work In Progress', 'AWilliams', '2022-05-02'),  
        ('Tikt22', '111111', 'Review New', 'Assigned', 'AWilliams', '2022-05-05'), 
        ('Tikt22', '111111', 'Assigned Team1', 'Assigned', 'ABrown', '2022-05-05'),
        ('Tikt22', '111111', 'Assigned Team1', 'On Hold', 'BJones', '2022-05-13'), 
        ('Tikt22', '111111', 'Assigned Team2', 'Assigned', 'AGarcia', '2022-05-05'), 
        ('Tikt22', '111111', 'Assigned Team2', 'Work In Progress', 'CSmith', '2022-05-06'),  
        ('Tikt22', '111111', 'Assigned Team2', 'Waiting on Customer', 'BMiller', '2022-05-17'), 
        ('Tikt22', '111111', 'Assigned Team2', 'Accepted', 'ADavis', '2022-02-26'),
        ('Tikt22', '111111', 'Assigned Team1', 'Customer Approve', 'CBrown', '2022-05-13');

SELECT 
    A.TId1,
    B.TProcessDesc, B.TProcessStatus, B.TProcessRecDate, B.TProcessFullName,
    [duration] = DATEDIFF (day, B.TProcessRecDate, 
                           LEAD (B.TProcessRecDate, 1, B.TProcessRecDate) OVER (PARTITION BY B.TProcessDesc 
                                                                              ORDER BY B.TProcessRecDate)),     
    [total_duration] = -DATEDIFF (day, B.TProcessRecDate, 
                                  FIRST_VALUE(B.TProcessRecDate) OVER (PARTITION BY B.TProcessDesc ORDER BY B.TProcessRecDate))     
FROM
    #Tickets A 
JOIN
    #TiktProcess B ON A.TId1 = B.TId1   
                   AND A.TId2 = B.TId2  
WHERE
    B.TId2 = '111111'

/* Desired Results - Question 1
TId1   TId2   TProcessDesc      Total_Duration
Tikt22 111111 Ticket Submitted  1
Tikt22 111111 Review New        3
Tikt22 111111 Assigned Team 1   8
Tikt22 111111 Assigned Team 1   80

Desired Results - Question 2
TId1   TId2   TProcessDesc      Total_Duration TProcessStatus
Tikt22 111111 Ticket Submitted  1              Submitted, Accepted
Tikt22 111111 Review New        3              Assigned, Work In Progress
Tikt22 111111 Assigned Team 1   8              Assigned, On Hold, Customer Approve
Tikt22 111111 Assigned Team 1   80
*/

DROP TABLE #Tickets; 
DROP TABLE #TiktProcess;

當我運行我的選擇時，我得到以下資訊：

1. 我的問題是如何獲得從第一個條目到最后一個條目total_duration的每個條目？TProcessDesc例如，

2. 是否可以TProcessStatus與問題 1 的結果連接？

謝謝

uj5u.com熱心網友回復：

這在 SQL Server 2012 中真的很難看：

SELECT TP.TId1, TP.TId2, TP.TProcessDesc, 
  TProcessStatuses = STUFF((SELECT CONCAT(',', TP2.TProcessStatus)
    FROM #TiktProcess AS TP2
    WHERE TP2.TId1 = TP.TId1 
      AND TP2.TId2 = TP.TId2 
      AND TP2.TProcessDesc = TP.TProcessDesc
    FOR XML PATH(''), TYPE).value(N'./text()[1]', N'varchar(max)'),1,1,''),
  Total_Duration = DATEDIFF(DAY, MIN(TP.TProcessRecDate), MAX(TP.TProcessRecDate))
FROM #Tickets AS T 
INNER JOIN #TiktProcess AS TP
ON T.TId1 = TP.TId1 and T.TId2 = TP.TId2 where TP.TId2 = '111111'
GROUP BY TP.TId1, TP.TId2, TP.TProcessDesc;

在 SQL Server 2017 中，您可以使用更整潔的：

SELECT T.TId1, T.TId2, TP.TProcessDesc, 
  TProcessStatuses = STRING_AGG(TP.TProcessStatus, ','), 
  Total_Duration   = DATEDIFF(DAY, MIN(TP.TProcessRecDate), MAX(TP.TProcessRecDate))
FROM #Tickets AS T 
INNER JOIN #TiktProcess AS TP
ON T.TId1 = TP.TId1 and T.TId2 = TP.TId2 where TP.TId2 = '111111'
GROUP BY T.TId1, T.TId2, TP.TProcessDesc;

兩者都產生相同的結果：

TId1	TId2	TProcessDesc	TProcessStatuses	Total_Duration
抖音22	111111	分配的 Team1	已分配、暫停、客戶批準	8
抖音22	111111	分配的 Team2	已分配，進行中，等待客戶，已接受	80
抖音22	111111	回顧新	進行中的作業，已分配	3
抖音22	111111	提交的票	已提交，已接受	1

• 示例db<>fiddle

有關字串聚合的更多資訊：

• SQL Server 中多年來的字串聚合

順便說一句，請不要使用無意義的別名，例如Aand B，請閱讀此處關于提出一個好的資料庫問題的所有鏈接。

標籤：sql sql服务器 tsql

上一篇：NodeJS，MYSQLconnection.query不是函式

下一篇：從字串轉換日期和/或時間時轉換失敗。SQL查詢