Home » SQL & PL/SQL » SQL & PL/SQL » query-assistance (Oracle 11g)
query-assistance [message #679431] Fri, 28 February 2020 05:33 Go to next message
winfire
Messages: 6
Registered: September 2017
Junior Member
Hi,

I need some help with a tricky query.
There are two tables (orders and prices). For every order I want to know the best supplier depending on ordered amount and the best supplier not depending on amount.


expected output:

article, order_date, amount, price, dep_price,dep_amount,dep_supplier, best_price, best_price_amount, best_price_amount_supplier
A100, 2020-02-02, 25, 0.9, 20, ARX, 0.8, 100, UPS
----------------------------------------------------
in words: ARX is best for when ordering 25 units - UPS would be best when ordering more than 100 units

Tried to start like this - no chance Confused
select ID,order_date,amount,article,date_from,date_to,price, supplier,amount_from,min_price
from (
select ID,order_date,amount,o.article,date_from,date_to,price, supplier,amount_from, 
  min(price) over (partition by p.article) min_price
from
orders o,
prices p
where 
o.article=p.article and
p.date_to is null and
o.amount>=p.amount_from)


create table orders(
      ID                 VARCHAR2(4 BYTE)         NOT NULL,
      article            VARCHAR2(4 BYTE)         NOT NULL,
      order_date           DATE,
      amount             Number(8,2)
   )
//
    create table prices(
      article            VARCHAR2(4 BYTE)         NOT NULL,
      date_from           DATE,
      date_to             DATE,
      amount_from       Number(8,2),
      price             Number(8,2),
      supplier          VARCHAR2(4 BYTE)
 )
//
insert into orders(ID,article,order_date,amount) VALUES('1','A100',to_date('20200202','YYYYMMDD'),25)
//
insert into prices(article,date_from,date_to,price, supplier,amount_from) VALUES('A100',to_date('20190201','YYYYMMDD'),to_date('20200228','YYYYMMDD'),0.7,'UPS',15)
//
insert into prices(article,date_from,date_to,price, supplier,amount_from) VALUES('A100',to_date('20200203','YYYYMMDD'),NULL,1.0,'UPS',10)
//
insert into prices(article,date_from,date_to,price, supplier,amount_from) VALUES('A100',to_date('20200203','YYYYMMDD'),NULL,0.8,'UPS',100)
//
insert into prices(article,date_from,date_to,price, supplier,amount_from) VALUES('A100',to_date('20200203','YYYYMMDD'),NULL,0.9,'ARX',20)
Re: query-assistance [message #679435 is a reply to message #679431] Fri, 28 February 2020 06:51 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2949
Registered: January 2010
Location: Connecticut, USA
Senior Member
How can ARX be best price if order date is 02/02/2020 and ARS price from date is 02/03/2020? Also, why ARX is best for when ordering 25 units? We need 25 units and UPS price is .7 if unit amount it 15 or more? Are you looking for order amount closest to amount_from?

SY.

Re: query-assistance [message #679436 is a reply to message #679435] Fri, 28 February 2020 07:10 Go to previous messageGo to next message
winfire
Messages: 6
Registered: September 2017
Junior Member
sorry for that typos regarding dates!
current prices have date_to=null.

correction:
//
insert into orders(ID,article,order_date,amount) VALUES('1','A100',to_date('20200202','YYYYMMDD'),25)
//
insert into prices(article,date_from,date_to,price, supplier,amount_from) VALUES('A100',to_date('20190201','YYYYMMDD'),to_date('20200130','YYYYMMDD'),0.7,'UPS',15)
//
insert into prices(article,date_from,date_to,price, supplier,amount_from) VALUES('A100',to_date('20200201','YYYYMMDD'),NULL,1.0,'UPS',10)
//
insert into prices(article,date_from,date_to,price, supplier,amount_from) VALUES('A100',to_date('20200201','YYYYMMDD'),NULL,0.8,'UPS',100)
//
insert into prices(article,date_from,date_to,price, supplier,amount_from) VALUES('A100',to_date('20200201','YYYYMMDD'),NULL,0.9,'ARX',20)
Re: query-assistance [message #679437 is a reply to message #679436] Fri, 28 February 2020 07:27 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2949
Registered: January 2010
Location: Connecticut, USA
Senior Member
select  o.article,
        o.order_date,
        o.amount,
        min(p.price) keep(dense_rank first order by least(0,sign(p.amount_from - o.amount)),price) dep_price,
        min(p.amount_from) keep(dense_rank first order by least(0,sign(p.amount_from - o.amount)),price) dep_amount,
        min(p.supplier) keep(dense_rank first order by least(0,sign(p.amount_from - o.amount)),price) dep_supplier,
        min(p.price) best_price,
        min(p.amount_from) keep(dense_rank first order by price) best_price_amount,
        min(p.supplier) keep(dense_rank first order by price) best_price_supplier
  from  orders o,
        prices p
  where p.article = o.article
    and o.order_date between p.date_from and nvl(p.date_to,sysdate)
  group by o.article,
           o.order_date,
           o.amount
/

ARTICLE ORDER_DAT     AMOUNT  DEP_PRICE DEP_AMOUNT DEP_ BEST_PRICE BEST_PRICE_AMOUNT BEST_PRICE_SUPPLIER
------- --------- ---------- ---------- ---------- ---- ---------- ----------------- -------------------
A100    02-FEB-20         25         .9         20 ARX          .8               100 UPS

SQL>
SY.
Re: query-assistance [message #679438 is a reply to message #679437] Fri, 28 February 2020 08:26 Go to previous message
winfire
Messages: 6
Registered: September 2017
Junior Member
wow thx !!!
Previous Topic: Time Between Consecutive Locations
Next Topic: embedded implicit cursors in SQL
Goto Forum:
  


Current Time: Sun May 31 09:31:37 CDT 2020