the thing is it will pull back everything don't filter on ID 136 yet just see if the data is being pulled back for other ID's?
I think I'm going to have to leave this for now and just come back letter when brain is fresh. I've been looking for too long at this for past two days.
Muj9, big thank you for your efforts, really appreciated you spend your time trying to help some random person.
My query is no longer using [System_Category].Title, it's using the Title it created, so just use the name Title (or title). [I didn't bold the text below, I have no idea why the site is bolding some of the code.]
SELECT
Title, --<<--no prefix on this column name
SUM(CASE WHEN ranking = 'H&S - Low' THEN 1 ELSE 0 END) AS low,
SUM(CASE WHEN ranking = 'H&S - Medium' THEN 1 ELSE 0 END) AS mid,
SUM(CASE WHEN ranking = 'H&S - High' THEN 1 ELSE 0 END) AS high
FROM (
SELECT
MAX(CASE WHEN is_ranking = 1 THEN '' ELSE [System_Category].Title END) AS title,
MAX(CASE WHEN is_ranking = 1 THEN [System_Category].Title ELSE '' END) AS ranking
FROM
[Envoy].[dbo].[Incident]
JOIN [System_Category_Module] on [System_Category_Module].Module_ID = [Incident].ID
JOIN [System_Category] on [System_Category].ID = [System_Category_Module].Category_ID
CROSS APPLY (
SELECT CASE WHEN [System_Category].Title IN ('H&S - Low', 'H&S - Medium', 'H&S - High') THEN 1 ELSE 0 END AS is_ranking
) AS alias1
GROUP BY [Incident].ID
) AS derived
GROUP BY Title --<<--no prefix on this column name
ORDER BY Title --<<--no prefix on this column name
Don't worry about bolding text. That doesn't bother me.
Your last code seems to work, but not sure if correctly, at least I started seeing some results, not just zeros.
Although, I have to add some time and title filters.
WHERE ([Incident].Inc_Date >= DATEADD(yy, DATEDIFF(yy, 0, GETDATE()), 0) AND [Incident].Inc_Date <= DATEADD(m, DATEDIFF(m, -1, GETDATE())-1, -1))
[System_Category].Parent_ID = 136
Wasn't sure how do I do that.
As soon as I add [System_Category].Parent_ID = 136 between CROSS APPLY.. and GROUP BY [Incident].ID, query starts to produce zeros only.
@eka_eka you might have a space in your value for example this is what I have written as a value in the Where clause
Title IN ('H&S - Low (No Injury, Minor Injury)')
but your value could be
Title IN ('H&S - Low (No Injury, Minor Injury) ')
What you should do is copy and paste the values directly from your sql query result into the query you are writing
It is not free text field, it is being hooked up with drop down field on the application.
I've pulled data and did manual calculation - above code doesn't count it correctly.