Need to know the previous Report Type? Any Query or T-SQL?

• prakashr.r7

  SSCrazy

  Points: 2828

  More actions

  October 15, 2013 at 5:25 am

  #278077

  Hi Friends,

  I am looking for a query or procedure which should give the previous report type of every type 'R' . 'R' is called Re-Do so i want to know the previous type which is realted to 'R'.

  I tried a query with the join condition like Table A join Table B on A.ID = B.ID-1 and A.PID = B.PID

  but the problem is, the type 'R' may be coming continuously , the query i tried is failed.

  For eg. In the below table , as you see the ID from 9 to 14 has type 'R', the result should be 'F' but with my query i get the same type 'R'.

  So friends, please gimme your suggestions on this. If you are not clear with my question, please let me know.

  Declare @Temp Table (PID int, ID int, ReportType char(1))

  Insert Into @Temp

  select 4,1,'P'

  union all

  select 4,2,'P'

  union all

  select 4,3,'P'

  union all

  select 4,4,'P'

  union all

  select 4,5,'R'

  union all

  select 4,6,'F'

  union all

  select 4,7,'F'

  union all

  select 4,8,'R'

  union all

  select 4,9,'R'

  union all

  select 4,10,'R'

  union all

  select 4,11,'R'

  union all

  select 4,12,'R'

  union all

  select 4,13,'R'

  union all

  select 4,14,'R'

  union all

  select 4,15,'F'

  union all

  select 4,16,'R'

  union all

  select 4,17, 'R'

  union all

  select 4,18,'P'

  select * from @Temp

• Mark Cowne

  One Orange Chip

  Points: 26952

  More actions

  October 15, 2013 at 6:15 am

  #1658480

  SELECT A.*,CA.*

  FROM @Temp A

  OUTER APPLY(SELECT TOP 1 B.ReportType

  FROM @Temp B

  WHERE B.PID = A.PID

  AND B.ID < A.ID

  AND B.ReportType<>'R'

  AND A.ReportType='R'

  ORDER BY B.ID DESC) CA(PrevReportType)

  ____________________________________________________

  Deja View - The strange feeling that somewhere, sometime you've optimised this query before

  How to get the best help on a forum

  http://www.sqlservercentral.com/articles/Best+Practices/61537

• prakashr.r7

  SSCrazy

  Points: 2828

  More actions

  October 15, 2013 at 7:10 am

  #1658510

  Mark-101232 (10/15/2013)

  ---

  SELECT A.*,CA.*

  FROM @Temp A

  OUTER APPLY(SELECT TOP 1 B.ReportType

  FROM @Temp B

  WHERE B.PID = A.PID

  AND B.ID < A.ID

  AND B.ReportType<>'R'

  AND A.ReportType='R'

  ORDER BY B.ID DESC) CA(PrevReportType)

  Great Mark. Thank you, well the result is exactly that i am looking for.

  Thanks a lot.