I was researching a similar issue with this question with a slight difference.

This is the script to recreate in 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;

When I run my select, I get the following:

1. My question is how do I get total_duration for each TProcessDesc from the 1st entry to the last entry? For example,

2. Is it possible to concatenate TProcessStatus with the results from question 1?

Thank you

CodePudding user response：

This is really ugly in 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;

In SQL Server 2017 , you can use the much tidier:

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;

Both produce the same results:

TId1	TId2	TProcessDesc	TProcessStatuses	Total_Duration
Tikt22	111111	Assigned Team1	Assigned,On Hold,Customer Approve	8
Tikt22	111111	Assigned Team2	Assigned,Work In Progress,Waiting on Customer,Accepted	80
Tikt22	111111	Review New	Work In Progress,Assigned	3
Tikt22	111111	Ticket Submitted	Submitted,Accepted	1

• Example db<>fiddle

More on string aggregation specifically:

• String aggregation over the years in SQL Server

As an aside, please don't use meaningless aliases like A and B, and please do read all the links here about asking a good database question.