Nothing is happening
What is this about dropping table?
select *
from (
select *
from ( select
a.*,
b.Title as T
from (
select [System_Category].Title, [System_Category].Parent_ID
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
where [System_Category].Title not in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)'))a
left join (
select
[System_Category].Title,
[System_Category].Parent_ID
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
where [System_Category].Title in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)')
) b on a.Parent_ID = b.Parent_ID
) main
where Parent_ID = 136
)fox
pivot(count(Parent_ID) for T in ([H&S - Low (No Injury, Minor Injury)],[H&S - Medium (first Aid injury)],[H&S - High (LTI, Disability, Fatality)]))ptdid the above work?
zeros only
this is what @ScottPletcher suggested
try this:-
if OBJECT_ID('tempdb..#temp') is not null drop table #temp
select
[System_Category].Title,
[System_Category].Parent_ID
into #temp
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
SELECT
Title,
SUM(CASE WHEN ranking = 'H&S - Low (No Injury, Minor Injury)'THEN 1 ELSE 0 END) AS [H&S - Low (No Injury, Minor Injury)],
SUM(CASE WHEN ranking = 'H&S - Medium (first Aid injury)'THEN 1 ELSE 0 END) AS [H&S - Medium (first Aid injury)],
SUM(CASE WHEN ranking = 'H&S - High (LTI, Disability, Fatality)' THEN 1 ELSE 0 END) AS [H&S - High (LTI, Disability, Fatality)]
FROM (
SELECT
MAX(CASE WHEN is_ranking = 1 THEN '' ELSE Title END) AS Title,
MAX(CASE WHEN is_ranking = 1 THEN Title ELSE '' END) AS ranking
FROM #temp
CROSS APPLY (SELECT CASE WHEN Title IN ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)') THEN 1 ELSE 0 END AS is_ranking) AS alias1
GROUP BY Parent_ID
) AS derived
GROUP BY Title
ORDER BY TitleThat one is worse.
its not easy trying to figure this out when I cant run the query and see results for myself I tried it on a dummy data and it seems to work fine
my query and @ScottPletcher query produces the same results don't know why your data is not being pulled back
if OBJECT_ID('tempdb..#temp') is not null drop table #temp
Select *
into #temp
from (
select 1 Parent_ID,'H&S - Low (No Injury, Minor Injury)'Title
union all select 1,'a'
union all select 2,'b'
union all select 2,'H&S - High (LTI, Disability, Fatality)'
union all select 3,'c'
union all select 3,'H&S - Low (No Injury, Minor Injury)'
union all select 4,'b'
union all select 4,'H&S - High (LTI, Disability, Fatality)'
union all select 5,'c'
union all select 5,'H&S - Medium (first Aid injury)'
union all select 6,'H&S - Medium (first Aid injury)'
union all select 6,'c'
union all select 7,'a'
union all select 7,'H&S - High (LTI, Disability, Fatality)'
union all select 8,'a'
union all select 8,'H&S - Medium (first Aid injury)'
) main
select *
from (
select
a.*
,b.Title as T
from (select Title, Parent_ID from #temp where Title not in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)'))a
left join (select Title, Parent_ID from #temp where Title in ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)')) b on a.Parent_ID = b.Parent_ID
) main
pivot(count(Parent_ID) for T in ([H&S - Low (No Injury, Minor Injury)],[H&S - Medium (first Aid injury)],[H&S - High (LTI, Disability, Fatality)]))pt
SELECT
Title,
SUM(CASE WHEN ranking = 'H&S - Low (No Injury, Minor Injury)'THEN 1 ELSE 0 END) AS [H&S - Low (No Injury, Minor Injury)],
SUM(CASE WHEN ranking = 'H&S - Medium (first Aid injury)'THEN 1 ELSE 0 END) AS [H&S - Medium (first Aid injury)],
SUM(CASE WHEN ranking = 'H&S - High (LTI, Disability, Fatality)' THEN 1 ELSE 0 END) AS [H&S - High (LTI, Disability, Fatality)]
FROM (
SELECT
MAX(CASE WHEN is_ranking = 1 THEN '' ELSE Title END) AS Title,
MAX(CASE WHEN is_ranking = 1 THEN Title ELSE '' END) AS ranking
FROM #temp
CROSS APPLY (SELECT CASE WHEN Title IN ('H&S - Low (No Injury, Minor Injury)','H&S - Medium (first Aid injury)','H&S - High (LTI, Disability, Fatality)') THEN 1 ELSE 0 END AS is_ranking) AS alias1
GROUP BY Parent_ID
) AS derived
GROUP BY Title
ORDER BY Title
1 Like
Wonder how much time I spent to make it working until decided to seek for help here.
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.