Query

Transact-SQL
1

Hi ,

Please help in writing the query to get the below result i have a Requested details table which contains the data in the below formate.

Pkey ParentId Status Datelogged
1 123 Submitted 12/9/2018 10:30
2 123 Assigned 12/9/2018 10:40
3 123 Acknowledge 12/9/2018 10:45
4 123 OnHold 12/9/2018 10:55
5 123 Resloved 12/9/2018 11:15
6 124 Submitted 12/9/2018 10:30
7 124 Assigned 12/9/2018 10:40
8 124 Acknowledge 12/9/2018 10:45
9 124 OnHold 12/9/2018 10:55
10 124 Work In Progress 12/9/2018 11:16
11 124 Resloved 12/9/2018 11:45

I want the result in the like below.

ParentId Total Resolved time (min) Actual Resolved Time (min)
123 45 25
124 75 54

total resolved time is the the total time take to resolve the ticket time difference resolved - Submitted . e.g for 123 request 12/9/2018 11:15-12/9/2018 10:30 = 45 mins
Actual resolved time is the Total resolve time - request on hold time.
e.g
for 123 request the total resolve time is 45 mins. , but the request is moved to on hold state 12/9/2018 10:55:00 AM and at 11:15 is moved directly to resolved state. so i have we have subtract 20 mins for the total resolve time . so the actual resolved time is 45-20=25 mins

For 124 request total resolved time is 75 min, the request is moved to on hold state at 12/9/2018 10:55:00 AM and then at 12/9/2018 11:16:00 AM the request is moved to work in progress state. we have subtract the 21 mins from the total resolve time. i.e. 75-21=54

2

Assuming datelogged field is of type datetime, try this:

select top(1) with ties
       a.parentid
      ,datediff(minute,a.datelogged,b.datelogged) as total
      ,datediff(minute,a.datelogged,b.datelogged)-datediff(minute,c.datelogged,d.datelogged) as actual
  from yourtable as a
       inner join yourtable as b
               on b.parentid=a.parentid
              and b.[status]='Resloved'
       left outer join yourtable as c
                    on c.parentid=a.parentid
                   and c.[status]='OnHold'
       left outer join yourtable as d
                    on d.parentid=a.parentid
                   and d.datelogged>c.datelogged
 where a.[status]='Submitted'
 order by row_number() over(partition by a.parentid order by d.datelogged)
;

Btw. Resolved is misspelled

3

You should really provide consumable test data with dates in ISO format:

CREATE TABLE #t
(
	Pkey int NOT NULL
	,ParentId int NOT NULL
	,[status] varchar(20) NOT NULL
	,Datelogged datetime NOT NULL
);
INSERT INTO #t
VALUES
	(1, 123, 'Submitted', '20180912 10:30')
	,(2, 123, 'Assigned', '20180912 10:40')
	,(3, 123, 'Acknowledge', '20180912 10:45')
	,(4, 123, 'OnHold', '20180912 10:55')
	,(5, 123, 'Resolved', '20180912 11:15')
	,(6, 124, 'Submitted', '20180912 10:30')
	,(7, 124, 'Assigned', '20180912 10:40')
	,(8, 124, 'Acknowledge', '20180912 10:45')
	,(9, 124, 'OnHold', '20180912 10:55')
	,(10, 124, 'Work In Progress', '20180912 11:16')
	,(11, 124, 'Resolved', '20180912 11:45');

Another approach is to pivot the data:

WITH XTab
AS
(
	SELECT ParentId, Submitted, OnHold, [Work In Progress] AS WIP, Resolved
	FROM
	(
		SELECT ParentId, [status], Datelogged
		FROM #t
	) S
	PIVOT
	(
		MAX(Datelogged)
		FOR [status] IN (Submitted, OnHold, [Work In Progress], Resolved)

	) P
)
SELECT T.ParentId
	,X.mTotal AS [Total Resolved time (min)]
	,X.mTotal - X.mHold AS [Actual Resolved Time (min)]
FROM XTab T
	CROSS APPLY
	(
		VALUES
		(
			DATEDIFF(minute, T.Submitted, T.Resolved)
			,DATEDIFF(minute, T.OnHold, COALESCE(T.WIP, T.Resolved))
		)
	) X (mTotal, mHold)
ORDER BY ParentId;
4

If you have multiple "OnHold" on same parentid, try this:

select parentid
      ,avg(total) as total
      ,sum(actual) as actual
  from (select top(1) with ties
               a.parentid
              ,datediff(minute,a.datelogged,b.datelogged) as total
              ,datediff(minute,c.datelogged,d.datelogged) as actual
          from yourtable as a
               inner join yourtable as b
                       on b.parentid=a.parentid
                      and b.[status]='Resloved'
               left outer join yourtable as c
                            on c.parentid=a.parentid
                           and c.[status]='OnHold'
               left outer join yourtable as d
                            on d.parentid=c.parentid
                           and d.datelogged>c.datelogged
         where a.[status]='Submitted'
         order by row_number() over(partition by a.parentid,c.datelogged order by d.datelogged)
       ) as a
 group by parentid
;

Btw. can a parentid have multiple "Resloved"? ("case" be reopened)

5

Thanks for the query, yes the ticket can be reopened. But i have status field in the table once the ticket is reopened, i am updating the status on the all existing records.

6

Multiple OnHolds can be dealt with by the LEAD function.

WITH Durations
AS
(
	SELECT ParentId
		,DATEDIFF(minute, Datelogged, LEAD(Datelogged, 1) OVER (PARTITION BY ParentId ORDER BY Datelogged)) AS Duration
		,CASE WHEN [status] IN ('OnHold', 'Resolved') THEN 1 ELSE 0 END AS OnHold
	FROM #t
)
SELECT ParentId
	,SUM(Duration) AS [Total Resolved time (min)]
	,SUM(CASE WHEN OnHold = 1 THEN 0 ELSE Duration END) AS [Actual Resolved Time (min)]
FROM Durations
GROUP BY ParentId
ORDER BY ParentId;