SQL & PL/SQL :: Value Of First And Last Sale
Aug 29, 2013I 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
[Code]....
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 ?