OraFAQ Forum: SQL & PL/SQL » query-assistance

Home » SQL & PL/SQL » SQL & PL/SQL » query-assistance (Oracle 11g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

query-assistance [message #679431]

Fri, 28 February 2020 05:33

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)

Report message to a moderator

Re: query-assistance [message #679435 is a reply to message #679431]

Fri, 28 February 2020 06:51

Solomon Yakobson
Messages: 3305
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.

Report message to a moderator

Re: query-assistance [message #679436 is a reply to message #679435]

Fri, 28 February 2020 07:10

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)

Report message to a moderator

Re: query-assistance [message #679437 is a reply to message #679436]

Fri, 28 February 2020 07:27

Solomon Yakobson
Messages: 3305
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.

Report message to a moderator

Re: query-assistance [message #679438 is a reply to message #679437]

Fri, 28 February 2020 08:26

winfire
Messages: 6
Registered: September 2017

Junior Member

wow thx !!!

Report message to a moderator

Previous Topic:	Time Between Consecutive Locations
Next Topic:	embedded implicit cursors in SQL

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon May 19 01:30:16 CDT 2025