RE: Control break reporting using analytical functions

From: Jesse, Rich <Rich.Jesse_at_quadtechworld.com>
Date: Thu, 14 Jul 2005 11:06:33 -0500
Message-ID: <FB5D3CCFCECC2948B5DCF4CABDBE66971FFABF@QTEX1.qg.com>

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
      )

)
)
)
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_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-l

Received on Thu Jul 14 2005 - 11:08:33 CDT