asked on

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?

muzzy2003

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

ASKER CERTIFIED SOLUTION

KarinLoos

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

imrancs

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

imrancs

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

imrancs

sorry

I think KarinLoos's has the right solution

Imran

OlegP

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]

wsfindlater

ASKER

Thanks to all of you