CODEWITHSUNDEEP
sqlsql-server
Rohit
Rohit

Reputation: 145

Rolling up data in SQL

I am new to SQL and facing an issue in rolling up data by Grouping. Consider this: 

PR_ProjectName  CategoryName        Compliant   AssessmentID    ComplianceRAG
CUNA            policy compliance   Yes                1        Green
CUNA            policy compliance   Yes                2        Green
CUNA            policy compliance   Yes                3        Green
CUNA            policy compliance   No                 4        Red
CUNA            Inventory           Yes                5        Red
CUNA            Security            No                 6        Red
CUNA            Security            Yes                7        Green
CUNA            Security            No                 8        Red
CUNA            Security            Yes                9        Green

The last column should send me rolled up data, like if any of Compliance is No for a particular category, it shall send me RED for all columns in ComplainceRAG. Example for all 4 rows of 'policy complaince' it must return me RED since atleast 1 column in RED. For 'Inventory' it should be GREEN.

Below is my current query:(Please guide me what modification my CASE statement needs):

SELECT  PDD.PR_ProjectName, IC.CategoryName, IA.Compliant, IA.AssessmentID,
            CASE
            WHEN IA.Compliant = 'Yes' THEN 'Green'
            WHEN IA.Compliant = 'No' THEN 'Red'
            END as ComplianceRAG
    FROM InfoSecAssessment IA
         INNER JOIN InfoSecConduct ICon ON Icon.ConductID = IA.ConductID
         INNER JOIN PROJECT_DIM_DM PDD ON PDD.PR_ProjectId = Icon.ProjectID
         INNER JOIN InfoSecQuestionMaster IQ ON IA.QuestionID = IQ.QuestionID
         INNER JOIN InfoSecControlCategoryMaster IC ON IC.CategoryID = IQ.CategoryID
    WHERE AssessmentSubmitted = 1 AND
     PDD.pr_isActive=1 and PDD.PR_EFF_END_DATE='31Dec3000'
    GROUP BY IC.CategoryName, PDD.PR_ProjectName, IA.AssessmentID, IA.Compliant

Upvotes: 2

Views: 128

Answers (2)

benbotto
benbotto

Reputation: 2439

I made a simplified SQL Fiddle here: http://sqlfiddle.com/#!2/16eba/15/2

  • The first query shows the entire data set.
  • The second query shows all projects with a failure in at least one category. In this case project 1 has a failure in inventory and project 2 has no failures.
  • The third query marries the two queries. Any project that has a failure in at least one category is marked RED.

Hope that helps!

Upvotes: 1

FuzzyTree
FuzzyTree

Reputation: 32402

Add the following left join to a derived table of all categories with at least 1 'No'.

LEFT JOIN (
    SELECT DISTINCT IC.CategoryName
    FROM InfoSecControlCategoryMaster IC 
    JOIN InfoSecQuestionMaster IQ ON IC.CategoryID = IQ.CategoryID
    JOIN InfoSecAssessment IA ON IA.QuestionID = IQ.QuestionID
    WHERE IA.Compliant = 'No'
) red ON red.CategoryName = IC.CategoryName

Then change your case statement to check if the category is in the derived table

CASE WHEN red.CategoryName IS NULL
THEN 'Green' ELSE 'Red'
END AS ComplianceRAG

Upvotes: 2

Related Questions