Re: SQL Question

From: Pierre Charpenay <pierre.charpenay_at_unilog.fr>
Date: Fri, 20 Jul 2001 12:52:38 +0200
Message-ID: <3B580D76.6BD90CAD@unilog.fr>

Fabio Oliveira Della Santina a écrit :
>
> Hi all,

Hello !

>
> I have a question about SQL.
>
> I am building a report that use the SQL consilt below:
>
> select to_char((submitted_on - to_char(submitted_on,'D') + 1),'MM-DD-YYYY') WeekDate,
> SUM(DECODE(ir_state,'Deferred',1,0)),
> SUM(DECODE(ir_state,'Development Release In Progres',1,0)),
> SUM(DECODE(ir_state,'Development Release In Progress',1,0)),
> SUM(DECODE(ir_state,'Document Fix In Progress',1,0)),
> SUM(DECODE(ir_state,'Forwarded',1,0)),
> SUM(DECODE(ir_state,'Passed By Retest',1,0)),
> SUM(DECODE(ir_state,'Proposed For Lagomization',1,0)),
> SUM(DECODE(ir_state,'SPE Release In Progress',1,0)),
> SUM(DECODE(ir_state,'Software Fix In Progress',1,0)),
> SUM(DECODE(ir_state,'Test In Progress',1,0)),
> SUM(DECODE(ir_state,'Unassigned',1,0)),
> SUM(DECODE(ir_state,'Under Investigation',1,0)),
> SUM(DECODE(ir_state,'Withdrawn',1,0)),
> SUM(DECODE(ir_state,'NULL',1,0)),
> COUNT(identifier)
> from defects
> where (submitted_on >= to_date('05-01-2001','MM-DD-YYYY'))
> group by (submitted_on - to_char(submitted_on,'D') + 1)
> order by WeekDate;
>
> And my result is a table grouped by week with number of ir_state by week.
>
> The problem is: I need of the result acumulative of each week. For example, the result of the second row
>
> the sum of ir_state of the second week more the result of first week.
>
> Can Anybody help me ?

Try to use a join between a sub-request finding the weeks and your table where dates are lower, something like that (not tested) :

select
  W.weekdate,

  SUM(DECODE(D.ir_state,'Deferred',1,0)),
  SUM(DECODE(D.ir_state,'Development Release In Progres',1,0)),
  SUM(DECODE(D.ir_state,'Development Release In Progress',1,0)),
  SUM(DECODE(D.ir_state,'Document Fix In Progress',1,0)),
  SUM(DECODE(D.ir_state,'Forwarded',1,0)),
  SUM(DECODE(D.ir_state,'Passed By Retest',1,0)),
  SUM(DECODE(D.ir_state,'Proposed For Lagomization',1,0)),
  SUM(DECODE(D.ir_state,'SPE Release In Progress',1,0)),
  SUM(DECODE(D.ir_state,'Software Fix In Progress',1,0)),
  SUM(DECODE(D.ir_state,'Test In Progress',1,0)),
  SUM(DECODE(D.ir_state,'Unassigned',1,0)),
  SUM(DECODE(D.ir_state,'Under Investigation',1,0)),
  SUM(DECODE(D.ir_state,'Withdrawn',1,0)),
  SUM(DECODE(D.ir_state,'NULL',1,0)),

  COUNT(D.identifier)
from defects D,

     (select submitted_on - to_char(submitted_on,'D') + 1 weekdate
      from defects
      where submitted_on >= to_date('05-01-2001','MM-DD-YYYY')
      group by submitted_on - to_char(submitted_on,'D') + 1) W

where D.submitted_on >= to_date('05-01-2001','MM-DD-YYYY')  and D.submitted_on <= W.weekdate+6
group by W.weekdate;

>
> Thanks.
 

-- 
 Pierre CHARPENAY

Received on Fri Jul 20 2001 - 05:52:38 CDT