Qurery help

• Nagaram

  SSCrazy

  Points: 2110

  More actions

  May 28, 2012 at 1:30 am

  #269542

  Hi ,

  here is the details

  CREATE TABLE SAMPLE

  (

  SOURCE_FILE_ID INT,

  CYCLE_ID INT,

  TASK_ID INT ,

  TASK_STATE_DESC VARCHAR(100)

  )

  INSERT INTO SAMPLE

  SELECT 22,1,1,'Valid' UNION ALL

  SELECT 22,1,2,'Error' UNION ALL

  SELECT 22,1,3,'Error' UNION ALL

  SELECT 22,1,4,'Setup Fail' UNION ALL

  SELECT 22,1,5,'Setup Fail' UNION ALL

  SELECT 22,1,6,'Error' UNION ALL

  SELECT 22,1,7,'Setup Fail' UNION ALL

  SELECT 22,1,8,'Error' UNION ALL

  SELECT 22,1,9,'Error' UNION ALL

  SELECT 22,1,10,'Setup Fail' UNION ALL

  SELECT 22,2,1,'Valid' UNION ALL

  SELECT 22,2,2,null UNION ALL

  SELECT 22,2,3,'Error' UNION ALL

  SELECT 22,2,4,null UNION ALL

  SELECT 22,2,5,'Setup Fail' UNION ALL

  SELECT 22,2,6,'Error' UNION ALL

  SELECT 22,2,7,'Setup Fail' UNION ALL

  SELECT 22,2,8,'Error' UNION ALL

  SELECT 22,2,9,'Error' UNION ALL

  SELECT 22,2,10,'Setup Fail' UNION ALL

  SELECT 22,3,1,'Valid' UNION ALL

  SELECT 22,3,2,null UNION ALL

  SELECT 22,3,3,'Error' UNION ALL

  SELECT 22,3,4,null UNION ALL

  SELECT 22,3,5,'Setup Fail' UNION ALL

  SELECT 22,3,6,'Error' UNION ALL

  SELECT 22,3,7,'Setup Fail' UNION ALL

  SELECT 22,3,8,'Error' UNION ALL

  SELECT 22,3,9,'Error' UNION ALL

  SELECT 22,3,10,'Setup Fail' UNION ALL

  select * from SAMPLE

  I need to write a qurey to select

  all the records having TASK_STATE_DESC='Error'

  and Next subsequent tasks if it is satisfied the condition TASK_STATE_DESC='Setup fail'

  here Every cycle will have 10 tasks (i.e., 1,2 ... 10)

  -- Expected output.

  --- from cycle1

  2212Error

  2213Error

  2214Setup Fail

  2215Setup Fail

  2216Error

  2217Setup Fail

  2218Error

  2219Error

  22110Setup Fail

  -- from cycle2

  132223Error

  162226Error

  172227Setup Fail

  182228Error

  192229Error

  2022210Setup Fail

  in the above list the task_id 5 have to miss in select list becasue the previous task not having Error as TASK_STATE_DESC

  and task_id 4 also should miss because it is having Null as TASK_STATE_DESC

  -- from cycle 3

  2233Error

  2236Error

  2237Setup Fail

  2238Error

  2239Error

  22310Setup Fail

  -- the above select

  in the above list the task_id 5 have to miss in select list becasue the previous task not having Error as TASK_STATE_DESC

  and task_id 2,4 also should miss because it is having Null as TASK_STATE_DESC

  Please help me

  Thanks,

