How can cumulative percent be achieved with SQL?

Posted on 2004-11-30
Last Modified: 2010-08-05
                    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?
Question by:wsfindlater
    LVL 16

    Expert Comment

    Can you give the complete structure of the table? I presume there are other fields (a primary key for instance). Thanks.
    LVL 13

    Accepted Solution

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

    LVL 10

    Expert Comment

    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

    LVL 10

    Expert Comment

    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

    LVL 10

    Expert Comment


    I think KarinLoos's has the right solution

    LVL 6

    Expert Comment

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


    Author Comment

    Thanks to all of you

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    In this article—a derivative of my blog post (—I will explore a few different perspectives on which week today's date falls within using Microsoft SQL Server. First, to frame this stu…
    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    729 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now