-
display count results and image in webpage
I am trying to count 5 categories, which will be based on the errorcode that has the most values. (eg errorcode1 was chosen 10 times, errocode2 was chosen 3 times all for category1, then the image in the cell next to category 1 should be red, because errorcode1 contained the most values for that category.
eg of how the webpage looks:
(5 categories, 5 errorcodes, 3 images for colorcode)
Ticket_view
Category 1 --> errorcode1(10) red
Category 2 --> errorcode2 (6) yellow
Category 3 --> errorcode3 (5) yellow
Category 4 --> errorcode4 (3) green
Category 5 --> errorcode5 (1) green
Color_code.dbo
legend:
green - 0-3 errors
yellow - 4-6 errors
red - 7+ errors
"SELECT category, COUNT(errorcode) AS id_number FROM ticket_view GROUP BY category"
-
here is the query i have been helped with so far:
SELECT
CASE
WHEN Count(e1.Group_Name) <= 3 Then Green
WHEN Count(e1..Group_Name > 3 AND Count(e1.ErrorCode) <= 6 Then Yellow
WHEN Count(e1..Group_Name) > 6 Then Red
END as [Uptime],
CASE
WHEN Count(e2..Group_Name) <= 3 Then Green
WHEN Count(e2..Group_Name) > 3 AND Count(e2.ErrorCode) <= 6 Then Yellow
WHEN Count(e2..Group_Name) > 6 Then Red
END as [Project]
CASE
WHEN Count(e3..Group_Name) <= 3 Then Green
WHEN Count(e3..Group_Name) > 3 AND Count(e2.ErrorCode) <= 6 Then Yellow
WHEN Count(e3..Group_Name) > 6 Then Red
END as [Personnel]
CASE
WHEN Count(e4..Group_Name) <= 3 Then Green
WHEN Count(e4..Group_Name) > 3 AND Count(e2.ErrorCode) <= 6 Then Yellow
WHEN Count(e4..Group_Name) > 6 Then Red
END as [Maintenance]
CASE
WHEN Count(e3..Group_Name) <= 3 Then Green
WHEN Count(e3..Group_Name) > 3 AND Count(e2.ErrorCode) <= 6 Then Yellow
WHEN Count(e3..Group_Name) > 6 Then Red
END as [IS Security]
FROM ticket_view e1
Left Join ticket_view e2 on e2.category = 'Category 2'
WHERE e1.category = 'Category 1'
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|