Solved

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

Posted on 2014-12-15
7
142 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 47

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 33

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
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
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 47

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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
SQL Server engine let you use a Windows account or a SQL Server account to connect to a SQL Server instance. This can be configured immediatly during the SQL Server installation or after in the Server Authentication section in the Server properties …
Along with being a a promotional video for my three-day Annielytics Dashboard Seminor, this Micro Tutorial is an intro to Google Analytics API data.

809 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