Need help in performing query..

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Need help in performing query..

Author

Topic

shield21
Starting Member

3 Posts

Posted - 2009-11-29 : 23:03:58

Good day SQL experts! I am a new member here..

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

I really need this one.. Any help would be greatly appreciated..

Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-11-30 : 13:09:06

can you explain how you get record with id 4 in output? isnt state again changing from 1->3->4->3 ? or are you concerned only on cyclic ones?

shield21
Starting Member

3 Posts

Posted - 2009-11-30 : 18:36:46

Yes you're correct.. The id 4 would be displayed but the succeeding records (id 5 and 6) should not be displayed to avoid the cycle.. Here are the states which record_id 1 undergoes 0->1->3..

No, I'm not only concerned with the cyclic one.. This one should also be taken into consideration:

id | record_id| date_changed | old_state | new_state |
7 | 3 | 2009-01-01 | 0 | 1 |
8 | 3 | 2009-01-01 | 1 | 2 | not displayed
9 | 3 | 2009-01-01 | 2 | 3 | not displayed
10 | 3 | 2009-01-01 | 3 | 4 | not displayed
11 | 3 | 2009-01-01 | 4 | 1 | not displayed
The result will be
id | record_id| date_changed | old_state | new_state |
7 | 3 | 2009-01-01 | 0 | 1 |
because state 1 is already recorded for id 7.. Those in between id > 7 and <=11 will not be displayed since state 1 (new_state) was repeated (id 7 and 11).. 0->1 only.. There should be unique new_state for each record_id given that they have the same date_changed..

Thanks!

shield21
Starting Member

3 Posts

Posted - 2009-11-30 : 18:38:14

I forgot.. Here's the code 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 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 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,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

SELECT * FROM #table

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources