Home » SQL & PL/SQL » SQL & PL/SQL » SQL Query Help (PL/SQL )
SQL Query Help [message #584783] Fri, 17 May 2013 11:34 Go to next message
jweingarth1
Messages: 4
Registered: May 2013
Location: minneapolis
Junior Member
I have the following SQL query:

 select pl.product_id, pl.location_id, week_begin_dt, 
               round((NVL(ps_gross_retail,0) + NVL(rs_gross_retail,0))/(NVL(rs_unit_qty,0) + NVL(ps_unit_qty,0)),2) as calc_unit_retail,
               case when ((NVL(ps_gross_retail,0) + NVL(rs_gross_retail,0))/(NVL(rs_unit_qty,0) + NVL(ps_unit_qty,0))) * 1.3
                         >= greatest(p.unit_retail, avg_unit_retail) then 'REG SALE' else 'MARKDOWN' end as sale_type    
         from qr_Trans_pl_WTD wtd
        join qr_product_location pl
        on pl.product_id = wtd.product_id and pl.location_id = wtd.location_id
        join qr_product p
        on p.product_id = wtd.product_id
        where NVL(rs_unit_qty,0) + NVL(ps_unit_qty,0) <> 0
        and NVL(ps_gross_retail,0) + NVL(rs_gross_retail,0) <> 0


which returns something like this....

ACECELLA2\3	S9A13	04-NOV-12	REG SALE
ACECELLA2\3	S9A13	25-NOV-12	REG SALE
ACECELLA2\3	S9A13	02-DEC-12	MARKDOWN
ACECELLA2\3	S9A13	16-DEC-12	REG SALE
ACECELLA2\3	S9A13	23-DEC-12	REG SALE


At first I was just selecting the min date where sale type = markdown. But that put this product on a markdown. Basically I need a way in a sql query to compare whether was just discounted for a time, but not actually on markdown. My guess is I need to some home compare that 2-DEC record with later records to see if it really is on markdown. Is there a way to do this in SQl, rather then writing some type of code block?


Basically if something is on markdown, I'd expect all additional dates to be on markdown.

Thanks for all the help.

Re: SQL Query Help [message #584784 is a reply to message #584783] Fri, 17 May 2013 11:38 Go to previous messageGo to next message
Michel Cadot
Messages: 57645
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Welcome to the forum.

With any SQL or PL/SQL question, please, Post a working Test case: create table (including all constraints) and insert statements along with the result you want with these data then we will be able work with your table and data. Explain with words and sentences the rules that lead to this result.
Also always post your Oracle version, with 4 decimals.

Regards
Michel
Re: SQL Query Help [message #584785 is a reply to message #584784] Fri, 17 May 2013 12:00 Go to previous messageGo to next message
jweingarth1
Messages: 4
Registered: May 2013
Location: minneapolis
Junior Member
CREATE TABLE qr_product
  ( 
     product_id  VARCHAR2(30 byte), 
     location_id VARCHAR2(32 byte), 
     unit_retail NUMBER(8, 2) 
  ); 

CREATE TABLE qr_product_location 
  ( 
     product_id      VARCHAR2(30 byte), 
     location_id     VARCHAR2(32 byte), 
     avg_unit_retail NUMBER(8, 2) 
  ); 

CREATE TABLE qr_trans_pl_wtd 
  ( 
     product_id      VARCHAR2(30 byte), 
     location_id     VARCHAR2(32 byte), 
     week_begin_dt   DATE, 
     ps_gross_retail NUMBER(8, 2), 
     rs_gross_retail NUMBER(8, 2), 
     rs_unit_qty     NUMBER(8, 2), 
     ps_unit_qty     NUMBER(8, 2) 
  ); 

INSERT INTO qr_product 
VALUES     ('ACECELLA2\3', 
            'S9A13', 
            '8.95'); 

INSERT INTO qr_product_location 
VALUES     ('ACECELLA2\3', 
            'S9A13', 
            '8.95'); 

INSERT INTO qr_trans_pl_wtd 
VALUES     ('ACECELLA2\3', 
            'S9A13', 
            '4-NOV-2012', 
            0, 
            8.95, 
            1, 
            0); 

INSERT INTO qr_trans_pl_wtd
VALUES     ('ACECELLA2\3', 
            'S9A13', 
            '25-NOV-2012', 
            0, 
            8.95, 
            1, 
            0); 

INSERT INTO qr_trans_pl_wtd
VALUES     ('ACECELLA2\3', 
            'S9A13', 
            '2-DEC-2012', 
            44.82, 
            0, 
            0, 
            10); 

INSERT INTO qr_trans_pl_wtd 
VALUES     ('ACECELLA2\3', 
            'S9A13', 
            '16-DEC-2012', 
            0, 
            44.75, 
            5, 
            0); 

INSERT INTO qr_trans_pl_wtd
VALUES     ('ACECELLA2\3', 
            'S9A13', 
            '23-DEC-2012', 
            0, 
            8.95, 
            1, 
            0); 


Expected results should be

PRODUCT_ID      LOCATION_ID    SALE_TYPE
'ACECELLA2\3'   'S9A13'              REG_SALE

Re: SQL Query Help [message #584787 is a reply to message #584785] Fri, 17 May 2013 12:06 Go to previous messageGo to next message
jweingarth1
Messages: 4
Registered: May 2013
Location: minneapolis
Junior Member
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
Re: SQL Query Help [message #585448 is a reply to message #584787] Sat, 25 May 2013 15:55 Go to previous message
Barbara Boehmer
Messages: 7861
Registered: November 2002
Location: California, USA
Senior Member
The following uses your original query, then takes the last value of sale_type for each combination of product_id and location_id.

SCOTT@orcl_11gR2> column product_id  format a11
SCOTT@orcl_11gR2> column location_id format a10
SCOTT@orcl_11gR2> with
  2    original_query as
  3  	 (select pl.product_id, pl.location_id, week_begin_dt,
  4  		 case when ((NVL(ps_gross_retail,0) +
  5  			     NVL(rs_gross_retail,0))/(NVL(rs_unit_qty,0) +
  6  			     NVL(ps_unit_qty,0))) * 1.3
  7  			    >= greatest(p.unit_retail, avg_unit_retail)
  8  		      then 'REG SALE'
  9  		      else 'MARKDOWN'
 10  		 end  as sale_type
 11  	  from	 qr_Trans_pl_WTD wtd
 12  		 join qr_product_location pl
 13  		   on pl.product_id = wtd.product_id and pl.location_id = wtd.location_id
 14  		 join qr_product p
 15  		   on p.product_id = wtd.product_id
 16  	  where  NVL(rs_unit_qty,0) + NVL(ps_unit_qty,0) <> 0
 17  	  and	 NVL(ps_gross_retail,0) + NVL(rs_gross_retail,0) <> 0)
 18  select product_id, location_id,
 19  	    max (sale_type) keep (dense_rank last order by week_begin_dt) as sale_type
 20  from   original_query
 21  group  by product_id, location_id
 22  /

PRODUCT_ID  LOCATION_I SALE_TYP
----------- ---------- --------
ACECELLA2\3 S9A13      REG SALE

1 row selected.

Previous Topic: Scheduling same job with different timing.
Next Topic: select the greatest number from the row
Goto Forum:
  


Current Time: Thu Apr 24 08:43:59 CDT 2014

Total time taken to generate the page: 0.71581 seconds