Say I have the following information in an Oracle 11g table:
Qty Production order Date and time
--- ----------------- ---------------
20 00000000000000001 12-JAN-14 00:02
20 00000000000000001 12-JAN-14 00:05
20 00000000000000001 12-JAN-14 00:07
20 00000000000000001 13-JAN-14 00:09
30 00000000000000002 12-JAN-14 00:11
30 00000000000000002 12-JAN-14 00:15
30 00000000000000002 12-JAN-14 00:20
30 00000000000000002 14-JAN-14 00:29
I would like to write a query that would return the following:
Qty Production order First Last
--- ----------------- --------------- ---------------
80 00000000000000001 12-JAN-14 00:02 13-JAN-14 00:09
120 00000000000000002 12-JAN-14 00:11 14-JAN-14 00:29
That is, the sum of the Qty column grouped by Production order, and the date/time of the first and last records for each Production order. I came up with a query that yielded this result:
Qty Production order First Last
--- ----------------- --------------- ---------------
80 00000000000000001 12-JAN-14 00:02 14-JAN-14 00:29
120 00000000000000002 12-JAN-14 00:02 14-JAN-14 00:29
Which means that the First and Last columns show the overall first and last date / time of the whole table. Please note that this is a dummy table. Sorry I am now allowed to write the actual query I came up with since work policies do not allow me to share it. Also, I tried with windowing functions such as rank() and row_number() but my user does not have enough privileges to do so. Any help or hints will be greatly appreciated.
I think you don't need rank() or row_number() in this case (at least with your sample data). If i am wrong then may be you could give more appropriate example. Anyway the following query returns what you need:
with t as (
select 20 Qty,
'00000000000000001' Production_order,
to_date('12-JAN-14 00:02',
'dd-MON-rr HH24:mi',
'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
from dual
union all
select 20 Qty,
'00000000000000001' Production_order,
to_date('12-JAN-14 00:05',
'dd-MON-rr HH24:mi',
'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
from dual
union all
select 20 Qty,
'00000000000000001' Production_order,
to_date('12-JAN-14 00:07',
'dd-MON-rr HH24:mi',
'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
from dual
union all
select 20 Qty,
'00000000000000001' Production_order,
to_date('13-JAN-14 00:09',
'dd-MON-rr HH24:mi',
'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
from dual
union all
select 30 Qty,
'00000000000000002' Production_order,
to_date('12-JAN-14 00:11',
'dd-MON-rr HH24:mi',
'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
from dual
union all
select 30 Qty,
'00000000000000002' Production_order,
to_date('12-JAN-14 00:15',
'dd-MON-rr HH24:mi',
'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
from dual
union all
select 30 Qty,
'00000000000000002' Production_order,
to_date('12-JAN-14 00:20',
'dd-MON-rr HH24:mi',
'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
from dual
union all
select 30 Qty,
'00000000000000002' Production_order,
to_date('14-JAN-14 00:29',
'dd-MON-rr HH24:mi',
'NLS_DATE_LANGUAGE=AMERICAN') Date_and_time
from dual)
select sum(Qty) Total_Qty,
Production_order,
min(Date_and_time) First_Date_and_time,
max(Date_and_time) Last_Date_and_time
from t
group by Production_order;
TOTAL_QTY PRODUCTION_ORDER FIRST_DATE_AND_TIME LAST_DATE_AND_TIME
---------- ----------------- ------------------- ------------------
80 00000000000000001 12.01.2014 0:02:00 13.01.2014 0:09:00
120 00000000000000002 12.01.2014 0:11:00 14.01.2014 0:29:00