Multiple case statement sum

Question

So I have the following columns of data in a table:

workordernum | targstart | targfinish | schedstart | schedfinish | actstart | actfinish

I need to return the following result set:

rating | count

Sample:

RED    |  0
ORANGE |  1
YELLOW |  4
GREEN  | 38

The rating is based on the following criteria:

acfinish <= targfinish (test 0)
actstart <= schedstart (test 1)
actfinish <= schedfinish (test 2)
targstart <= (a calculated column called "halflife") (test 3)

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)

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.

Any and all help is appreciated and thank you in advance!

Edit 1: Current code (as requested)

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 < wo.targcompdate THEN 1
        ELSE 0
    END AS test0,
    CASE
        WHEN wo.actstart <= wo.schedstart THEN 1
        ELSE 0
    END AS test1,
    CASE
        WHEN wo.actfinish <= wo.schedfinish THEN 1
        ELSE 0
    END AS test2,
    CASE
        WHEN wo.schedstart <= 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'

Edit 2:

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!

SELECT
    wo.wonum,
    CASE
        (CASE 
            WHEN wo.actfinish < wo.targcompdate THEN 1
            ELSE 0
        END *
        (CASE
            WHEN wo.actstart <= wo.schedstart THEN 1
            ELSE 0
        END +
        CASE
            WHEN wo.actfinish <= wo.schedfinish THEN 1
            ELSE 0
        END +
        CASE
            WHEN wo.schedstart <= 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'

FINAL EDIT: Just for completeness, I have put the final code below:

SELECT rating,count(*)
FROM
(SELECT
    CASE
        (CASE 
            WHEN wo.actfinish < wo.targcompdate THEN 1
            ELSE 0
        END *
        (CASE
            WHEN wo.actstart <= wo.schedstart THEN 1
            ELSE 0
        END +
        CASE
            WHEN wo.actfinish <= wo.schedfinish THEN 1
            ELSE 0
        END +
        CASE
            WHEN wo.schedstart <= 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

AHiggins · Accepted Answer

Can you check the sum of the tests you performed in your query, such as with the following?

SELECT 
    wonum,
    targstartdate,
    targcompdate,
    schedstart,
    schedfinish,
    actstart,
    actfinish,
    halflife,
    CASE
        WHEN test0 + test1 + test2 + test3 >= 3 THEN 'RED'
        WHEN test0 + test1 + test2 + test3 = 2 THEN 'ORANGE'
        WHEN test0 + test1 + test2 + test3 = 1 THEN 'YELLOW'
        WHEN test0 + test1 + test2 + test3 = 0 THEN 'GREEN'
    END AS Flag
FROM 
  (
    SELECT .... yourquery
  ) s

EDIT 1: forgot to add that in your query, you should evaluate the major case (test0, right?) as 4, not 1, i.e.,

CASE 
    WHEN wo.actfinish < wo.targcompdate THEN 4
    ELSE 0
END AS test0,

That way, anything that fails Test Zero is automatically going to sum up to Red-level totals.

EDIT 2: adding the CASE statement to a GROUP BY. If you only want to see the statement and a count, this query should do what you are looking for.

SELECT 
    CASE
        WHEN test0 + test1 + test2 + test3 >= 3 THEN 'RED'
        WHEN test0 + test1 + test2 + test3 = 2 THEN 'ORANGE'
        WHEN test0 + test1 + test2 + test3 = 1 THEN 'YELLOW'
        WHEN test0 + test1 + test2 + test3 = 0 THEN 'GREEN'
    END AS Rating,
    COUNT(wonum) AS TotalRecords
FROM 
  (
    SELECT .... yourquery
  ) s

GROUP BY 
    CASE
        WHEN test0 + test1 + test2 + test3 >= 3 THEN 'RED'
        WHEN test0 + test1 + test2 + test3 = 2 THEN 'ORANGE'
        WHEN test0 + test1 + test2 + test3 = 1 THEN 'YELLOW'
        WHEN test0 + test1 + test2 + test3 = 0 THEN 'GREEN'
    END

Multiple case statement sum

Answers (2)

Related Questions