# RE: Control break reporting using analytical functions

• From: "Jesse, Rich" <Rich.Jesse@xxxxxxxxxxxxxxxxx>
• To: "Gints Plivna" <gints.plivna@xxxxxxxxx>
• Date: Thu, 14 Jul 2005 11:06:33 -0500
```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

-----Original Message-----
From: Gints Plivna [mailto:gints.plivna@xxxxxxxxx]
Sent: Thursday, July 14, 2005 3:58 AM
To: Jesse, Rich
Cc: oracle-l@xxxxxxxxxxxxx
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
)
)
)
)
WHERE flag = 1
/

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@xxxxxxxxxxxxxxxxx> 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-l
```