Need Help to obtain records.

[jovillanuev]

Hello Guys,

I need your help to obtain this records.
I could not figure how how to do this in script.
I would like to get the RMA # 3. but the records will
pull out based on the type equal to 2 and date range..

Here is sample data:

ESN|TYPE|DATE|RMA
-----------------
123|1|2011-01-06|Null
123|3|2011-01-07|Null
123|2|2011-01-08|Null
123|11|2011-03-01|[B]3[/B]
123|4|2011-03-17|3

the correct result:
ESN|TYPE|DATE|RMA
-----------------
123|2|2011-01-08|[B]3[/B]


Help is greatly appreciated.

Thank you.

Jonel

[urtrivedi]

on what basis you chose following record for RMA
123|11|2011-03-01|3

[jovillanuev]

on what basis you chose following record for RMA
123|11|2011-03-01|3

Hi urtrivedi,

actually by basis to get this data is using type is equal to 2 but my corncern is how to get the RMA # 3 that should be included in the result. is this possible to use the ESN to get the RMA # 3

[urtrivedi]

You are not getting my question. I will ask in other way round.
why you have not picked ?
123|4|2011-03-17|3

[jovillanuev]

Cannot be. because every type there is value like the # 2 this means Shipment.
so, i will pull the data based on the Shipment which is # 2 the RMA # is NULL. my problem is how could i included in the report the RMA #3.is there any possibility?

actually i understand your question. where type # 11 the RMA value is 3.
well, the value for the type 11 is Returns, I ned the Shipment.

Regards,

Jonel

[adam_k]

Since you are not providing a clear set of rules for your query, I will improvise. You can change the criteria used on your own, or provide a clear set of rules.

select a.ESN, a.TYPE,a.DATE,b.RMA
from tablename a 
left join (select ESN, RMA from tablename 
where RMS IS NOT NULL 
and ESN ='123' 
and type =11) b 
on a.ESN = b.ESN
where ESN = '123'
and type = 2

[urtrivedi]

adam_k's query will work well (for ESN 123). Here I am posting it without subquery and for all ESN

select a.ESN, a.TYPE,a.DATE,b.RMA
from tablename a 
left join tablename b on a.ESN=b.ESN and b.TYPE=11
where a.type = 2

[jovillanuev]

Since you are not providing a clear set of rules for your query, I will improvise. You can change the criteria used on your own, or provide a clear set of rules.

select a.ESN, a.TYPE,a.DATE,b.RMA
from tablename a 
left join (select ESN, RMA from tablename 
where RMS IS NOT NULL 
and ESN ='123' 
and type =11) b 
on a.ESN = b.ESN
where ESN = '123'
and type = 2

Hi Adam_K.

Thank you very much. you got it. Thumbs up.

Regards,

Jonel

[jovillanuev]

adam_k's query will work well (for ESN 123). Here I am posting it without subquery and for all ESN

select a.ESN, a.TYPE,a.DATE,b.RMA
from tablename a 
left join tablename b on a.ESN=b.ESN and b.TYPE=11
where a.type = 2

Hi urtrivedi,

Thank you very much. Thumbs up for you.

Regards,

Jonel