Check values by sequence number

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)

Check values by sequence number

Author

Topic

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-06-19 : 15:31:17

Hi All,

Could you please help me with the query.
I have a table [Sequence] with two fields: Code and SequenceNumber
I need to check if Code corresponding to the next SequenceNumber is valid,
i.e. is 'a73' having SequenceNumber=2 is valid to be the next code after 'b34' with SequenceNumber=1
List of all valid codes is in the table [NextAvailableCode]

CREATE TABLE [dbo].[Sequence]
(
[Code] VARCHAR(3) not null,
[SequenceNumber] [int] not null
)
INSERT INTO Sequence (ClientID,Code,SequenceNumber) VALUES ('b34',1)
INSERT INTO Sequence (ClientID,Code,SequenceNumber) VALUES ('a73',2)
INSERT INTO Sequence (ClientID,Code,SequenceNumber) VALUES ('a12',3)
INSERT INTO Sequence (ClientID,Code,SequenceNumber) VALUES ('g32',4)
INSERT INTO Sequence (ClientID,Code,SequenceNumber) VALUES ('h28',5)

CREATE TABLE [dbo].[NextAvailableCode]
(
[Code] VARCHAR(3) not null,
[NextValidCode] VARCHAR(400) null
)
INSERT INTO NextAvailableCode (Code,NextValidCode) VALUES ('a12', 'a28,b02,b34,c93,c56,d23,f45,h23')
INSERT INTO NextAvailableCode (Code,NextValidCode) VALUES ('a28', 'b02,s72,b34,c93,c56,e29,f45,r34')
INSERT INTO NextAvailableCode (Code,NextValidCode) VALUES ('b34', 'b12,c93,c56,e29,a73,r34,g32')
INSERT INTO NextAvailableCode (Code,NextValidCode) VALUES ('a73', 'h28,d45,a12,h56,s23,d94,y13,h23')
INSERT INTO NextAvailableCode (Code,NextValidCode) VALUES ('f47', 'h28,d45,g11,h56,j20,k22,y13,a28,r96')
INSERT INTO NextAvailableCode (Code,NextValidCode) VALUES ('g32', 'j31,s65,a12,h28,b02,e29')

Expected results
'b34': null
'a73': true
'a12': true
'g32': false
'h28': true

TIA.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-06-19 : 17:30:12

I think you need to clarify the expected output.

Peter Larsson
Helsingborg, Sweden

PurpleSun
Yak Posting Veteran

50 Posts

Posted - 2007-06-20 : 11:03:37

You're right, I should have explain it.
The actual output should be True or False.
If every row returns true output should be true, otherwise output should be false.
In the example above output should be false.
Based on the output I should do some actions in the other tables.

Thanks.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources