Home » SQL & PL/SQL » SQL & PL/SQL » Value of first and last sale
Value of first and last sale [message #594529] Thu, 29 August 2013 09:54 Go to next message
RMSoares
Messages: 46
Registered: September 2010
Location: Lisboa
Member

Hi,
I have a sales table where i need to get same indicators about the account, I get the date of first sale and the date of last sale, but I need to get the value of first sale (min date) and the last sale (max date)

WITH My_table AS
(
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130801', 'YYYYMMDD') DTCREATED, 1000 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130815', 'YYYYMMDD') DTCREATED, 400 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130807', 'YYYYMMDD') DTCREATED, 2600 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130808', 'YYYYMMDD') DTCREATED, 500 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130811', 'YYYYMMDD') DTCREATED, 450 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130802', 'YYYYMMDD') DTCREATED, 4000 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130817', 'YYYYMMDD') DTCREATED, 600 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130828', 'YYYYMMDD') DTCREATED, 10 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130822', 'YYYYMMDD') DTCREATED, 400 as Value FROM dual UNION ALL
SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130818', 'YYYYMMDD') DTCREATED, 500 as Value FROM dual 
)
SELECT 
ID_ACC, Cod_Product,
MIN(TO_CHAR(DTCREATED, 'YYYYMMDD')) as DATE_START,  
MAX( TO_CHAR(DTCREATED, 'YYYYMMDD')) as DATE_LAST, 
(MAX(DTCREATED) - MIN(DTCREATED)) as NUM_DAYS, 
COUNT(1) as NUM_SALES  
FROM My_table
GROUP BY  ID_ACC, Cod_Product


Where i will get
ID_ACC = 0001	
CPD_PRODUCT = ABC	
DATE_START = 20130801	
DATE_LAST = 20130828	
NUM_DAYS = 27	
NUM_SALES = 10


And i need this two values
VALUE_FIRST = 1000 (on 20130801)
VALUE_LAST = 10 (on 20130828)


How can i get VALUE_FIRST and VALUE_LAST without using temp tables or sub-select ?
Re: Value of first and last sale [message #594530 is a reply to message #594529] Thu, 29 August 2013 10:05 Go to previous messageGo to next message
cookiemonster
Messages: 11285
Registered: September 2008
Location: Rainy Manchester
Senior Member
SQL> WITH My_table AS
  2  (
  3  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130801', 'YYYYMMDD') DTCREATED, 1000 as Value FROM dual UNION ALL
  4  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130815', 'YYYYMMDD') DTCREATED, 400 as Value FROM dual UNION ALL
  5  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130807', 'YYYYMMDD') DTCREATED, 2600 as Value FROM dual UNION ALL
  6  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130808', 'YYYYMMDD') DTCREATED, 500 as Value FROM dual UNION ALL
  7  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130811', 'YYYYMMDD') DTCREATED, 450 as Value FROM dual UNION ALL
  8  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130802', 'YYYYMMDD') DTCREATED, 4000 as Value FROM dual UNION ALL
  9  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130817', 'YYYYMMDD') DTCREATED, 600 as Value FROM dual UNION ALL
 10  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130828', 'YYYYMMDD') DTCREATED, 10 as Value FROM dual UNION ALL
 11  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130822', 'YYYYMMDD') DTCREATED, 400 as Value FROM dual UNION ALL
 12  SELECT '0001' ID_ACC, 'ABC' Cod_Product, to_date('20130818', 'YYYYMMDD') DTCREATED, 500 as Value FROM dual
 13  )
 14  SELECT
 15  ID_ACC, Cod_Product,
 16  MIN(TO_CHAR(DTCREATED, 'YYYYMMDD')) as DATE_START,
 17  MAX( TO_CHAR(DTCREATED, 'YYYYMMDD')) as DATE_LAST,
 18  (MAX(DTCREATED) - MIN(DTCREATED)) as NUM_DAYS,
 19  COUNT(1) as NUM_SALES,
 20  first_val,
 21  last_val
 22  FROM (SELECT ID_ACC, Cod_Product, DTCREATED, VALUE,
 23               first_value(VALUE) OVER (ORDER BY dtcreated) first_val,
 24               last_value(VALUE) OVER (ORDER BY dtcreated
 25               ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) last_val
 26        FROM My_table
 27       )
 28  GROUP BY ID_ACC, Cod_Product, first_val, last_val
 29  /
 
ID_ACC COD_PRODUCT DATE_START DATE_LAST   NUM_DAYS  NUM_SALES  FIRST_VAL   LAST_VAL
------ ----------- ---------- --------- ---------- ---------- ---------- ----------
0001   ABC         20130801   20130828          27         10       1000         10
 
SQL> 
Re: Value of first and last sale [message #594541 is a reply to message #594530] Thu, 29 August 2013 15:38 Go to previous message
DrabJay
Messages: 32
Registered: May 2013
Member
WITH my_table AS
  (SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130801', 'YYYYMMDD') dtcreated, 1000 AS value FROM dual
   UNION ALL
   SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130815', 'YYYYMMDD') dtcreated,  400 AS value FROM dual
   UNION ALL
   SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130807', 'YYYYMMDD') dtcreated, 2600 AS value FROM dual
   UNION ALL
   SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130808', 'YYYYMMDD') dtcreated,  500 AS value FROM dual
   UNION ALL
   SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130811', 'YYYYMMDD') dtcreated,  450 AS value FROM dual
   UNION ALL
   SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130802', 'YYYYMMDD') dtcreated, 4000 AS value FROM dual
   UNION ALL
   SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130817', 'YYYYMMDD') dtcreated,  600 AS value FROM dual
   UNION ALL
   SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130828', 'YYYYMMDD') dtcreated,   10 AS value FROM dual
   UNION ALL
   SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130822', 'YYYYMMDD') dtcreated,  400 AS value FROM dual
   UNION ALL
   SELECT '0001' id_acc, 'ABC' cod_product, to_date('20130818', 'YYYYMMDD') dtcreated,  500 AS value FROM dual 
  )
SELECT id_acc,
       cod_product,
       min(to_char(dtcreated, 'YYYYMMDD')) AS date_start,
       max(to_char(dtcreated, 'YYYYMMDD')) AS date_last,
       max(dtcreated) - min(dtcreated) AS num_days,
       count(*) AS num_sales,
       max(value) KEEP (DENSE_RANK FIRST ORDER BY dtcreated) value_first,
       max(value) KEEP (DENSE_RANK LAST ORDER BY dtcreated) value_last
FROM my_table
GROUP BY id_acc,
         cod_product
Previous Topic: Migration of three tables
Next Topic: synonym and permission
Goto Forum:
  


Current Time: Sat Dec 20 05:41:20 CST 2014

Total time taken to generate the page: 0.10427 seconds