Re: Analytic Problem
Date: Wed, 14 May 2008 19:36:49 +0200
Message-ID: <g0f7ub$is0$1@online.de>
jimmyb schrieb:
> 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
>
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
Received on Wed May 14 2008 - 12:36:49 CDT