How to write a query for this one?

• shield_21

  Ten Centuries

  Points: 1033

  More actions

  November 29, 2009 at 7:42 pm

  #135609

  Good day!

  I just need help in writing query.. I have records in a table below.. The condition would be no records should be displayed if the succeeding records' new_state was repeated from the previous records(new_state) and if is changed in the same date..

  here record_id 1 has gone through the ff states: 0->1->2->1->3->4->3 in the same day.. state 1 was changed to state 2 then back to state 1 again (id 2 & 3 would not be displayed).. same with state 3 (id 5 & 6 would not be displayed)..

  id | record_id| date_changed | old_state | new_state |

  1 | 1 | 2009-01-01 | 0 | 1 | displayed

  2 | 1 | 2009-01-01 | 1 | 2 | not displayed

  3 | 1 | 2009-01-01 | 2 | 1 | not displayed

  4 | 1 | 2009-01-01 | 1 | 3 | displayed

  5 | 1 | 2009-01-01 | 3 | 4 | not displayed

  6 | 1 | 2009-01-01 | 4 | 3 | not displayed

  so the result would display only 2 records for record_id=1..

  id | record_id| date_changed | old_state | new_state |

  1 | 1 | 2009-01-01 | 0 | 1 |

  4 | 1 | 2009-01-01 | 1 | 3 |

  Any help would be greatly appreciated..

  Thanks

  -----------------------------------------------------

  Sorry for that, here's the code which contains the data: (updated: 12/2/2009)

  IF OBJECT_ID('TempDB..#table','U') IS NOT NULL

  DROP TABLE #table

  CREATE TABLE #table

  (

  id INT identity primary key,

  record_id INT,

  date_changed DATETIME,

  old_state INT,

  new_state INT

  )

  INSERT INTO #table(record_id,date_changed,old_state,new_state)

  SELECT 1,'2009-01-01',0,1 UNION ALL --displayed

  SELECT 1,'2009-01-01',1,2 UNION ALL --not displayed

  SELECT 1,'2009-01-01',2,1 UNION ALL --not displayed

  SELECT 1,'2009-01-01',1,3 UNION ALL --displayed

  SELECT 1,'2009-01-01',3,4 UNION ALL --not displayed

  SELECT 1,'2009-01-01',4,3 --not displayed

  INSERT INTO #table(record_id,date_changed,old_state,new_state)

  SELECT 2,'2009-01-01',0,1 UNION ALL --displayed

  SELECT 2,'2009-01-01',1,2 UNION ALL --not displayed

  SELECT 2,'2009-01-01',2,1 UNION ALL --not displayed

  SELECT 2,'2009-01-01',1,2 UNION ALL --displayed

  SELECT 2,'2009-01-01',2,4 UNION ALL --displayed

  SELECT 2,'2009-01-01',4,3 UNION ALL --not displayed

  SELECT 2,'2009-01-01',3,4 --not displayed

  INSERT INTO #table(record_id,date_changed,old_state,new_state)

  SELECT 3,'2009-01-01',0,1 UNION ALL --displayed

  SELECT 3,'2009-01-01',1,2 UNION ALL --not displayed

  SELECT 3,'2009-01-01',2,1 UNION ALL --not displayed

  SELECT 3,'2009-01-01',1,2 UNION ALL --not displayed

  SELECT 3,'2009-01-01',2,3 UNION ALL --not displayed

  SELECT 3,'2009-01-01',3,4 UNION ALL --not displayed

  SELECT 3,'2009-01-01',4,1 --not displayed

  INSERT INTO #table(record_id,date_changed,old_state,new_state)

  SELECT 4,'2009-01-01',0,1 UNION ALL --displayed

  SELECT 4,'2009-01-01',1,2 UNION ALL --displayed

  SELECT 4,'2009-01-01',2,3 UNION ALL --not displayed

  SELECT 4,'2009-01-01',3,2 UNION ALL --not displayed

  SELECT 4,'2009-01-01',2,3 UNION ALL --displayed

  SELECT 4,'2009-01-01',3,4 UNION ALL --not displayed

  SELECT 4,'2009-01-01',4,3 --not displayed

  INSERT INTO #table(record_id,date_changed,old_state,new_state)

  SELECT 5,'2009-01-01',0,1 UNION ALL --displayed

  SELECT 5,'2009-01-01',1,2 UNION ALL --not displayed

  SELECT 5,'2009-01-01',2,3 UNION ALL --not displayed

  SELECT 5,'2009-01-01',3,1 UNION ALL --not displayed

  SELECT 5,'2009-01-01',1,2 UNION ALL --displayed

  SELECT 5,'2009-01-01',2,3 --displayed

  SELECT * FROM #table

  Thanks =)

  -----------------------------------------------------

  Special case (updated:12/2/2009)

  id | record_id| date_changed | old_state | new_state |

  1 | 4 | 2009-01-01 | 0 | 1 | displayed

  2 | 4 | 2009-01-01 | 1 | 2 | displayed

  3 | 4 | 2009-01-01 | 2 | 3 | not displayed

  4 | 4 | 2009-01-01 | 3 | 2 | not displayed

  5 | 4 | 2009-01-01 | 2 | 3 | displayed

  6 | 4 | 2009-01-01 | 3 | 4 | not displayed

  7 | 4 | 2009-01-01 | 4 | 3 | not displayed

  where new_state 3 appears on id 3,5,7.. But id 3 will not be displayed since it is between id 2 and id 4 which have the same new_state 2.. So id 5 is the one that would be displayed to represent new_state 3.. I think the sequence is essential in building the result.. Is there a need for recursive CTE?

  Updated code is placed above..

  Thanks =)

  Is Shortest Path algorithm applicable for this one?

