Finding Records that match but dollars are different 1

[tpbjr]

Hi All,

I have a table with year, quarter, item, and four different dollar fields.

I am trying to build a query that will display records that have year, quarter and the first 8 out of 16 positions of the item field that match but only if at least one of the dollar fields are different.

Example:
Year Qtr Item $1 $2 $3 $4
=======================================================
2006 1 abc 99997 10 2.5 3.5 5.5
2006 1 abc 99999 5 2.5 3.5 5.5

2006 2 zyz 99997 2.3 5.5 2.5 3.5
2006 2 zyz 99999 2.3 5.5 2.1 3.5

The first and second group of records above should return from the query that I desire. However something like below should not be returned because the dollars match.

Example:
Year Qtr Item $1 $2 $3 $4
=======================================================
2006 1 abc 99997 10 2.5 3.5 5.5
2006 1 abc 99999 10 2.5 3.5 5.5

2006 2 zyz 99997 2.3 5.5 2.5 3.5
2006 2 zyz 99999 2.3 5.5 2.5 3.5

I think I need some kind of sub query since I am querying on the same table to try to match the year, qtr and item (first 8 character only) as the key.

Is my issue clear as mud?

I really appreciate the help.



Thank you for all your help

http://www.besware.com

Tom

 

[pabowen]

Hey Tom,

I had to change some of the field names a bit, but you should get the point. Basically you need to perform a self join.

try this...

select distinct a.yr, a.qtr, a.item, a.code, a.one, a.two, a.three, a.four
from
test a
inner join test b on a.yr = b.yr and a.qtr = b.qtr and a.item = b.item
where a.one <> b.one or a.two <> b.two or a.three <> b.three or a.four <> b.four

Please let me know how it works.
Patrick

 

[bborissov]

I use Patric's example with some modifications (Too lazy to write new one ))

select distinct yr, qtr, LEFT(item,8) AS Item, code, one, two, three, four
from MyTable

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[tpbjr]

I wanted thank everyone for your support but I wanted to give a special thank you to Pabowen. Your idea narrowed down over 93,000 rows to 350 rows.


Thank you for all your help

http://www.besware.com

Tom

 

[pabowen]

Thanks, I appreciate the positive comments.