SQL Query to find groups of records with approximately same value in numeric field

Hi,

Using the sample data below, I am trying to build a query that returns all records from the VENDOR table where:

 There are 2 or more records in the VENDOR table with a UNIT_COST that is up to 3% higher or lower than any other records.

The VEND_NO in the ITEM table matches the VEND_NO in the VENDOR table

The records in the VENDOR table have the same corresponding BRAND and SIZE values in the ITEM table.

In plain English, I am looking for cost records, in VENDOR,  for items with the same brand, size, and approximately the same cost, regardless of what vendor we have them assigned to.   (The vendor assignment being the value for VEND_NO in the item table.)

The query should return ITEM_NO's '1000001609' and '1000001610' as their assigned vendor costs are 10.8 and 10.7.

The query should not return ITEM_NO's '1000001612' or '1000001613' as their assigned vendor costs are 15.2 and 14.7, which is more than a 3% variance.

DECLARE @ ITEM
(ITEM_NO VARCHAR(10) NOT NULL,
BRAND VARCHAR(10) NOT NULL,
SIZE VARCHAR(10) NOT NULL,
VEND_NO VARCHAR(10) NOT NULL)
  
DECLARE @ VENDOR
(ITEM_NO VARCHAR(10) NOT NULL,
VEND_NO VARCHAR(10) NOT NULL,
UNIT_COST VARCHAR(10) NOT NULL)

INSERT @ITEM
(ITEM_NO,BRAND,SIZE,VEND_NO,)
VALUES
('1000001609','AMYS','12 OZ','NATUBEST'),
('1000001610','AMYS','12 OZ','UNITNATU'),
('1000001611','AMYS','14 OZ','UNITNATU'),
('1000001612','BEARITOS','10 OZ','NATUBEST'),
('1000001613','BEARITOS','10 OZ','UNITNATU'),
('1000001614','BEARITOS','10 OZ','NATUBEST'),
('1000001615','CADIA','10 OZ','NATUBEST'),
('1000001616','CADIA','10 OZ','NATUBEST'),
('1000001617','CADIA','12 OZ','NATUBEST'),
('1000001618','CADIA','12 OZ','NATUBEST'),
('1000001619','CADIA','9 OZ','NATUBEST')

INSERT @VENDOR
(ITEM_NO,VEND_NO,UNIT_COST)
VALUES
('1000001609','NATUBEST','10.8'),
('1000001609','UNITNATU','10.5'),
('1000001610','NATUBEST','10.8'),
('1000001610','UNITNATU','10.7'),
('1000001611','NATUBEST','11.9'),
('1000001611','UNITNATU','11.8'),
('1000001612','NATUBEST','15.2'),
('1000001612','UNITNATU','15.1'),
('1000001613','NATUBEST','15.2'),
('1000001613','UNITNATU','14.7'),
('1000001614','NATUBEST','14.6'),
('1000001614','UNITNATU','14.5'),
('1000001615','NATUBEST','12.7'),
('1000001615','UNITNATU','13.5'),
('1000001616','NATUBEST','12.7'),
('1000001616','UNITNATU','12.7'),
('1000001617','NATUBEST','10.9'),
('1000001617','UNITNATU','10.8'),
('1000001618','NATUBEST','10.9'),
('1000001618','UNITNATU','10.8'),
('1000001619','NATUBEST','8.5'),
('1000001619','UNITNATU','8.4')

Open in new window

AD1080Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
I needed to use CAST function since in your code the fields are all VARCHAR(10):
SELECT *
FROM @vendor v
WHERE EXISTS (SELECT 1
			FROM @vendor v2
			WHERE v.vend_no<>v2.vend_no AND v.item_no=v2.item_no
			 AND CAST(v.unit_cost AS FLOAT) BETWEEN CAST(v2.unit_cost AS FLOAT)*0.97 AND CAST(v2.unit_cost AS FLOAT)*1.03)

Open in new window

0
ste5anSenior DeveloperCommented:
Not sure, what you exactly want. E.g.

DECLARE @ITEM TABLE
    (
      ITEM_NO VARCHAR(10) NOT NULL ,
      BRAND VARCHAR(10) NOT NULL ,
      SIZE VARCHAR(10) NOT NULL ,
      VEND_NO VARCHAR(10) NOT NULL
    );
  
DECLARE @VENDOR TABLE
    (
      ITEM_NO VARCHAR(10) NOT NULL ,
      VEND_NO VARCHAR(10) NOT NULL ,
      UNIT_COST FLOAT NOT NULL
    );

INSERT  @ITEM
        ( ITEM_NO, BRAND, SIZE, VEND_NO )
VALUES  ( '1000001609', 'AMYS', '12 OZ', 'NATUBEST' ),
        ( '1000001610', 'AMYS', '12 OZ', 'UNITNATU' ),
        ( '1000001611', 'AMYS', '14 OZ', 'UNITNATU' ),
        ( '1000001612', 'BEARITOS', '10 OZ', 'NATUBEST' ),
        ( '1000001613', 'BEARITOS', '10 OZ', 'UNITNATU' ),
        ( '1000001614', 'BEARITOS', '10 OZ', 'NATUBEST' ),
        ( '1000001615', 'CADIA', '10 OZ', 'NATUBEST' ),
        ( '1000001616', 'CADIA', '10 OZ', 'NATUBEST' ),
        ( '1000001617', 'CADIA', '12 OZ', 'NATUBEST' ),
        ( '1000001618', 'CADIA', '12 OZ', 'NATUBEST' ),
        ( '1000001619', 'CADIA', '9 OZ', 'NATUBEST' );

