Need help in writing query. I table Requestdetails which contain the below data.
Pkey
RequestId
StateName
ResolverGroup
DateCreated
1
123
Resolved
29
9/27/18 15:16
2
123
Acknowledge
29
9/27/18 15:16
3
123
Assign To
29
9/27/18 15:08
4
123
Assign To
32
9/27/18 15:05
5
123
Assign To
30
9/27/18 15:00
6
123
Acknowledge
29
9/25/18 15:13
7
123
Assign To
29
9/25/18 15:00
8
123
Assign To
30
9/25/18 14:57
9
123
Submitted
29
9/25/18 14:53
I need below output
RequestId
ResolverGroup
Recvd Date
Processdate
123
29
9/25/18 14:53
9/25/18 14:57
123
30
9/25/18 14:57
9/25/18 15:00
123
29
9/25/18 15:00
9/27/18 15:00
123
30
9/27/18 15:00
9/27/18 15:05
123
32
9/27/18 15:05
9/27/18 15:08
123
29
9/27/18 15:08
9/27/18 15:16
I have written the below query, but i am not getting the above required output.
select
a.RequestId,a.ResolverGroup,a.datecreated RecvdDate,b.DateCreated Processdate
from Requestdetails a
Left Outer Join Requestdetails b on a.RequestId=b.RequestId
AND a.DateCreated<b.DateCreated
AND b.StateName in ('Submitted','Assign To')
and a.ResolverGroup<>b.ResolverGroup
where a.StateName in ('Submitted','Assign To')
AND a.RequestId in (123)
Not sure what the goal is - but maybe this will help?
Select RequestId
, ResolverGroup
, ReceivedDate = a.DateCreated
, ProcessedDate = lead(a.DateCreated, 1) over(Partition By a.RequestId Order By a.DateCreated)
From RequestDetails a
Where a.StateName <> 'Acknowledge'
And a.RequestId = 123;
You need to include the 'Resolved' status to get the latest date created for the processed date. To exclude that last row from the total results:
With requests
As (
Select RequestId
, ResolverGroup
, ReceivedDate = a.DateCreated
, ProcessedDate = lead(a.DateCreated, 1) over(Partition By a.RequestId Order By a.DateCreated)
From RequestDetails a
Where a.StateName <> 'Acknowledge'
And a.RequestId = 123
)
Select *
From requests
Where ProcessedDate Is Not Null
Order By
ReceivedDate;
Thanks Jeff for the query.
I am looking to get the output then when did the resolver group received the request and when request is submitted or assign to next resolver group. when the other group received the request. if request is moved to the other resolver group state name for that request will be submitted or Assign to.
I got the required output. Please find below query
select
a.RequestId,a.ResolverGroup,a.datecreated RecvdDate
,lead(a.datecreated) over(order by a.datecreated) processdate Processdate
from Requestdetails a
AND b.StateName in ('Submitted','Assign To')
AND a.RequestId in (123)
drop table #data
go
create table #data
(
Pkey int,
RequestId int,
StateName varchar(100),
ResolverGroup int,
DateCreated datetime
)
go
insert into #data select 1 ,123,'Resolved', 29,'9/27/18 15:16'
insert into #data select 2 ,123,'Acknowledge', 29,'9/27/18 15:16'
insert into #data select 3 ,123,'Assign To', 29,'9/27/18 15:08'
insert into #data select 4 ,123,'Assign To', 32,'9/27/18 15:05'
insert into #data select 5 ,123,'Assign To', 30,'9/27/18 15:00'
insert into #data select 6 ,123,'Acknowledge', 29,'9/25/18 15:13'
insert into #data select 7 ,123,'Assign To', 29,'9/25/18 15:00'
insert into #data select 8 ,123,'Assign To', 30,'9/25/18 14:57'
insert into #data select 9 ,123,'Submitted', 29,'9/25/18 14:53'
go
SQL Script
SELECT a.requestid,
a.resolvergroup,
b.recvddate,
b.processdate
FROM (SELECT *
FROM #data) a
JOIN (SELECT Min(a.datecreated) AS Processdate,
b.datecreated AS recvddate
FROM #data a
JOIN #data b
ON a.datecreated > b.datecreated
GROUP BY b.datecreated) b
ON a.datecreated = b.recvddate
ORDER BY b.recvddate
go