Analytic Problem
Date: Tue, 13 May 2008 18:21:08 -0700 (PDT)
Message-ID: <7f5a87a0-bf3d-4a78-a0ad-006a96501fe7@u36g2000prf.googlegroups.com>
Could someone provide insight on this request?
create table scott
(
mydate date,
vehicle number,
route number,
tstamp date
)
/
insert into scott
values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
APR-08 00:59:00','DD-MON-YYHH24:MI:SS')
/
insert into scott
values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
APR-08 01:10:00','DD-MON-YYHH24:MI:SS')
/
insert into scott
values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
APR-08 02:00:00','DD-MON-YYHH24:MI:SS')
/
insert into scott
values ( to_date('10-APR-08','DD-MON-YY'), 5182,200, to_date('10-
APR-08 02:10:00','DD-MON-YYHH24:MI:SS')
/
insert into scott
values ( to_date('10-APR-08','DD-MON-YY'), 5182,200, to_date('10-
APR-08 02:20:00','DD-MON-YYHH24:MI:SS')
/
insert into scott
values ( to_date('10-APR-08','DD-MON-YY'), 5182,200, to_date('10-
APR-08 02:20:00','DD-MON-YYHH24:MI:SS')
/
insert into scott
values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
APR-08 02:40:00','DD-MON-YYHH24:MI:SS')
/
insert into scott
values ( to_date('10-APR-08','DD-MON-YY'), 5182,100, to_date('10-
APR-08 03:00:00','DD-MON-YYHH24:MI:SS')
/
commit
/
- return records and --convert tstamp to number of minutes past midnight select mydate,vehicle,route, TRUNC(tstamp,'MI') from scott ;
10-APR-08, 5182, 100, 59 10-APR-08, 5182, 100, 70 10-APR-08, 5182, 100, 120 10-APR-08, 5182, 200, 130 10-APR-08, 5182, 200, 140 10-APR-08, 5182, 100, 160 10-APR-08, 5182, 100, 180
Requirements:
return group records by mydate,vehicle and route
convert tstamp to minutes past midnight and return MIN(tstamp) and
MAX(tstamp)
results are based on the order records were entered => tstamp
So returned results would be:
MYDATE VEHICLE ROUTE MIN MAX
10-APR-08 5182 100 59 120 10-APR-08 5182 200 130 140 10-APR-08 5182 100 160 180Received on Tue May 13 2008 - 20:21:08 CDT