SQLTeam.com | Weblogs | Forums

Need help in writing a query


#1

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


#2

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;

#3

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.


#4

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)


#5

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.


#6

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