Hi there
I am trying to produce a table that looks like this:
As you can see it has subtotals and a grand total.
My query is working (the data in the table is slightly wrong, due to how it was downloaded from the database), but I don't know how to put in the subtotals and the grand total. I have made an attempt below.
--Incident Reporting for Restraint Committee
use [exoMashTrust-test]
declare
@startdate datetime
,@enddate datetime
--Change these for each month's report
SET @startdate = '20150501 00:00:00.000'
SET @enddate = '20150531 23:59:59.999'
--Pivot Listing
SELECT *
FROM (
SELECT
PIP.INCIDENT_NAME as [Category]
,PIO.OPTION_NAME as [Incident Type]
,CASE
WHEN
PATI.INCIDENT_AREA IN (7,8,76,9,10,11,12,13,70,72,75,73,74,71,27,69,28,29,30,31,32,67,33,34,80,35,36,37,38,39)
THEN 'ID'
WHEN
PATI.INCIDENT_AREA = 22
THEN 'PD'
WHEN
PATI.INCIDENT_AREA IN (48,49,41,42)
THEN 'MH Res PN'
WHEN
PATI.INCIDENT_AREA IN (47,52,46,45,43,44,81)
THEN 'MH Res Wgtn'
WHEN
PATI.INCIDENT_AREA IN (50,51)
THEN 'Terrace End'
WHEN
PATI.INCIDENT_AREA IN (18,53,77,78)
THEN 'AOD/DD'
WHEN
PATI.INCIDENT_AREA IN (25,24,23,79)
THEN 'Vocational'
WHEN
PATI.INCIDENT_AREA IN (25,24,23,79)
THEN 'Vocational'
WHEN
PATI.INCIDENT_AREA = 6
THEN 'Te Matai'
WHEN
PATI.INCIDENT_AREA = 40
THEN 'Luck'
WHEN
PATI.INCIDENT_AREA in (19,20)
THEN 'Offices'
WHEN
PATI.INCIDENT_AREA = 6
THEN 'Healthy Lifestyles'
WHEN
PATI.INCIDENT_AREA in (55,56,57,60,63,64,16,17,58,59)
THEN 'MH Level 2 PN'
WHEN
PATI.INCIDENT_AREA = 66
THEN 'MH Bluegum'
ELSE 'Other'
END AS [Area]
,pati.INCIDENT_DATE as [Date of Incident]
FROM
PATIENT_INCIDENTS AS pati
JOIN PATIENT_INC_PARENT AS PIP
ON PATI.PARENT_ID=PIP.SEQNO
JOIN PATIENT_INC_OPTIONS AS pio
ON pio.SEQNO = pati.OPTION_ID
JOIN PATIENT_INC_AREA AS PIA
ON PIA.AREA_ID=PATI.INCIDENT_AREA
WHERE
pati.PARENT_ID IN (3,6,9)
AND PATI.INCIDENT_DATE BETWEEN @startdate AND @enddate
----SUBTOTAL ATTEMPT
--GROUP BY
-- PIP.INCIDENT_NAME
-- ,PIO.OPTION_NAME
-- ,CASE
-- WHEN
-- PATI.INCIDENT_AREA IN (7,8,76,9,10,11,12,13,70,72,75,73,74,71,27,69,28,29,30,31,32,67,33,34,80,35,36,37,38,39)
-- THEN 'ID'
-- WHEN
-- PATI.INCIDENT_AREA = 22
-- THEN 'PD'
-- WHEN
-- PATI.INCIDENT_AREA IN (48,49,41,42)
-- THEN 'MH Res PN'
-- WHEN
-- PATI.INCIDENT_AREA IN (47,52,46,45,43,44,81)
-- THEN 'MH Res Wgtn'
-- WHEN
-- PATI.INCIDENT_AREA IN (50,51)
-- THEN 'Terrace End'
-- WHEN
-- PATI.INCIDENT_AREA IN (18,53,77,78)
-- THEN 'AOD/DD'
-- WHEN
-- PATI.INCIDENT_AREA IN (25,24,23,79)
-- THEN 'Vocational'
-- WHEN
-- PATI.INCIDENT_AREA IN (25,24,23,79)
-- THEN 'Vocational'
-- WHEN
-- PATI.INCIDENT_AREA = 6
-- THEN 'Te Matai'
-- WHEN
-- PATI.INCIDENT_AREA = 40
-- THEN 'Luck'
-- WHEN
-- PATI.INCIDENT_AREA in (19,20)
-- THEN 'Offices'
-- WHEN
-- PATI.INCIDENT_AREA = 6
-- THEN 'Healthy Lifestyles'
-- WHEN
-- PATI.INCIDENT_AREA in (55,56,57,60,63,64,16,17,58,59)
-- THEN 'MH Level 2 PN'
-- WHEN
-- PATI.INCIDENT_AREA = 66
-- THEN 'MH Bluegum'
-- ELSE 'Other'
-- END
-- ,pati.INCIDENT_DATE
--WITH ROLLUP
) AS A
pivot(count([Date of Incident]) FOR [Area] IN (
[ID]
,[PD]
,[MH Res PN]
,[MH Res Wgtn]
,[MH Bluegum]
,[MH Level 2 PN]
,[Terrace End]
,[AOD/DD]
,[Vocational]
,[Te Matai]
,[LUCK]
,[Offices]
,[Healthy Lifestyles]
,[Other]
)) AS p
ORDER BY
Category
;
This is what I get with the subtotal line commented out:
When I uncomment those lines I get this, so my code is clearly wrong. But I can't figure out what to do.
Hopefully someone can help.