Need help in writing a query

prakashuj · 2018-09-30 11:57:00 UTC

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)

Thanks and Regards
Prakash

jeffw8713 · 2018-09-30 17:02:22 UTC

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;

prakashuj · 2018-09-30 18:36:21 UTC

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.

prakashuj · 2018-10-01 06:48:53 UTC

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)

jeffw8713 · 2018-10-01 17:57:13 UTC

This is essentially the same query I provided - except here you are not including the final row of 'Resolved' which has the 15:16 date/time.

The last row in your results will only include the 15:08 time since that is the last row that has an assign to state.

harishgg1 · 2018-10-12 14:47:08 UTC

Hi

I solved the query

Please look if it is what you want

Create Data Script

use tempdb
go

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

Result