Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Control break reporting using analytical functions
Excellent! Works like a charm!
And, yes, the math was done manually and hand-entered by me, so there's probably a miscalc or typo involved.
Thanks!!
Rich
Rich Jesse System/Database Administrator rich.jesse_at_quadtechworld.com QuadTech, Sussex, WI USA
-----Original Message-----
From: Gints Plivna [mailto:gints.plivna_at_gmail.com]
Sent: Thursday, July 14, 2005 3:58 AM
To: Jesse, Rich
Cc: oracle-l_at_freelists.org
Subject: Re: Control break reporting using analytical functions
Probably it could be achieved using less levels of included FROMs but
I leave that task of optimization to You :)))
SELECT
ASSEMBLY,
ROUTE,
WORKcenter,
opno,
runtimeSum,
setuptimeSum
FROM (
SELECT
ASSEMBLY,
ROUTE,
WORKcenter,
opno,
sum(runtime) OVER (PARTITION BY rn) runtimeSum,
sum(setuptime) OVER (PARTITION BY rn) setuptimeSum,
flag
FROM (
SELECT
ASSEMBLY, ROUTE, WORKcenter, opno, runtime, setuptime, max(rn) OVER (ORDER BY opno) rn, flag FROM ( select ASSEMBLY, ROUTE, WORKcenter, opno, runtime, setuptime, case when prev_workcenter = WORKcenter THEN 0 ELSE 1 END flag, case when prev_workcenter = WORKcenter THEN NULL ELSE rn END rn FROM ( select ASSEMBLY, ROUTE, WORKcenter, opno, runtime, setuptime, lag(workcenter) OVER (ORDER BY opno) prev_workcenter, row_number() OVER (ORDER BY opno) rn from mytable order by opno )
ASSEMBLY RO WORK OPNO RUNTIMESUM SETUPTIMESUM --------------- -- ---- ------- --------------- ---------------
33858 00 4003 0995 1,9 2,7 33858 00 4051 1010 0 ,1 33858 00 4091 1020 1,25 2,5 33858 00 4620 1030 0 0 33858 00 4052 1040 ,5 ,3 33858 00 4640 1050 2 0 33858 00 4003 1055 3,5 7 33858 00 4006 1070 1,5 2
BTW I think that your given example of sums is a bit wrong or otherwise I haven't undestood you correctly.
Gints Plivna
On 7/13/05, Jesse, Rich <Rich.Jesse_at_quadtechworld.com> wrote:
>
>
> Hey all,
>
>
>
> Using 9.2.0.5, we've got this table and data (don't ask about the
> datatypes):
>
>
>
> CREATE TABLE MYTABLE
> (
> ASSEMBLY CHAR(15),
> OPNO CHAR(7),
> ROUTE CHAR(2),
> WORKCENTER CHAR(4),
> RUNTIME FLOAT(126),
> SETUPTIME FLOAT(126)
> )/
>
[snip]
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Jul 14 2005 - 11:08:33 CDT
![]() |
![]() |