• Andy Hyslop

  SSCrazy Eights

  Points: 9198

  More actions

  May 28, 2012 at 5:03 am

  #1493731

  Hi

  Does this help?

  CREATE TABLE #SAMPLE

  (

  SOURCE_FILE_ID INT,

  CYCLE_ID INT,

  TASK_ID INT ,

  TASK_STATE_DESC VARCHAR(100)

  )

  INSERT INTO #SAMPLE

  SELECT 22,1,1,'Valid' UNION ALL

  SELECT 22,1,2,'Error' UNION ALL

  SELECT 22,1,3,'Error' UNION ALL

  SELECT 22,1,4,'Setup Fail' UNION ALL

  SELECT 22,1,5,'Setup Fail' UNION ALL

  SELECT 22,1,6,'Error' UNION ALL

  SELECT 22,1,7,'Setup Fail' UNION ALL

  SELECT 22,1,8,'Error' UNION ALL

  SELECT 22,1,9,'Error' UNION ALL

  SELECT 22,1,10,'Setup Fail' UNION ALL

  SELECT 22,2,1,'Valid' UNION ALL

  SELECT 22,2,2,null UNION ALL

  SELECT 22,2,3,'Error' UNION ALL

  SELECT 22,2,4,null UNION ALL

  SELECT 22,2,5,'Setup Fail' UNION ALL

  SELECT 22,2,6,'Error' UNION ALL

  SELECT 22,2,7,'Setup Fail' UNION ALL

  SELECT 22,2,8,'Error' UNION ALL

  SELECT 22,2,9,'Error' UNION ALL

  SELECT 22,2,10,'Setup Fail' UNION ALL

  SELECT 22,3,1,'Valid' UNION ALL

  SELECT 22,3,2,null UNION ALL

  SELECT 22,3,3,'Error' UNION ALL

  SELECT 22,3,4,null UNION ALL

  SELECT 22,3,5,'Setup Fail' UNION ALL

  SELECT 22,3,6,'Error' UNION ALL

  SELECT 22,3,7,'Setup Fail' UNION ALL

  SELECT 22,3,8,'Error' UNION ALL

  SELECT 22,3,9,'Error' UNION ALL

  SELECT 22,3,10,'Setup Fail'

  SELECT *

  FROM #SAMPLE

  where

  (TASK_STATE_DESC = 'Error' OR TASK_STATE_DESC = 'Setup Fail')

  AND CYCLE_ID = 1

  ORDER BY

  #SAMPLE.CYCLE_ID

  DROP TABLE #SAMPLE

  Output Cycle 1

  SOURCE_FILE_IDCYCLE_IDTASK_IDTASK_STATE_DESC

  2212Error

  2213Error

  2214Setup Fail

  2215Setup Fail

  2216Error

  2217Setup Fail

  2218Error

  2219Error

  22110Setup Fail

  Andy

  ==========================================================================================================================
  A computer lets you make more mistakes faster than any invention in human history - with the possible exceptions of handguns and tequila. Mitch Ratcliffe

• imex

  Ten Centuries

  Points: 1234

  More actions

  May 28, 2012 at 5:54 am

  #1493750

  Hi,

  Try:

  with CTE as

  (

  select a.CycleID, min(a.TASK_ID) as Task_ID

  from MyTable as a

  join MyTable as b on (b.CycleID = a.CycleID) and

  (b.Task_ID = a.Task_ID + 1)

  where (a.TASK_STATE_DESC = 'Error') and

  (b.TASK_STATE_DESC = 'Setup fail')

  )

  select t.*

  from MyTable as t

  join CTE as c on (c.CycleID = t.CycleID)

  where t.Task_ID >= c.Task_ID

  Hope this helps.

  https://www.imoveisemexposicao.com.br/imobiliarias-em-guarulhos

• ChrisM@Work

  SSC Guru

  Points: 186127

  More actions

  May 28, 2012 at 6:35 am

  #1493768

  SELECT a.SOURCE_FILE_ID, a.CYCLE_ID, a.TASK_ID, a.TASK_STATE_DESC

  FROM #Sample a

  LEFT JOIN #Sample b

  ON b.SOURCE_FILE_ID = a.SOURCE_FILE_ID

  AND b.CYCLE_ID = a.CYCLE_ID

  AND b.TASK_STATE_DESC = 'Error' AND a.TASK_STATE_DESC = 'Setup Fail'

  AND b.TASK_ID+1 = a.TASK_ID

  WHERE a.TASK_STATE_DESC = 'Error'

  OR b.TASK_STATE_DESC IS NOT NULL

  ^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

  For fast, accurate and documented assistance in answering your questions, please read this article.
  Understanding and using APPLY, (I) and (II) Paul White
  Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden