Analytic Problem

jimmyb

unread,

May 13, 2008, 9:21:08 PM5/13/08

to

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,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

Urs Metzger

unread,

May 14, 2008, 1:36:49 PM5/14/08

to

jimmyb schrieb:

this is what meets your requirements, but maybe not what you
expected:

SQL> select mydate, vehicle, route,
2 min((tstamp - trunc(tstamp)) * 24 * 60) as "MIN",
3 max((tstamp - trunc(tstamp)) * 24 * 60) AS "MAX"
4 from scott
5 group by mydate, vehicle, route;

MYDATE VEHICLE ROUTE MIN MAX

-------- ---------- ---------- ---------- ----------
10.04.08 5182 100 59 180
10.04.08 5182 200 130 140

I don't see why you get three rows in your sample result set.
Is there a hidden row-number-like thing?

hth anyway,
Urs Metzger

Maxim Demenko

unread,

May 14, 2008, 3:45:04 PM5/14/08

to Urs Metzger

Urs Metzger schrieb:

As i understand, the row-number-like thing is

>> results are based on the order records were entered => tstamp

So, based on your example something like this could work:
SQL> select max(mydate) mydate,
2 max(vehicle) vehicle,
3 max(route) route,
4 min((tstamp - trunc(tstamp)) * 24 * 60) as "MIN",
5 max((tstamp - trunc(tstamp)) * 24 * 60) as "MAX"
6 from (select mydate,
7 vehicle,
8 route,
9 tstamp,
10 sum(start_of_group)
11 over(partition by mydate, vehicle order by tstamp)
group_no
12 from (select t.*,
13 decode(lag(route) over(partition by mydate,
14 vehicle order by tstamp),
15 route,
16 0,
17 1) start_of_group
18 from scott t))
19 group by group_no
20 order by group_no
21 ;

MYDATE VEHICLE ROUTE MIN MAX

------------------- ---------- ---------- ---------- ----------
10.04.2008 00:00:00 5182 100 59 120
10.04.2008 00:00:00 5182 200 130 140
10.04.2008 00:00:00 5182 100 160 180

Best regards

Maxim