extract distinct information and order the results

Hi,

MSSQL 2000 T-SQL

I have a problem in extracting information pertaing to a key value and matching that key value to another transaction but the order is based on another value in the same row.

I've attached a sample of DB data below.

tran_nr ret_ref_no msg_type description

5111 12345 420 reversal
5112 12345 200 auths
5113 15236 200 auths
5114 46587 200 auths
5115 46587 420 reversal

Requirement using the above data is to extract data where the ret_ref_no is the same for more than one row but also check that the msg_type 420 happens before the 200. Is there a way of retrieving the information in this way using the tran_nr coloumn values? The tran_nr values is basically the serial number when the transaction is wrriten away to the DB.

I've managed only to retrive the 1st half of my query whereby the same ret_ref_nr is being used by more then one transaction. Still need to figure out the 2nd part where the msg_type of 420 happens before the 200.

SELECT * FROM SAMPLE

WHERE ret_ref_no in
(
SELECT ret_ref_no FROM SAMPLE
GROUP BY ret_ref_no HAVING COUNT(*) > 1
)

Results of query

5111 12345 420 reversal
5112 12345 200 auths
5114 46587 200 auths
5115 46587 420 reversal

If someone could assist with only retreiving the above results in bold to the query analyser i will really appreciate it.

Regards

DeceptiveThe requirement "msg_type 420 happens before the 200" can be interpreted in more than one way. Did you mean that both 420 and 200 must exist and 420 must have a lower tran_nr. Or did you mean that if 200 exists for ret_ref_no , then it must be rpecede by 420?|||

Hi AKuz,

Your first thought was correct. Thats exactly what i require.

The requirement "msg_type 420 happens before the 200" can be interpreted in more than one way. Did you mean that

both 420 and 200 must exist and 420 must have a lower tran_nr.

Deceptive

|||

The possible query,

Assumption : The tran_nr are inserted in sequence. Suggestion, instead of using the id use the timestamp.

Code Snippet

Create Table #data (

[tran_nr] int ,

[ret_ref_no] int ,

[msg_type] int ,

[description] Varchar(100)

);

Insert Into #data Values('5111','12345','420','reversal');

Insert Into #data Values('5112','12345','200','auths');

Insert Into #data Values('5113','15236','200','auths');

Insert Into #data Values('5114','46587','200','auths');

Insert Into #data Values('5115','46587','420','reversal');

select * from #data main

where exists

(

select l.ret_ref_no from #data l

inner join #data r

on l.ret_ref_no = r.ret_ref_no

and l.tran_nr < r.tran_nr

where

l.msg_type='420'

and r.msg_type = '200'

and main.ret_ref_no = l.ret_ref_no

)

|||

Here are two more possible solutions. Thanks to Manivannan for the DDL and sample data.

Code Snippet

select

*

from

#data as a

where

exists (

select

*

from

#data as b

where

b.[ret_ref_no] = a.[ret_ref_no]

and b.[tran_nr] < a.[tran_nr]

and b.[msg_type] = 420 and a.[msg_type] = 200

)

or exists (

select

*

from

#data as b

where

b.[ret_ref_no] = a.[ret_ref_no]

and b.[tran_nr] > a.[tran_nr]

and b.[msg_type] = 200 and a.[msg_type] = 420

)

select

*

from

#data

where

[ret_ref_no] in (

select

a.[ret_ref_no]

from

#data as a

where

a.[msg_type] in (200, 420)

group by

a.[ret_ref_no]

having

min(case when a.[msg_type] = 200 then a.[tran_nr] end) > min(case when a.[msg_type] = 420 then a.[tran_nr] end)

)

AMB

|||Thanks Manivannan.D.Sekaran for your assistance.|||Thank you too hunchback |||

hi Manivannan

Looks like i have another issue that i need help with. I've i add a coloumn rsp_code to the database. This coloumn will contain two degit response codes consisting of values like 00, 25,91,01,05 etc. Each transaction will have its own rsp_code value.

Question:-

Together with the SQL already provided is it posible to only retrive the records where the rsp_code = '25' for the 420 and the rsp_code = '00' for the 200 transactions?

Thanks

Deceptive