• Bru Medishetty

  SSC-Insane

  Points: 23606

  More actions

  November 29, 2009 at 9:06 pm

  #1085220

  It needs more time to simluate the same data at our end to come up with a script,

  please post the table scripts and some sample records as per the following Best Practices article.

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

  ---

  Bru Medishetty

  Blog -- LearnSQLWithBru

  Join on Facebook Page Facebook.comLearnSQLWithBru

  Twitter -- BruMedishetty

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  November 29, 2009 at 9:16 pm

  #1085222

  Can the following happen? If so, what do you actually want displayed?

  id | record_id| date_changed | old_state | new_state |

  1 | 1 | 2009-01-01 | 0 | 1 |

  2 | 1 | 2009-01-01 | 1 | 2 | not displayed

  3 | 1 | 2009-01-01 | 2 | 1 | not displayed

  4 | 1 | 2009-01-01 | 1 | 2 |

  5 | 1 | 2009-01-01 | 2 | 4 | not displayed or displayed or????

  6 | 1 | 2009-01-01 | 4 | 3 | not displayed

  7 | 1 | 2009-01-01 | 3 | 4 |

  or... how about this? Can this happen, as well?

  id | record_id| date_changed | old_state | new_state |

  1 | 1 | 2009-01-01 | 0 | 1 |

  2 | 1 | 2009-01-01 | 1 | 2 | not displayed

  3 | 1 | 2009-01-01 | 2 | 1 | not displayed

  4 | 1 | 2009-01-01 | 1 | 2 | not displayed

  5 | 1 | 2009-01-01 | 2 | 3 | not displayed

  6 | 1 | 2009-01-01 | 3 | 4 | not displayed

  7 | 1 | 2009-01-01 | 4 | 1 | not displayed

  --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)
  

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  November 29, 2009 at 9:17 pm

  #1085223

  Also, I see you're brand new here. Take a look at the first link in my signature below. It helps you and us, a lot.

  --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)
  

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  November 29, 2009 at 9:18 pm

  #1085224

  Heh... wilst I was pondering the situation, I see Bru snuck one in on me. 🙂

  --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)
  

• shield_21

  Ten Centuries

  Points: 1033

  More actions

  November 29, 2009 at 10:39 pm

  #1085243

  I apologize for that.. I already posted the table creation script together with sample data.. Thanks!

