[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1583
  • Last Modified:

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
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?
1 Solution
Can you give the complete structure of the table? I presume there are other fields (a primary key for instance). Thanks.
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
      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
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

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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

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


I think KarinLoos's has the right solution

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

wsfindlaterAuthor Commented:
Thanks to all of you

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now