Solved

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

Posted on 2014-12-15
7
132 Views
Last Modified: 2014-12-16
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

0
Comment
Question by:AD1080
7 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 40499886
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
 
LVL 32

Expert Comment

by:ste5an
ID: 40499894
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
 

Author Comment

by:AD1080
ID: 40501374
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
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40502390
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
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40502556
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
 

Author Comment

by:AD1080
ID: 40503718
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

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Join & Write a Comment

Audit has been really one of the more interesting, most useful, yet difficult to maintain topics in the history of SQL Server. In earlier versions of SQL people had very few options for auditing in SQL Server. It typically meant using SQL Trace …
How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now