How to manipulate records using SQL

[santoshsri]

Hi,

I am writing a SQL code for a report. My problem is something like this :
there and event_id in a table.. that is having values in order 1,2,3,4,5 .. and so on .. if there is any event ids for a customer are 1 and 81 , I will have to show only 81th record and for 1, 91 .. I will have to show only 91th record and so on .. and if a particular customer is not having any even id in 8X or 9X series then I will have to show exact data ..

Here is an example :

Cust 1
event_id--City---State--- zip
1 -- abc --- def -- 22804
2 --------- xyz --- xab -- 22564
81 --------- abc -- hsh-- 22222

The expected output should be :
event_id--City---State--- zip
2 --------- xyz --- xab -- 22564
81 --------- abc -- hsh-- 22222

Cust 2
1 -- abc --- def -- 22804
2 --------- xyz --- xab -- 22564

The expected output should be :
event_id--City---State--- zip
1 -- abc --- def -- 22804
2 --------- xyz --- xab -- 22564

Please help me out in this how can I handle this using SQL.

[r035198x]

Hi,

I am writing a SQL code for a report. My problem is something like this :
there and event_id in a table.. that is having values in order 1,2,3,4,5 .. and so on .. if there is any event ids for a customer are 1 and 81 , I will have to show only 81th record and for 1, 91 .. I will have to show only 91th record and so on .. and if a particular customer is not having any even id in 8X or 9X series then I will have to show exact data ..

Here is an example :

Cust 1
event_id--City---State--- zip
1 -- abc --- def -- 22804
2 --------- xyz --- xab -- 22564
81 --------- abc -- hsh-- 22222

The expected output should be :
event_id--City---State--- zip
2 --------- xyz --- xab -- 22564
81 --------- abc -- hsh-- 22222

Cust 2
1 -- abc --- def -- 22804
2 --------- xyz --- xab -- 22564

The expected output should be :
event_id--City---State--- zip
1 -- abc --- def -- 22804
2 --------- xyz --- xab -- 22564

Please help me out in this how can I handle this using SQL.

You description is still vague.
1.)In your first example you have IDs 1 and 81 present and you showed data for IDs 81 and 2 but you said "if there is any event ids for a customer are 1 and 81 , I will have to show only 81th record".

2.) You said "and if a particular customer is not having any even id in 8X or 9X series then I will have to show exact data ..". By 8X do you mean (81, 82, ..89, 801, 802, ...,809, ... )?

[r035198x]

You description is still vague.
1.)In your first example you have IDs 1 and 81 present and you showed data for IDs 81 and 2 but you said "if there is any event ids for a customer are 1 and 81 , I will have to show only 81th record".

2.) You said "and if a particular customer is not having any even id in 8X or 9X series then I will have to show exact data ..". By 8X do you mean (81, 82, ..89, 801, 802, ...,809, ... )?

Hi, you used the report button instead of the reply button. Can you post your responses to the two questions I've asked bove?

[santoshsri]

Hi ,
Here is my problem description ..

Below is a sample data of the table.

IBPSUB IBPQTN CLIENT_INFORMAT ION CLASS ASSESTS
18783 1 Echo Test 1 - Client A 730 100000
18783 2 Echo Test 1 - Client A 730 120000
18784 1 Echo Test 1 - Client B 853 130000
18785 1 Echo Test 1 - Client C 853 100000
18785 81 Echo Test 1 - Client C 854 100000


Primary keys : IBPSUB and IBPQTN

My problem is : Whenever a Record having a particular IBPSUB has a IBPQTN number = 80 + IBPQTN , then I will have display the only record having greater IBPQTN value ..


My expected output from above table should be :

IBPSUB IBPQTN CLIENT_INFORMAT ION CLASS ASSESTS
18783 1 Echo Test 1 - Client A 730 100000
18783 2 Echo Test 1 - Client A 730 120000
18784 1 Echo Test 1 - Client B 853 130000
18785 81 Echo Test 1 - Client C 854 100000

IBPSUB 18785 has IBPQTN #1 and 81 ... since this IBPSUB had 1 and 81 in problem so the result should display only 81 not 1.

That's my problem ..

Please help me

Thanks