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@xxxxxxxxxxxxxxxxx      QuadTech, Sussex, WI USA


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

Other related posts: