How can cumulative percent be achieved with SQL?

                    Defects Requirements Phase      Defects Design Phase                 Defects Production Phase
Requirements      7            
Design                     14                                      50      
Production                     27                                      30                                 175
total                     48                                      80                                 175

The table above is a ‘Defect Containment Matrix’. It shows how many defects were discovered in three phases of development, and what phase they “belong” to. So 7 defects were discovered during the Requirements phase. There were, however, 14 more requirements defects that were discovered during the Design Phase, as well as 50 design defects. During the Production Phase there were 27 more Requirements defects, and 30 more Design defects found, as well as 175 Production defects. The total defects are shown at the bottom for each phase.

The database table (DefCon) which stores this data is similar to the Matrix table. It consists of three records, each record having three fields: “Req”, ”Design”, and “Production”.

Req      Design      Production
7            
14      50      
27      30      175

The SQL query used to produce the Matrix is simple:
Select * from DefCon

Now comes the problem. It is required that a “Percentages DefCon Matrix” be produced.

                   Defects Requirements Phase      Defects Design Phase                             Defects Production Phase
Requirements      (7/48)*100 = 14.6%            
Design                      ((7+14)/48)*100 = 43%      (50/80)*100 = 62.5%      
Production                      100%                                       100%                                              100%

Each row of every column is added the sum of the previous rows in that column and divided by the total of the column to give the “Cumulative percent”
“which shows what percent of the defect were capture in stage and by what stage 100% of the defects that were injected in a certain stage were detected”.

This is the problem: How can this cumulative percent table be achieved with SQL?
wsfindlaterAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

muzzy2003Commented:
Can you give the complete structure of the table? I presume there are other fields (a primary key for instance). Thanks.
0
KarinLoosCommented:
i created a temproary table to simulate your data, this will work. (had to add a primary key called id "adi")

create table #DefMatrix (adi int,  Req int NULL, Design int NULL, Production int NULL )
insert #DefMatrix VALUEs (1, 7, NULL, NULL)     7
insert #DefMatrix VALUEs (2, 14, 50, NULL)     21
insert #DefMatrix VALUEs (3, 27, 30, 175)      48

-- first work out the running totals into a temporary table
create table #runntotals ( adi int, req decimal(10,4) , design decimal(10,4), production decimal(10,4))
declare @adi int,
      @req int,
      @design int,
      @prod int,

      @reqold int,
      @designold int,
      @prodold int

set @reqold = 0.0000
set @designold = 0.0000
set @prodold = 0.0000

declare myCursor CURSOR for
Select  adi,
      isnull(Req,0) as req ,
      isnull(Design,0)  as design,
      isnull(Production,0)  as prod
FROM #DefMatrix
open myCursor
fetch next from myCursor into @adi, @req, @design, @prod
while @@fetch_status = 0
  begin
      insert #runntotals
           values (@adi,  @reqold + @req, @designold + @design, @prodold + @prod )
      set @reqold = @reqold + @req
      set @designold = @designold + @design
      Set @prodold = @prodold + @prod

      fetch next from myCursor into @adi, @req, @design, @prod
  end
close myCursor
deallocate myCursor

-- now  work out the "running percentages"
SELECT  a.adi,
      (a.req/ b.req)* 100.00  as percReq,
      (a.design/ b.Design)*100.00  as percDesign ,
      (a.production / b.production) *100.00 as percProduction
FROM #runntotals  a ,
( SELECT convert(decimal(10,4), sum(req)) as req,
       convert(decimal(10,4), sum(design)) as design,
       convert(decimal(10,4), sum(Production)) as Production from #DefMatrix ) as b

-- result (you can of course format the output to show less decimals)
adi         percReq                           percDesign                        percProduction                    
----------- --------------------------------- --------------------------------- ---------------------------------
1           14.58333333333330000              .00000000000000000                .00000000000000000
2           43.75000000000000000              62.50000000000000000              .00000000000000000
3           100.00000000000000000             100.00000000000000000             100.00000000000000000

HTH
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
imrancsCommented:
try this

Create table DefectContainmentMatrix (Requirment int, Design int, Production int)

Insert Into DefectContainmentMatrix values(7,null,null)
Insert Into DefectContainmentMatrix values(14, 50, null)
Insert Into DefectContainmentMatrix values(27, 30, 175)



Select * From DefectContainmentMatrix


Select  (Cast(a.Requirment As Decimal(11,2)) / Cast(t.Requirment As Decimal(11,2))) * 100       RequirmentPercentage,
       (Cast(a.Design As Decimal(11,2)) / Cast(t.Design As Decimal(11,2))) * 100             DesignPercentage,
      (Cast(a.Production As Decimal(11,2)) / Cast(t.Production As Decimal(11,2))) * 100       ProductionPercentage
 
From       DefectContainmentMatrix a CROSS JOIN
      (Select Sum(Requirment) Requirment, Sum(Design) Design, Sum(Production) Production From DefectContainmentMatrix) t



Imran
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

imrancsCommented:
and here is result generated by above query

RequirmentPercentage            DesignPercentage                ProductionPercentage            
------------------------------- ------------------------------- -------------------------------
14.58333333333300               NULL                            NULL
29.16666666666600               62.50000000000000               NULL
56.25000000000000               37.50000000000000               100.00000000000000


Imran
0
imrancsCommented:
sorry

I think KarinLoos's has the right solution


Imran
0
OlegPCommented:
DECLARE @TBL TABLE       (
                  ID             INT IDENTITY(1,1),
                  Req             DECIMAL(38,20),
                  Design            DECIMAL(38,20),
                  Production      DECIMAL(38,20)
                  )
INSERT INTO @TBL(Req,Design,Production)
SELECT       Req,Design,Production
FROM      DefCon

SELECT      100*SUM(B.Req)/R,100*SUM(B.Design)/D,100*SUM(B.Production)/P
FROM      @TBL A INNER JOIN @TBL B
      ON A.[ID]>=B.[ID],
      (
      SELECT       SUM(Req)       R,
            SUM(Design)      D,
            SUM(Production)      P
      FROM      @TBL      
      ) C
GROUP      BY A.[ID],R,D,P
ORDER       BY A.[ID]

0
wsfindlaterAuthor Commented:
Thanks to all of you
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.