Analytic Problem

From: jimmyb <jimmybrock_at_gmail.com>
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   180
Received on Tue May 13 2008 - 20:21:08 CDT

Original text of this message