Using a count as a percentage to determine results

Badja 0
Viewed 43

I need to compare the StatusID's of a job in the last 12 hours. If the value is over 50%, an alert is created.

select StatusID, count(statusid) as [count]
from job nolock 
where jobtypeid = 5033 
    and ModifiedOn > CONVERT(datetime,dateadd(hh,-12,getdate()),104)
group by statusid
order by 1 desc

StatusIDCount

This gives me my results, but I need it as part of:

CASE WHEN "Count(StatusID = 5 > 50%)" THEN ''GREEN'' ELSE ''RED''

How can I turn the top select into a Case?

sql
2 Answers

Please explain how you are going to determine the percentage? If you just want to check count(statusid)>50 then:

select StatusID, count(statusid) as [count], (case when count(statusid)>50 then 'GREEN' else 'RED' end)
from job nolock 
where jobtypeid = 5033 
    and ModifiedOn > CONVERT(datetime,dateadd(hh,-12,getdate()),104)
group by statusid
order by 1 desc
Kazi Mohammad Ali Nur0

Try the following:

SELECT jobtypeid,  StatusID,
       CASE WHEN StatusID=5 AND 
                 CNT*1.0 /SUM(CNT) OVER (PARTITION BY jobtypeid)>0.5 
            THEN 'RED' ELSE 'GREEN'
       END AS Status5Result
  FROM
  (
    SELECT jobtypeid,  StatusID, 
       COUNT(*) CNT
    From job 
    WHERE jobtypeid = 5033 AND
          ModifiedOn > CONVERT(datetime,dateadd(hh,-12,getdate()),104)
    GROUP BY jobtypeid,  StatusID
  ) T
ORDER BY StatusID

See a demo.

The subquery finds the count for each StatusID within the jobtypeid, and the main query uses a sum window function to find the sum of all StatusID count within a jobtypeid.

userGpjR0
Related