sorry my mistake try now
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
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
It did successfully run, but generated only zeros and pulled all categories on the left hand side, I only need that refer to parent_id = 136
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
where a.Parent_ID = 136
) 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
This pull exactly what I wanted, but it doesn't count by 'HS - Low (No Injury, Minor Injury)','HS - Medium (first Aid injury)' and 'HS - High (LTI, Disability, Fatality)' and returns zeros.
double check your data as this is counting T which is the second table in your query and looks right to me
I have just run my table with this and it clearly returns required data.
select [Incident].ID, [System_Category].Parent_ID,[System_Category].Title
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
ORDER BY [incident].ID
send me a screen shot for this query
select [Incident].ID, [System_Category].Parent_ID,[System_Category].Title
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].Parent_ID = 136
ORDER BY [incident].ID
run this please
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 a.*,b.Title as T
from (
select * 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 * 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
where a.Parent_ID = 136
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)]))pt
did 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 Title
That 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
Wonder how much time I spent to make it working until decided to seek for help here.