Getting differing values based on 3 criteria-CodePudding

Looked for an answer on this for a while and not quite sure how to ask it, much less answer it. I have a setup like the one below:

warehouse	company	charge code	date	price	other data
1	comp 1	boxes	2022-1-1	3.00	blah blah
2	comp 1	bags	2022-1-1	1.00	blah blah
3	comp 1	bag2	2022-2-5	1.00	blah blah
1	comp 2	boxes	2022-1-1	3.00	blah blah
2	comp 2	bags	2022-1-1	1.50	blah blah
3	comp 2	bag2	2022-2-5	2.00	blah blah

I am trying to make a query that will get me the prices that are different compared to the other companies in the same warehouse with the same charge code. For example, if it were to be run on the table above, it would result in

warehouse	company	charge code	date	price	other data
2	comp 1	bags	2022-1-1	1.00	blah blah
2	comp 2	bags	2022-1-1	1.50	blah blah
3	comp 1	bag2	2022-2-5	1.00	blah blah
3	comp 2	bag2	2022-2-5	2.00	blah blah

Since the box prices were the same for both companies in the same warehouse, they would be removed.

My code is

SELECT * FROM
(
    WITH subquery AS (***LARGE IRRELEVANT SUBQUERY***) 

    SELECT distinct
        warehouse, company, charge_code, date, price, other1, other2
    FROM
        subquery

    WHERE(price) 
        IN 
           (SELECT distinct i1.price 
            FROM M_CHG_DATE_D i1 join M_CHG_DATE_D i2
            ON i1.charge_code = i2.charge_code AND
               i2.warehouse = i2.warehouse AND
               i1.company != i2.company)

    AND (warehouse, company, charge_code, date) 
        IN
           (SELECT warehouse, company, charge_code, MAX(date) 
            FROM subquery
            GROUP BY warehouse, company, charge_code)
)

WHERE company IN 
    ('comp1', 'comp2', 'comp3', ... , 'comp n')                  
AND 
    warehouse NOT IN('list of warehouses')
ORDER BY company, charge_code, warehouse

Currently, instances where the companies have the same price in the same warehouse for the same charge code are not being filtered out. I would appreciate any help. Thanks.

update with actual data from the table:

warehouse	company	charge code	date	price	other data
C1	GEN	BB	2022-2-5	.032	the same
C1	MUL	BB	2022-2-5	.032	the same
C1	RAV	BB	2022-1-1	.0476	the same
C1	RMF	BB	2022-1-1	.0476	the same
C2	BAM	BB	2022-1-1	.0553	the same
C2	BUM	BB	2022-1-1	.0553	the same

which should result in

warehouse	company	charge code	date	price	other data
C1	GEN	BB	2022-2-5	.032	the same
C1	MUL	BB	2022-2-5	.032	the same
C1	RAV	BB	2022-1-1	.0476	the same
C1	RMF	BB	2022-1-1	.0476	the same

CodePudding user response：

Use a subquery to determine which combinations of warehouse and charge code has more than one price, and fetch the matching rows:

select warehouse, company, charge_code, chg_date, price, other1, other2
from m_chg_date_d
where (warehouse, charge_code) in
  (
    select warehouse, charge_code
    from m_chg_date_d
    group by warehouse, charge_code
    having count(distinct price) > 1
  );

The query assumes that (warehouse, company, charge_code) is unique, that is to say, that no company has the same charge code more than once in the same warehouse. This also means that there is no need to use distinct in the outermost query, since all rows must be unique when the result includes warehouse, company and charge code.

In the query above, I renamed the date column to chg_date, as date is a reserved word in Oracle.

CodePudding user response：

Or, create an in-line grouping table to join the original table back with ; I don't know if the date column (I called it dt to avoid conflicts with reserved words) must or must not be part of the grouping criteria - works both ways ...

WITH
  2 -- your input , don't use in final query
  3 indata(warehouse,company,charge_code,dt,price,other_data) AS (
  4           SELECT 1,'comp 1','boxes',DATE '2022-1-1',3.00,'blah blah'
  5 UNION ALL SELECT 2,'comp 1','bags' ,DATE '2022-1-1',1.00,'blah blah'
  6 UNION ALL SELECT 3,'comp 1','bag2' ,DATE '2022-2-5',1.00,'blah blah'
  7 UNION ALL SELECT 1,'comp 2','boxes',DATE '2022-1-1',3.00,'blah blah'
  8 UNION ALL SELECT 2,'comp 2','bags' ,DATE '2022-1-1',1.50,'blah blah'
  9 UNION ALL SELECT 3,'comp 2','bag2' ,DATE '2022-2-5',2.00,'blah blah'
 10 )
 11 -- real query starts here - replace following comma with "WITH"                                                                                                                                       
 12 ,
 13 select_criteria AS (
 14   SELECT
 15     warehouse
 16   , charge_code
 17   , dt
 18   , COUNT(*) AS itemcount 
 19   , COUNT(DISTINCT price) AS pricecount
 20   FROM indata
 21   GROUP BY
 22     warehouse
 23   , charge_code
 24   , dt
 25   HAVING pricecount = itemcount
 26 )
 27 SELECT
 28   indata.*
 29 FROM indata
 30 JOIN select_criteria USING (
 31     warehouse
 32   , charge_code
 33   , dt
 34 )
 35 ORDER BY 1,2
 36 ;
 37 -- out  warehouse | company | charge_code |     dt     | price | other_data 
 38 -- out ----------- --------- ------------- ------------ ------- ------------
 39 -- out          2 | comp 1  | bags        | 2022-01-01 |  1.00 | blah blah
 40 -- out          2 | comp 2  | bags        | 2022-01-01 |  1.50 | blah blah
 41 -- out          3 | comp 1  | bag2        | 2022-02-05 |  1.00 | blah blah
 42 -- out          3 | comp 2  | bag2        | 2022-02-05 |  2.00 | blah blah
 43 -- out (4 rows)

CodePudding user response：

Use EXISTS to check whether there is a matching row with another price.

SELECT *
FROM m_chg_date_d
WHERE EXISTS
( 
  SELECT NULL
  FROM m_chg_date_d other
  WHERE other.warehouse = m_chg_date_d.warehouse
  AND other.charge_code = m_chg_date_d.charge_code
  AND other.price <> m_chg_date_d.price
)
ORDER BY warehouse, charge_code, company;