INSERT  @VENDOR
        ( ITEM_NO, VEND_NO, UNIT_COST )
VALUES  ( '1000001609', 'NATUBEST', 10.8 ),
        ( '1000001609', 'UNITNATU', 10.5 ),
        ( '1000001610', 'NATUBEST', 10.8 ),
        ( '1000001610', 'UNITNATU', 10.7 ),
        ( '1000001611', 'NATUBEST', 11.9 ),
        ( '1000001611', 'UNITNATU', 11.8 ),
        ( '1000001612', 'NATUBEST', 15.2 ),
        ( '1000001612', 'UNITNATU', 15.1 ),
        ( '1000001613', 'NATUBEST', 15.2 ),
        ( '1000001613', 'UNITNATU', 14.7 ),
        ( '1000001614', 'NATUBEST', 14.6 ),
        ( '1000001614', 'UNITNATU', 14.5 ),
        ( '1000001615', 'NATUBEST', 12.7 ),
        ( '1000001615', 'UNITNATU', 13.5 ),
        ( '1000001616', 'NATUBEST', 12.7 ),
        ( '1000001616', 'UNITNATU', 12.7 ),
        ( '1000001617', 'NATUBEST', 10.9 ),
        ( '1000001617', 'UNITNATU', 10.8 ),
        ( '1000001618', 'NATUBEST', 10.9 ),
        ( '1000001618', 'UNITNATU', 10.8 ),
        ( '1000001619', 'NATUBEST', 8.5 ),
        ( '1000001619', 'UNITNATU', 8.4 );

SELECT  *
FROM    @ITEM I
        LEFT JOIN @VENDOR V ON V.ITEM_NO = I.ITEM_NO
WHERE   ( SELECT    COUNT(*)
          FROM      @VENDOR V2
          WHERE     V2.UNIT_COST BETWEEN V.UNIT_COST * 0.97 AND V.UNIT_COST * 1.03
        ) > 1;

Open in new window

0
AD1080Author Commented:
Hi,

The first solution offered above is returning records that vary by more than 3% in either direction.

The 2nd solution is returning millions of rows.  

Please take another look.   I can provide more sample data if that would help.   In both suggestions, I don't see any reference  to the ITEM.BRAND field.   My goal is to return a list of VENDOR.COST records with same ITEM.BRAND, that have almost the same cost.  

ITEM_NO is the primary key for the ITEM table.   There can be any number of records in VENDOR.  The primary key for VENDOR is a combination of ITEM_NO and VEND_NO.

The result set should only contain VENDOR records where the ITEM.VEND_NO matches the VENDOR.VEND_NO field.  

Thanks in advance, and let me know if I can clarify further.
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
How about:

SELECT Brand, Size, Min(cast(Unit_Cost as float)) as MinUnitCost, Max(cast(Unit_Cost as float)) as MaxUnitCost
FROM @vendor v
join @ITEM i on v.ITEM_NO = i.ITEM_NO
group by Brand, Size
having Max(cast(Unit_Cost as float))<=Min(cast(Unit_Cost as float))*1.03

Open in new window


This gives the result of:

Brand      Size       MinUnitCost            MaxUnitCost
---------- ---------- ---------------------- ----------------------
AMYS       12 OZ      10.5                   10.8
AMYS       14 OZ      11.8                   11.9
CADIA      12 OZ      10.8                   10.9
CADIA      9 OZ       8.4                    8.5

Open in new window

0
Vitor MontalvãoMSSQL Senior EngineerCommented:
The first solution offered above is returning records that vary by more than 3% in either direction.
Can you give an example? I tested with the data you provided and looked ok to me.


In both suggestions, I don't see any reference  to the ITEM.BRAND field.   My goal is to return a list of VENDOR.COST records with same ITEM.BRAND,
There's no need for that since you didn't request to return the Brand and your statement was "The VEND_NO in the ITEM table matches the VEND_NO in the VENDOR table", so you have all data that it needed in the VENDOR table.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
AD1080Author Commented:
Hi Vitor,

Thanks for your help.  I think your suggestion is more or less what I need.  

I have adapted it into the following which is more or less working.  I actually wanted to filter out any records for brands that only have one row in the result set, but I can live with this for now.  Appreciate your help.


SELECT V.ITEM_NO, V.UNIT_COST, I.ATTR_COD_2 AS BRAND, I.ATTR_COD_3 AS SIZE, I.DESCR
FROM PO_VEND_ITEM V, IM_ITEM I


WHERE
V.ITEM_NO = I.ITEM_NO AND V.VEND_NO = I.ITEM_VEND_NO AND
I.ATTR_COD_2 <> '' AND
 
	(SELECT COUNT(*)AS BRAND_COUNT FROM PO_VEND_ITEM V2, IM_ITEM I2
	 WHERE CAST(V2.UNIT_COST AS FLOAT) BETWEEN CAST(V.UNIT_COST AS FLOAT) * .97 AND CAST(V.UNIT_COST AS FLOAT) * 1.03 AND
	 V2.ITEM_NO = I2.ITEM_NO AND V2.VEND_NO = I2.ITEM_VEND_NO AND I2.ATTR_COD_2 = I.ATTR_COD_2
	 GROUP BY I2.ATTR_COD_2 HAVING COUNT(I2.ATTR_COD_2) > 1) > 1

ORDER BY ATTR_COD_2, ATTR_COD_3

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.