• shield_21

  Ten Centuries

  Points: 1033

  More actions

  November 29, 2009 at 10:48 pm

  #1085245

  Can the following happen? If so, what do you actually want displayed? Yes id5 would be displayed.. The states that would be included: 0->1->2->4.. Thanks for clarifying..

  id | record_id| date_changed | old_state | new_state |

  1 | 1 | 2009-01-01 | 0 | 1 |

  2 | 1 | 2009-01-01 | 1 | 2 | not displayed

  3 | 1 | 2009-01-01 | 2 | 1 | not displayed

  4 | 1 | 2009-01-01 | 1 | 2 |

  5 | 1 | 2009-01-01 | 2 | 4 | not displayed or displayed or????

  6 | 1 | 2009-01-01 | 4 | 3 | not displayed

  7 | 1 | 2009-01-01 | 3 | 4 |

  or... how about this? Can this happen, as well? Yes, this can happen also.. This is correct only first record will be displayed.. Those records where id>1 and id<=7 will not be displayed..

  id | record_id| date_changed | old_state | new_state |

  1 | 1 | 2009-01-01 | 0 | 1 |

  2 | 1 | 2009-01-01 | 1 | 2 | not displayed

  3 | 1 | 2009-01-01 | 2 | 1 | not displayed

  4 | 1 | 2009-01-01 | 1 | 2 | not displayed

  5 | 1 | 2009-01-01 | 2 | 3 | not displayed

  6 | 1 | 2009-01-01 | 3 | 4 | not displayed

  7 | 1 | 2009-01-01 | 4 | 1 | not displayed

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  November 29, 2009 at 11:10 pm

  #1085250

  Thanks for the update and the code to create the data... now all we need to do is come up with an answer for this. Good problem...

  --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)
  

• shield_21

  Ten Centuries

  Points: 1033

  More actions

  November 29, 2009 at 11:36 pm

  #1085256

  Just a follow up in regarding this scenario (record_id=2 from the code above):

  id | record_id| date_changed | old_state | new_state |

  1 | 1 | 2009-01-01 | 0 | 1 |

  2 | 1 | 2009-01-01 | 1 | 2 | not displayed

  3 | 1 | 2009-01-01 | 2 | 1 | not displayed

  4 | 1 | 2009-01-01 | 1 | 2 |

  5 | 1 | 2009-01-01 | 2 | 4 | not displayed or displayed or????

  6 | 1 | 2009-01-01 | 4 | 3 | not displayed

  7 | 1 | 2009-01-01 | 3 | 4 |

  The last record would not be displayed since state 4 is already repeated(id=5).. For clarity, the output will display:

  id | record_id| date_changed | old_state | new_state |

  1 | 1 | 2009-01-01 | 0 | 1 |

  4 | 1 | 2009-01-01 | 1 | 2 |

  5 | 1 | 2009-01-01 | 2 | 4 |

  I appreciate your help..

• khawaja.irfan

  Newbie

  Points: 5

  More actions

  December 1, 2009 at 9:06 am

  #1085884

  I have used your #table

  select * into #table1 from

  (select record_id, date_changed, min(id) min_id, max(id) max_id, new_state

  from #table B

  group by record_id, date_changed, new_state) a

  select * from

  (

  select *,

  row_number() over(partition by record_id, date_changed, new_state order by id) as rowNumber

  from #table A

  where not exists

  (

  select 1 from #table1 b

  where A.record_id = B.record_id

  and A.date_changed = B.date_changed

  and (A.id > min_id and A.id < max_id)

  )

  ) a

  where rowNumber = 1

• Jeff Moden

  SSC Guru

  Points: 1004406

  More actions

  December 1, 2009 at 11:24 am

  #1085986

  Sorry, Pren... I lost track of this one. Does khawaja.irfan's solution work for you?

  --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)
  

