title: Multiple case statement sum
body: <p>So I have the following columns of data in a table:</p>
  
  <pre><code>workordernum | targstart | targfinish | schedstart | schedfinish | actstart | actfinish
  </code></pre>
  
  <p>I need to return the following result set:</p>
  
  <pre><code>rating | count
  </code></pre>
  
  <p>Sample:</p>
  
  <pre><code>RED    |  0
  ORANGE |  1
  YELLOW |  4
  GREEN  | 38
  </code></pre>
  
  <p>The rating is based on the following criteria:</p>
  
  <pre><code>acfinish &lt;= targfinish (test 0)
  actstart &lt;= schedstart (test 1)
  actfinish &lt;= schedfinish (test 2)
  targstart &lt;= (a calculated column called "halflife") (test 3)
  </code></pre>
  
  <p>If test 0 fails, then the work order is automatically rated as "RED". For tests 1, 2, and 3, they get rated as "GREEN" if they passed all 3, "YELLOW" if they pass any 2, "ORANGE" if they only pass 1, and "RED" if they pass none (or if they fail test 0)</p>
  
  <p>What is the best way to handle this in a SQL query? My current query uses CASE statements to evaluate each test to a 1 or 0, but then I'd need to sum them up and do some sort of IF statement to handle test 0. My guess is that I'm either over-complicating it or lack knowledge of a little SQL function that might help.</p>
  
  <p>Any and all help is appreciated and thank you in advance!</p>
  
  <p><hr>
  Edit 1: Current code (as requested)</p>
  
  <pre><code>SELECT
      wo.wonum,
      wo.targstartdate,
      wo.targcompdate,
      wo.schedstart,
      wo.schedfinish,
      wo.actstart,
      wo.actfinish,
      FLOOR(
          DATEDIFF(
              DAY,
              targstartdate,
              CASE pm.frequnit
                  WHEN 'YEARS' THEN DATEADD(YEAR,pm.frequency,wo.targstartdate)
                  WHEN 'MONTHS' THEN DATEADD(MONTH,pm.frequency,wo.targstartdate)
                  WHEN 'WEEKS' THEN DATEADD(WEEK,pm.frequency,wo.targstartdate)
                  WHEN 'DAYS' THEN DATEADD(DAY,pm.frequency,wo.targstartdate)
                  ELSE targstartdate
              END
          )
      ) AS halflife,
      CASE 
          WHEN wo.actfinish &lt; wo.targcompdate THEN 1
          ELSE 0
      END AS test0,
      CASE
          WHEN wo.actstart &lt;= wo.schedstart THEN 1
          ELSE 0
      END AS test1,
      CASE
          WHEN wo.actfinish &lt;= wo.schedfinish THEN 1
          ELSE 0
      END AS test2,
      CASE
          WHEN wo.schedstart &lt;= DATEADD(DAY,FLOOR(DATEDIFF(DAY,wo.targstartdate,CASE pm.frequnit WHEN 'YEARS' THEN DATEADD(YEAR,pm.frequency,wo.targstartdate) WHEN 'MONTHS' THEN DATEADD(MONTH,pm.frequency,wo.targstartdate) WHEN 'WEEKS' THEN DATEADD(WEEK,pm.frequency,wo.targstartdate) WHEN 'DAYS' THEN DATEADD(DAY,pm.frequency,wo.targstartdate) ELSE wo.targstartdate END)),wo.targstartdate) THEN 1
          ELSE 0
      END AS test3
  FROM 
      workorder AS wo
      LEFT OUTER JOIN pm ON pm.pmnum=ISNULL(wo.pmnum,(SELECT pmnum FROM workorder WHERE wonum=wo.parent))
  WHERE 
      wo.status IN (SELECT value FROM synonymdomain WHERE domainid='WOSTATUS' AND maxvalue IN ('COMP','CLOSE','HISTEDIT'))
      AND wo.istask=0
      AND DATEDIFF(MONTH,wo.actfinish,GETDATE())=1
      AND wo.worktype='PM'
  </code></pre>
  
  <p><hr>
  Edit 2:</p>
  
  <p>I updated my code using the following thought Test 0 returns either 1 or 0 so I multiply that by the sum of the Test 1, 2, and 3, that way it will always return 0 if that one fails. I then use the CASE statement to label it with the appropriate color. However, now I'm getting a list of workorder numbers and their rating and I need to adapt it to a list of ratings and their counts!</p>
  
  <pre><code>SELECT
      wo.wonum,
      CASE
          (CASE 
              WHEN wo.actfinish &lt; wo.targcompdate THEN 1
              ELSE 0
          END *
          (CASE
              WHEN wo.actstart &lt;= wo.schedstart THEN 1
              ELSE 0
          END +
          CASE
              WHEN wo.actfinish &lt;= wo.schedfinish THEN 1
              ELSE 0
          END +
          CASE
              WHEN wo.schedstart &lt;= DATEADD(DAY,FLOOR(DATEDIFF(DAY,wo.targstartdate,CASE pm.frequnit WHEN 'YEARS' THEN DATEADD(YEAR,pm.frequency,wo.targstartdate) WHEN 'MONTHS' THEN DATEADD(MONTH,pm.frequency,wo.targstartdate) WHEN 'WEEKS' THEN DATEADD(WEEK,pm.frequency,wo.targstartdate) WHEN 'DAYS' THEN DATEADD(DAY,pm.frequency,wo.targstartdate) ELSE wo.targstartdate END)),wo.targstartdate) THEN 1
              ELSE 0
          END))
          WHEN 3 THEN 'GREEN'
          WHEN 2 THEN 'YELLOW'
          WHEN 1 THEN 'ORANGE'
          WHEN 0 THEN 'RED' 
          ELSE 'RED'
      END
  FROM 
      workorder AS wo
      LEFT OUTER JOIN pm ON pm.pmnum=ISNULL(wo.pmnum,(SELECT pmnum FROM workorder WHERE wonum=wo.parent))
  WHERE 
      wo.status IN (SELECT value FROM synonymdomain WHERE domainid='WOSTATUS' AND maxvalue IN ('COMP','CLOSE','HISTEDIT'))
      AND wo.istask=0
      AND DATEDIFF(MONTH,wo.actfinish,GETDATE())=1
      AND wo.worktype='PM'
  </code></pre>
  
  <p><hr>
  FINAL EDIT:
  Just for completeness, I have put the final code below:</p>
  
  <pre><code>SELECT rating,count(*)
  FROM
  (SELECT
      CASE
          (CASE 
              WHEN wo.actfinish &lt; wo.targcompdate THEN 1
              ELSE 0
          END *
          (CASE
              WHEN wo.actstart &lt;= wo.schedstart THEN 1
              ELSE 0
          END +
          CASE
              WHEN wo.actfinish &lt;= wo.schedfinish THEN 1
              ELSE 0
          END +
          CASE
              WHEN wo.schedstart &lt;= DATEADD(DAY,FLOOR(DATEDIFF(DAY,wo.targstartdate,CASE pm.frequnit WHEN 'YEARS' THEN DATEADD(YEAR,pm.frequency,wo.targstartdate) WHEN 'MONTHS' THEN DATEADD(MONTH,pm.frequency,wo.targstartdate) WHEN 'WEEKS' THEN DATEADD(WEEK,pm.frequency,wo.targstartdate) WHEN 'DAYS' THEN DATEADD(DAY,pm.frequency,wo.targstartdate) ELSE wo.targstartdate END)),wo.targstartdate) THEN 1
              ELSE 0
          END))
          WHEN 3 THEN 'GREEN'
          WHEN 2 THEN 'YELLOW'
          WHEN 1 THEN 'ORANGE'
          WHEN 0 THEN 'RED' 
          ELSE 'RED'
      END AS rating
  FROM 
      workorder AS wo
      LEFT OUTER JOIN pm ON pm.pmnum=ISNULL(wo.pmnum,(SELECT pmnum FROM workorder WHERE wonum=wo.parent))
  WHERE 
      wo.status IN (SELECT value FROM synonymdomain WHERE domainid='WOSTATUS' AND maxvalue IN ('COMP','CLOSE','HISTEDIT'))
      AND wo.istask=0
      AND DATEDIFF(MONTH,wo.actfinish,GETDATE())=1
      AND wo.worktype='PM'
  ) AS worating
  GROUP BY rating
  </code></pre>