Spreadsheet: 750245d1633356941-automatically-fill-summary-table-with-filtered-data-based-on-drop-down-list-example
Sheet: SQL

USE [EmergencyDW]
DECLARE @Sdte SMALLDATETIME, @Edte SMALLDATETIME
SET @Sdte='2019-04-01'
SET @Edte='2019-12-31'
DECLARE @SincID BIGINT, @EincID BIGINT
SET @SincID=CAST(CONVERT(VARCHAR(10),@Sdte,112) AS BIGINT)*1000000
SET @EincID=CAST(CONVERT(VARCHAR(10),@Edte,112) AS BIGINT)*1000000+999999
SELECT
 | z1.incidentID,
 | z1.[Date],
 | z1.[Weekending],
 | z1.[CAD],
 | [NAME],
 | z1.[Sector],
 | z1.[Station Group],
 | z1.[Blue Call Y/N],
 | [NAME]],
 | z1.[Outcome],
 | z1.[Running],
 | [NAME],
 | z1.[ArrivedAtHospitalToPatientHandover],
 | z1.[PatientHandoverToGreen],
 | z1.[JobCycle],
 | ISNULL(ill1.illnesscode,'') [NAME], ISNULL(ill1.illnesstype,'Not Recorded') [Illnesstype1],
 | ISNULL(ill2.illnesscode,'') [NAME], ISNULL(ill2.illnesstype,'') [Illnesstype2],
 | ISNULL(ill3.illnesscode,'') ill3, ISNULL(ill3.illnesstype,'') [Illnesstype3],
 | ISNULL(ill4.illnesscode,'') [NAME], ISNULL(ill4.illnesstype,'') [Illnesstype4]
FROM
(
SELECT
 | a.incidentid,
 | a.cadref [CAD],
 | dh.date [Date],
 | dh.month [Month],
 | dh.weekending [Weekending],
 | cs.sectorname [Sector],
 | cs.complexname [Station Group],
 | CASE WHEN am.ampdscode LIKE 'DX%' AND c.chub=1 THEN 'CHUB Adastra'
 | WHEN c.mps=1 THEN 'Police Incident'
 | ELSE am.chiefcomplaint END [Chief Complaint],
 | CASE WHEN a.bluecall IS NOT NULL THEN 1 ELSE 0 END [Blue Call Y/N],
 | CASE WHEN h.conveyed=1 THEN 1 ELSE 0 END [Conveyed Y/N],
 | h.hospitalname [Outcome],
 | CASE WHEN a.mobilisation < 0 THEN NULL ELSE CAST(a.mobilisation AS FLOAT)/60 END [Mobilisation],
 | CASE WHEN a.running < 0 THEN NULL ELSE CAST(a.running AS FLOAT)/60 END [Running],
 | CASE WHEN a.onscene < 0 THEN NULL ELSE CAST(a.onscene AS FLOAT)/60 END [OnScene],
 | CASE WHEN a.tohospital < 0 THEN NULL ELSE CAST(a.tohospital AS FLOAT)/60 END [ToHospital],
 | CASE WHEN a.arrivedathospitaltopatienthandover < 0 THEN NULL ELSE
 | CAST(a.arrivedathospitaltopatienthandover AS FLOAT)/60 END [ArrivedAtHospitalToPatientHandover],
 | CASE WHEN a.patienthandovertogreen < 0 THEN NULL ELSE
 | CAST(a.patienthandovertogreen AS FLOAT)/60 END [PatientHandoverToGreen],
 | CASE WHEN a.hospitalturnaround < 0 THEN NULL ELSE CAST(a.hospitalturnaround AS FLOAT)/60 END[HospitalTurnaround],
 | CASE WHEN a.jobcycle < 0 THEN NULL ELSE CAST(a.jobcycle AS FLOAT)/60 END [JobCycle]
FROM activation a WITH (NOLOCK)
 | INNER JOIN datehour dh ON dh.datehourkey = a.dispatch_datehour
 | INNER JOIN callsign cs ON cs.callsignkey = a.callsignkey
 | INNER JOIN ampdscodes am ON a.ampdsKey=am.ampdskey
 | INNER JOIN call c WITH (NOLOCK) ON c.incidentid=a.incidentID
 | INNER JOIN hospital h ON a.hospitalcode=h.hospitalcode
WHERE a.incidentid BETWEEN @SincID AND @EincID
 | AND cs.patientfacing='patient facing'
 | AND cs.respondergroup='double crewed ambulance'
 | AND a.arrivedatscene IS NOT NULL
 | AND c.Contacts>0
)z1
LEFT JOIN
(
SELECT s1.incidentID, s1.illnesscode, ill.illnesstype
FROM
(
SELECT incidentID, cast(illnesscode as varchar(3)) illnesscode,
ROW_NUMBER() OVER (PARTITION BY incidentID ORDER BY incidentID, illnesscode) ILLno
FROM EmergencyDW.dbo.incident_illnesstypes ill WITH (NOLOCK)
WHERE incidentid between @SincID AND @EincID
) s1
LEFT JOIN EmergencyDW.dbo.illnesstype ill WITH (NOLOCK)
ON s1.illnesscode=ill.illnesscode
WHERE ILLno=1
) ILL1
ON z1.incidentID=ILL1.incidentID
LEFT JOIN
(
SELECT s1.incidentID, s1.illnesscode, ill.illnesstype
FROM
(
SELECT incidentID, cast(illnesscode as varchar(3)) illnesscode,
ROW_NUMBER() OVER (PARTITION BY incidentID ORDER BY incidentID, illnesscode) ILLno
FROM EmergencyDW.dbo.incident_illnesstypes ill WITH (NOLOCK)
WHERE incidentid between @SincID AND @EincID
) s1
LEFT JOIN EmergencyDW.dbo.illnesstype ill WITH (NOLOCK)
ON s1.illnesscode=ill.illnesscode
WHERE ILLno=2
) ILL2
ON z1.incidentID=ILL2.incidentID
LEFT JOIN
(
SELECT s1.incidentID, s1.illnesscode, ill.illnesstype
FROM
(
SELECT incidentID, cast(illnesscode as varchar(3)) illnesscode,
ROW_NUMBER() OVER (PARTITION BY incidentID ORDER BY incidentID, illnesscode) ILLno
FROM EmergencyDW.dbo.incident_illnesstypes ill WITH (NOLOCK)
WHERE incidentid between @SincID AND @EincID
) s1
LEFT JOIN EmergencyDW.dbo.illnesstype ill WITH (NOLOCK)
ON s1.illnesscode=ill.illnesscode
WHERE ILLno=3
) ILL3
ON z1.incidentID=ILL3.incidentID
LEFT JOIN
(
SELECT s1.incidentID, s1.illnesscode, ill.illnesstype
FROM
(
SELECT incidentID, cast(illnesscode as varchar(3)) illnesscode,
ROW_NUMBER() OVER (PARTITION BY incidentID ORDER BY incidentID, illnesscode) ILLno
FROM EmergencyDW.dbo.incident_illnesstypes ill WITH (NOLOCK)
WHERE incidentid between @SincID AND @EincID
) s1
LEFT JOIN EmergencyDW.dbo.illnesstype ill WITH (NOLOCK)
ON s1.illnesscode=ill.illnesscode
WHERE ILLno=4
) ILL4
ON z1.incidentID=ILL4.incidentID
ORDER BY z1.incidentid