Need help in writing Query

• Prakash-485822

  SSC Eights!

  Points: 982

  More actions

  September 30, 2018 at 6:25 am

  #364290

  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

• saravanatn

  SSCarpal Tunnel

  Points: 4533

  More actions

  September 30, 2018 at 9:37 am

  #2007438

  Prakash-485822 - Sunday, September 30, 2018 6:25 AM

  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

  Quick question.  If you are filtering by  a.StateName in ('Submitted','Assign To') then how do got  Processdate='9/27/18 15:16' as it coming under a.StateName in ('Resolved','Acknowledge')

  Saravanan

• Prakash-485822

  SSC Eights!

  Points: 982

  More actions

  October 1, 2018 at 12:47 am

  #2007455

  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)

• Jeff Moden

  SSC Guru

  Points: 1004448

  More actions

  October 1, 2018 at 7:09 am

  #2007493

  Prakash-485822 - Sunday, September 30, 2018 6:25 AM

  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

  When you post sample data, you'll get a whole lot better help if you post it in a readily consumable format as code.  Please see the first link in my signature below for how to do that.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• drew.allen

  SSC Guru

  Points: 76999

  More actions

  October 1, 2018 at 7:48 am

  #2007497

  Prakash-485822 - Monday, October 1, 2018 12:47 AM

  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)

  This query has a bunch of errors.  You reference an alias 'b' that is never defined.  You have conditional expressions, but no WHERE clause.

  Drew

  J. Drew Allen
  Business Intelligence Analyst
  Philadelphia, PA