Need Help to obtain records.

Question

jovillanuev 0 Junior Poster in Training

13 Years Ago

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

mssql

Edited 13 Years Ago by jovillanuev because: n/a

3 Contributors
8 Replies
156 Views
1 Day Discussion Span
Latest Post 13 Years Ago Latest Post by jovillanuev

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

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

Edited 13 Years Ago by urtrivedi because: n/a

urtrivedi 276 Nearly a Posting Virtuoso

13 Years Ago

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

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jovillanuev 0 Junior Poster in Training · Answer 1 · 2011-07-07T10:58:49+00:00

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

jovillanuev 0 Junior Poster in Training · Answer 2 · 2011-07-07T13:51:57+00:00

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 239 Master Poster · Answer 3 · 2011-07-07T23:31:54+00:00

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 276 Nearly a Posting Virtuoso · Answer 4 · 2011-07-08T09:56:54+00:00

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 0 Junior Poster in Training · Answer 5 · 2011-07-08T13:13:24+00:00

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 0 Junior Poster in Training · Answer 6 · 2011-07-08T13:14:28+00:00

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