• shield_21

  Ten Centuries

  Points: 1033

  More actions

  December 2, 2009 at 5:18 am

  #1086378

  Thanks khawaja.irfan.. Your query works 100% for the data the I have provided.. Thanks for providing a great solution!:-)

  There is just a special case where the query would not produce expected result.. Example would be this data:

  id | record_id| date_changed | old_state | new_state |

  1 | 4 | 2009-01-01 | 0 | 1 | displayed

  2 | 4 | 2009-01-01 | 1 | 2 | displayed

  3 | 4 | 2009-01-01 | 2 | 3 | not displayed, --this is the current min(id) for new_state 3

  4 | 4 | 2009-01-01 | 3 | 2 | not displayed

  5 | 4 | 2009-01-01 | 2 | 3 | displayed --this should be the min(id) for new_state 3

  6 | 4 | 2009-01-01 | 3 | 4 | not displayed

  7 | 4 | 2009-01-01 | 4 | 3 | not displayed

  where new_state 3 appears on id 3,5,7.. But id 3 will not be displayed, so the min(id) for new_state 3 should be next one (id 5).. I think the sequence matters..

  running your query would result into this one:

  id | record_id| date_changed | old_state | new_state |rowNumber

  1 |4 |2009-01-01 |0|1 |1

  2 |4 |2009-01-01 |1|2 |1

  7 |4 |2009-01-01 |4|3 |1

  Here's the code snippet for table creation:

  IF OBJECT_ID('TempDB..#table','U') IS NOT NULL

  DROP TABLE #table

  CREATE TABLE #table

  (

  id INT identity primary key,

  record_id INT,

  date_changed DATETIME,

  old_state INT,

  new_state INT

  )

  INSERT INTO #table(record_id,date_changed,old_state,new_state)

  SELECT 4,'2009-01-01',0,1 UNION ALL --displayed

  SELECT 4,'2009-01-01',1,2 UNION ALL --displayed

  SELECT 4,'2009-01-01',2,3 UNION ALL --not displayed

  SELECT 4,'2009-01-01',3,2 UNION ALL --not displayed

  SELECT 4,'2009-01-01',2,3 UNION ALL --displayed

  SELECT 4,'2009-01-01',3,4 UNION ALL --not displayed

  SELECT 4,'2009-01-01',4,3 --not displayed

  Now I was thinking if using recursive CTE is appropriate..

  Thanks..

• shield_21

  Ten Centuries

  Points: 1033

  More actions

  December 2, 2009 at 5:25 am

  #1086381

  @jeff Moden.. The solution of khawaja.irfan is great! There's just special case that it would not display the result I need..

  God bless!

  Thanks..

• khawaja.irfan

  Newbie

  Points: 5

  More actions

  December 2, 2009 at 9:37 am

  #1086630

  Just with a very small change i was able to produce the result you wanted

  select * from

  (

  select *,

  row_number() over(partition by record_id, date_changed, new_state order by id) as rowNumber

  from #table A

  where not exists

  (

  select 1 from #table1 b

  where A.record_id = B.record_id

  and A.date_changed = B.date_changed

  and (A.id > min_id and A.id < max_id)

  and A.new_state != B.new_state -- This one is the change from the previous query. this one will prevent grouping on all transitional states

  )

  ) a

  where rowNumber = 1

• shield_21

  Ten Centuries

  Points: 1033

  More actions

  December 3, 2009 at 7:22 am

  #1087063

  khawaja.irfan.. Thanks for updating your query.. The idea of using Row_Number is great!

  However, there are some instances where it would not produce expected result.. In this case,

  id | record_id| date_changed | old_state | new_state |

  1 | 5 | 2009-01-01 | 0 | 1 | displayed

  2 | 5 | 2009-01-01 | 1 | 2 | not displayed

  3 | 5 | 2009-01-01 | 2 | 3 | not displayed

  4 | 5 | 2009-01-01 | 3 | 1 | not displayed

  5 | 5 | 2009-01-01 | 1 | 2 | displayed

  6 | 5 | 2009-01-01 | 2 | 3 | displayed

  Running the query would produce:

  id | record_id| date_changed | old_state | new_state | rowNumber

  1 | 5 | 2009-01-01 | 0 | 1 | 1

  6 | 5 | 2009-01-01 | 2 | 3 | 1

  because id 5 satisfied the where exists condition (id 5 > min_id (3) and < max_id (6) and new_state 2 != new_state 3).. so it was removed..

  Here's the code snippet:

  IF OBJECT_ID('TempDB..#table','U') IS NOT NULL

  DROP TABLE #table

  CREATE TABLE #table

  (

  id INT identity primary key,

  record_id INT,

  date_changed DATETIME,

  old_state INT,

  new_state INT

  )

  INSERT INTO #table(record_id,date_changed,old_state,new_state)

  SELECT 5,'2009-01-01',0,1 UNION ALL --displayed

  SELECT 5,'2009-01-01',1,2 UNION ALL --not displayed

  SELECT 5,'2009-01-01',2,3 UNION ALL --not displayed

  SELECT 5,'2009-01-01',3,1 UNION ALL --not displayed

  SELECT 5,'2009-01-01',1,2 UNION ALL --displayed

  SELECT 5,'2009-01-01',2,3 --displayed

  I really think there's a need to check the previous records to be displayed before progressing on the next one..

  Thanks..