SELECT InterventionType.*, InterventionLocation.*, InterventionService.* FROM
(SELECT SURef,
COALESCE([1],0) AS Phone,
COALESCE([2],0) AS [Assessment/Intervention],
COALESCE([3],0) AS Meeting,
COALESCE([4],0) AS Equipment,
COALESCE([5],0) AS [Admin],
COALESCE([6],0) AS Travel,
SUM(
COALESCE([1],0) +
COALESCE([2],0) +
COALESCE([3],0) +
COALESCE([4],0) +
COALESCE([5],0) +
COALESCE([6],0)
) AS TotalInterventions
FROM
(
SELECT SURef, InterventionType
FROM [BOCClientIndex - SU Interventions]
) src
pivot
(
COUNT(InterventionType)
for InterventionType in ([1], [2], [3], [4], [5], [6])
) piv
GROUP BY SURef,
COALESCE([1],0) ,
COALESCE([2],0) ,
COALESCE([3],0) ,
COALESCE([4],0) ,
COALESCE([5],0) ,
COALESCE([6],0)
) AS InterventionType
INNER JOIN
(SELECT SURef,
COALESCE([1],0) AS TeamBase,
COALESCE([2],0) AS [FamilyHomeRes],
COALESCE([3],0) AS Preschool,
COALESCE([4],0) AS School,
COALESCE([5],0) AS [DayService],
COALESCE([6],0) AS RespiteFamilyHomeShare,
COALESCE([7],0) AS [Other],
COALESCE([8],0) AS TeleHealth,
SUM(
COALESCE([1],0) +
COALESCE([2],0) +
COALESCE([3],0) +
COALESCE([4],0) +
COALESCE([5],0) +
COALESCE([6],0) +
COALESCE([7],0) +
COALESCE([8],0)
) AS TotalInterventions
from
(
SELECT SURef, InterventionLocation
FROM [BOCClientIndex - SU Interventions]
) src
pivot
(
COUNT(InterventionLocation)
FOR InterventionLocation in ([1], [2], [3], [4], [5], [6], [7], [8])
) piv
GROUP BY SURef,
COALESCE([1],0) ,
COALESCE([2],0) ,
COALESCE([3],0) ,
COALESCE([4],0) ,
COALESCE([5],0) ,
COALESCE([6],0) ,
COALESCE([7],0) ,
COALESCE([8],0)
) As InterventionLocation
ON InterventionType.SURef = InterventionLocation.SURef
INNER JOIN
(
SELECT SURef,
COALESCE([1],0) AS CommunityNursing,
COALESCE([2],0) AS OccupationalTherapy,
COALESCE([3],0) AS Paediatrics,
COALESCE([4],0) AS Physiotherapy,
COALESCE([5],0) AS Psychology,
COALESCE([6],0) AS PreSchoolLiaison,
COALESCE([7],0) AS SocialWork,
COALESCE([8],0) AS SpeechLanguage,
COALESCE([9],0) AS Psychiatry,
COALESCE([10],0) AS ClinicalNurse,
COALESCE([11],0) AS BehaviourSupport,
COALESCE([12],0) AS CommunityLinkWorker,
COALESCE([13],0) AS Psychotherapy,
COALESCE([14],0) AS AdvancedNursePractitioner,
COALESCE([15],0) AS AssistiveTechnology,
SUM(
COALESCE([1],0) +
COALESCE([2],0) +
COALESCE([3],0) +
COALESCE([4],0) +
COALESCE([5],0) +
COALESCE([6],0) +
COALESCE([7],0) +
COALESCE([8],0) +
COALESCE([9],0) +
COALESCE([10],0) +
COALESCE([11],0) +
COALESCE([12],0) +
COALESCE([13],0) +
COALESCE([14],0) +
COALESCE([15],0)
) AS TotalInterventions
from
(
select SURef, [Service]
from [BOCClientIndex - SU Interventions]
) src
pivot
(
COUNT([Service])
FOR [Service] in ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12], [13], [14], [15])
) piv
GROUP BY SURef,
COALESCE([1],0) ,
COALESCE([2],0) ,
COALESCE([3],0) ,
COALESCE([4],0) ,
COALESCE([5],0) ,
COALESCE([6],0) ,
COALESCE([7],0) ,
COALESCE([8],0) ,
COALESCE([9],0) ,
COALESCE([10],0) ,
COALESCE([11],0) ,
COALESCE([12],0) ,
COALESCE([13],0) ,
COALESCE([14],0) ,
COALESCE([15],0) ) AS InterventionService
ON InterventionType.SURef = InterventionService.SURef
ORDER BY InterventionLocation.SURef
