Repository navigation
Expand file tree
/
Copy pathhealthcare_ems_dispatch.sql
More file actions
135 lines (123 loc) · 11.4 KB
/
Copy pathhealthcare_ems_dispatch.sql
File metadata and controls
135 lines (123 loc) · 11.4 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
/*
Dremio High-Volume SQL Pattern: Healthcare EMS Dispatch Optimization
Business Scenario:
Emergency Medical Services (EMS) need to minimize "Chute Time" (dispatch to wheels rolling)
and "Travel Time" to improve survival rates.
Data Story:
We ingest CAD (Computer Aided Dispatch) logs.
Medallion Architecture:
- Bronze: CAD_Logs (Call, Dispatch, Arrival Times).
*Volume*: 50+ records.
- Silver: Response Metrics (Chute Time & Travel Time).
- Gold: Coverage Heatmap by Zip Code.
Key Dremio Features:
- TIMESTAMPDIFF for multiple intervals
- Aggregation by Zip
*/
-------------------------------------------------------------------------------
-- 0. SETUP: Create Folder Structure
-------------------------------------------------------------------------------
CREATE FOLDER IF NOT EXISTS HealthcareEMSDB;
CREATE FOLDER IF NOT EXISTS HealthcareEMSDB.Bronze;
CREATE FOLDER IF NOT EXISTS HealthcareEMSDB.Silver;
CREATE FOLDER IF NOT EXISTS HealthcareEMSDB.Gold;
USE HealthcareEMSDB;
-------------------------------------------------------------------------------
-- 1. BRONZE LAYER: Raw Data Ingestion
-------------------------------------------------------------------------------
CREATE OR REPLACE TABLE HealthcareEMSDB.Bronze.CAD_Logs (
IncidentID STRING,
ZipCode STRING,
CallReceivedTime TIMESTAMP,
UnitDispatchedTime TIMESTAMP,
UnitEnRouteTime TIMESTAMP,
UnitOnSceneTime TIMESTAMP,
Priority STRING -- ALPHA, BRAVO, CHARLIE, DELTA, ECHO (Highest)
);
INSERT INTO HealthcareEMSDB.Bronze.CAD_Logs VALUES
('INC001', '10001', TIMESTAMP '2025-01-18 08:00:00', TIMESTAMP '2025-01-18 08:01:00', TIMESTAMP '2025-01-18 08:02:00', TIMESTAMP '2025-01-18 08:08:00', 'ECHO'), -- 6m travel
('INC002', '10002', TIMESTAMP '2025-01-18 08:15:00', TIMESTAMP '2025-01-18 08:16:30', TIMESTAMP '2025-01-18 08:18:00', TIMESTAMP '2025-01-18 08:28:00', 'DELTA'), -- 10m travel
('INC003', '10001', TIMESTAMP '2025-01-18 08:30:00', TIMESTAMP '2025-01-18 08:31:00', TIMESTAMP '2025-01-18 08:32:00', TIMESTAMP '2025-01-18 08:39:00', 'CHARLIE'),
('INC004', '10003', TIMESTAMP '2025-01-18 09:00:00', TIMESTAMP '2025-01-18 09:02:00', TIMESTAMP '2025-01-18 09:04:00', TIMESTAMP '2025-01-18 09:20:00', 'ALPHA'), -- 16m travel
('INC005', '10001', TIMESTAMP '2025-01-18 09:15:00', TIMESTAMP '2025-01-18 09:16:00', TIMESTAMP '2025-01-18 09:17:00', TIMESTAMP '2025-01-18 09:22:00', 'ECHO'),
('INC006', '10002', TIMESTAMP '2025-01-18 10:00:00', TIMESTAMP '2025-01-18 10:01:30', TIMESTAMP '2025-01-18 10:03:00', TIMESTAMP '2025-01-18 10:15:00', 'DELTA'),
('INC007', '10003', TIMESTAMP '2025-01-18 10:30:00', TIMESTAMP '2025-01-18 10:32:00', TIMESTAMP '2025-01-18 10:34:00', TIMESTAMP '2025-01-18 10:45:00', 'BRAVO'),
('INC008', '10001', TIMESTAMP '2025-01-18 11:00:00', TIMESTAMP '2025-01-18 11:01:00', TIMESTAMP '2025-01-18 11:02:00', TIMESTAMP '2025-01-18 11:10:00', 'ECHO'),
('INC009', '10002', TIMESTAMP '2025-01-18 11:45:00', TIMESTAMP '2025-01-18 11:46:00', TIMESTAMP '2025-01-18 11:48:00', TIMESTAMP '2025-01-18 12:00:00', 'DELTA'),
('INC010', '10003', TIMESTAMP '2025-01-18 12:15:00', TIMESTAMP '2025-01-18 12:17:00', TIMESTAMP '2025-01-18 12:19:00', TIMESTAMP '2025-01-18 12:35:00', 'ALPHA'),
('INC011', '10001', TIMESTAMP '2025-01-18 13:00:00', TIMESTAMP '2025-01-18 13:01:00', TIMESTAMP '2025-01-18 13:02:00', TIMESTAMP '2025-01-18 13:09:00', 'ECHO'),
('INC012', '10002', TIMESTAMP '2025-01-18 13:30:00', TIMESTAMP '2025-01-18 13:31:30', TIMESTAMP '2025-01-18 13:33:00', TIMESTAMP '2025-01-18 13:45:00', 'CHARLIE'),
('INC013', '10003', TIMESTAMP '2025-01-18 14:00:00', TIMESTAMP '2025-01-18 14:02:00', TIMESTAMP '2025-01-18 14:04:00', TIMESTAMP '2025-01-18 14:20:00', 'BRAVO'),
('INC014', '10001', TIMESTAMP '2025-01-18 14:30:00', TIMESTAMP '2025-01-18 14:31:00', TIMESTAMP '2025-01-18 14:32:00', TIMESTAMP '2025-01-18 14:38:00', 'ECHO'),
('INC015', '10002', TIMESTAMP '2025-01-18 15:00:00', TIMESTAMP '2025-01-18 15:01:30', TIMESTAMP '2025-01-18 15:03:00', TIMESTAMP '2025-01-18 15:15:00', 'DELTA'),
('INC016', '10003', TIMESTAMP '2025-01-18 15:30:00', TIMESTAMP '2025-01-18 15:32:00', TIMESTAMP '2025-01-18 15:34:00', TIMESTAMP '2025-01-18 15:50:00', 'ALPHA'),
('INC017', '10001', TIMESTAMP '2025-01-18 16:00:00', TIMESTAMP '2025-01-18 16:01:00', TIMESTAMP '2025-01-18 16:02:00', TIMESTAMP '2025-01-18 16:10:00', 'ECHO'),
('INC018', '10002', TIMESTAMP '2025-01-18 16:45:00', TIMESTAMP '2025-01-18 16:46:00', TIMESTAMP '2025-01-18 16:48:00', TIMESTAMP '2025-01-18 17:00:00', 'CHARLIE'),
('INC019', '10003', TIMESTAMP '2025-01-18 17:15:00', TIMESTAMP '2025-01-18 17:17:00', TIMESTAMP '2025-01-18 17:19:00', TIMESTAMP '2025-01-18 17:35:00', 'BRAVO'),
('INC020', '10001', TIMESTAMP '2025-01-18 18:00:00', TIMESTAMP '2025-01-18 18:01:00', TIMESTAMP '2025-01-18 18:02:00', TIMESTAMP '2025-01-18 18:09:00', 'ECHO'),
('INC021', '10002', TIMESTAMP '2025-01-18 18:30:00', TIMESTAMP '2025-01-18 18:31:30', TIMESTAMP '2025-01-18 18:33:00', TIMESTAMP '2025-01-18 18:45:00', 'DELTA'),
('INC022', '10003', TIMESTAMP '2025-01-18 19:00:00', TIMESTAMP '2025-01-18 19:02:00', TIMESTAMP '2025-01-18 19:04:00', TIMESTAMP '2025-01-18 19:20:00', 'ALPHA'),
('INC023', '10001', TIMESTAMP '2025-01-18 19:30:00', TIMESTAMP '2025-01-18 19:31:00', TIMESTAMP '2025-01-18 19:32:00', TIMESTAMP '2025-01-18 19:38:00', 'ECHO'),
('INC024', '10002', TIMESTAMP '2025-01-18 20:00:00', TIMESTAMP '2025-01-18 20:01:30', TIMESTAMP '2025-01-18 20:03:00', TIMESTAMP '2025-01-18 20:15:00', 'CHARLIE'),
('INC025', '10003', TIMESTAMP '2025-01-18 20:30:00', TIMESTAMP '2025-01-18 20:32:00', TIMESTAMP '2025-01-18 20:34:00', TIMESTAMP '2025-01-18 20:50:00', 'BRAVO'),
('INC026', '10001', TIMESTAMP '2025-01-18 21:00:00', TIMESTAMP '2025-01-18 21:01:00', TIMESTAMP '2025-01-18 21:02:00', TIMESTAMP '2025-01-18 21:08:00', 'ECHO'),
('INC027', '10002', TIMESTAMP '2025-01-18 21:30:00', TIMESTAMP '2025-01-18 21:31:30', TIMESTAMP '2025-01-18 21:33:00', TIMESTAMP '2025-01-18 21:45:00', 'DELTA'),
('INC028', '10003', TIMESTAMP '2025-01-18 22:00:00', TIMESTAMP '2025-01-18 22:02:00', TIMESTAMP '2025-01-18 22:04:00', TIMESTAMP '2025-01-18 22:20:00', 'ALPHA'),
('INC029', '10001', TIMESTAMP '2025-01-18 22:30:00', TIMESTAMP '2025-01-18 22:31:00', TIMESTAMP '2025-01-18 22:32:00', TIMESTAMP '2025-01-18 22:38:00', 'ECHO'),
('INC030', '10002', TIMESTAMP '2025-01-18 23:00:00', TIMESTAMP '2025-01-18 23:01:30', TIMESTAMP '2025-01-18 23:03:00', TIMESTAMP '2025-01-18 23:15:00', 'CHARLIE'),
('INC031', '10003', TIMESTAMP '2025-01-19 00:00:00', TIMESTAMP '2025-01-19 00:02:00', TIMESTAMP '2025-01-19 00:04:00', TIMESTAMP '2025-01-19 00:20:00', 'BRAVO'),
('INC032', '10001', TIMESTAMP '2025-01-19 01:00:00', TIMESTAMP '2025-01-19 01:01:00', TIMESTAMP '2025-01-19 01:02:00', TIMESTAMP '2025-01-19 01:09:00', 'ECHO'),
('INC033', '10002', TIMESTAMP '2025-01-19 02:00:00', TIMESTAMP '2025-01-19 02:01:30', TIMESTAMP '2025-01-19 02:03:00', TIMESTAMP '2025-01-19 02:15:00', 'DELTA'),
('INC034', '10003', TIMESTAMP '2025-01-19 03:00:00', TIMESTAMP '2025-01-19 03:02:00', TIMESTAMP '2025-01-19 03:04:00', TIMESTAMP '2025-01-19 03:20:00', 'ALPHA'),
('INC035', '10001', TIMESTAMP '2025-01-19 04:00:00', TIMESTAMP '2025-01-19 04:01:00', TIMESTAMP '2025-01-19 04:02:00', TIMESTAMP '2025-01-19 04:08:00', 'ECHO'),
('INC036', '10002', TIMESTAMP '2025-01-19 05:00:00', TIMESTAMP '2025-01-19 05:01:30', TIMESTAMP '2025-01-19 05:03:00', TIMESTAMP '2025-01-19 05:15:00', 'CHARLIE'),
('INC037', '10003', TIMESTAMP '2025-01-19 06:00:00', TIMESTAMP '2025-01-19 06:02:00', TIMESTAMP '2025-01-19 06:04:00', TIMESTAMP '2025-01-19 06:20:00', 'BRAVO'),
('INC038', '10001', TIMESTAMP '2025-01-19 07:00:00', TIMESTAMP '2025-01-19 07:01:00', TIMESTAMP '2025-01-19 07:02:00', TIMESTAMP '2025-01-19 07:09:00', 'ECHO'),
('INC039', '10002', TIMESTAMP '2025-01-19 08:00:00', TIMESTAMP '2025-01-19 08:01:30', TIMESTAMP '2025-01-19 08:03:00', TIMESTAMP '2025-01-19 08:15:00', 'DELTA'),
('INC040', '10003', TIMESTAMP '2025-01-19 09:00:00', TIMESTAMP '2025-01-19 09:02:00', TIMESTAMP '2025-01-19 09:04:00', TIMESTAMP '2025-01-19 09:20:00', 'ALPHA'),
('INC041', '10001', TIMESTAMP '2025-01-19 10:00:00', TIMESTAMP '2025-01-19 10:01:00', TIMESTAMP '2025-01-19 10:02:00', TIMESTAMP '2025-01-19 10:08:00', 'ECHO'),
('INC042', '10002', TIMESTAMP '2025-01-19 11:00:00', TIMESTAMP '2025-01-19 11:01:30', TIMESTAMP '2025-01-19 11:03:00', TIMESTAMP '2025-01-19 11:15:00', 'CHARLIE'),
('INC043', '10003', TIMESTAMP '2025-01-19 12:00:00', TIMESTAMP '2025-01-19 12:02:00', TIMESTAMP '2025-01-19 12:04:00', TIMESTAMP '2025-01-19 12:20:00', 'BRAVO'),
('INC044', '10001', TIMESTAMP '2025-01-19 13:00:00', TIMESTAMP '2025-01-19 13:01:00', TIMESTAMP '2025-01-19 13:02:00', TIMESTAMP '2025-01-19 13:09:00', 'ECHO'),
('INC045', '10002', TIMESTAMP '2025-01-19 14:00:00', TIMESTAMP '2025-01-19 14:01:30', TIMESTAMP '2025-01-19 14:03:00', TIMESTAMP '2025-01-19 14:15:00', 'DELTA'),
('INC046', '10003', TIMESTAMP '2025-01-19 15:00:00', TIMESTAMP '2025-01-19 15:02:00', TIMESTAMP '2025-01-19 15:04:00', TIMESTAMP '2025-01-19 15:20:00', 'ALPHA'),
('INC047', '10001', TIMESTAMP '2025-01-19 16:00:00', TIMESTAMP '2025-01-19 16:01:00', TIMESTAMP '2025-01-19 16:02:00', TIMESTAMP '2025-01-19 16:08:00', 'ECHO'),
('INC048', '10002', TIMESTAMP '2025-01-19 17:00:00', TIMESTAMP '2025-01-19 17:01:30', TIMESTAMP '2025-01-19 17:03:00', TIMESTAMP '2025-01-19 17:15:00', 'CHARLIE'),
('INC049', '10003', TIMESTAMP '2025-01-19 18:00:00', TIMESTAMP '2025-01-19 18:02:00', TIMESTAMP '2025-01-19 18:04:00', TIMESTAMP '2025-01-19 18:20:00', 'BRAVO'),
('INC050', '10001', TIMESTAMP '2025-01-19 19:00:00', TIMESTAMP '2025-01-19 19:01:00', TIMESTAMP '2025-01-19 19:02:00', TIMESTAMP '2025-01-19 19:09:00', 'ECHO');
-------------------------------------------------------------------------------
-- 2. SILVER LAYER: Response Metrics
-------------------------------------------------------------------------------
CREATE OR REPLACE VIEW HealthcareEMSDB.Silver.ResponseMetrics AS
SELECT
IncidentID,
ZipCode,
Priority,
TIMESTAMPDIFF(SECOND, UnitDispatchedTime, UnitEnRouteTime) AS ChuteTimeSec, -- Goal < 60s
TIMESTAMPDIFF(MINUTE, UnitEnRouteTime, UnitOnSceneTime) AS TravelTimeMin,
TIMESTAMPDIFF(MINUTE, CallReceivedTime, UnitOnSceneTime) AS TotalResponseTimeMin
FROM HealthcareEMSDB.Bronze.CAD_Logs;
-------------------------------------------------------------------------------
-- 3. GOLD LAYER: Coverage Heatmap
-------------------------------------------------------------------------------
CREATE OR REPLACE VIEW HealthcareEMSDB.Gold.CoverageHeatmap AS
SELECT
ZipCode,
COUNT(IncidentID) AS TotalCalls,
AVG(TotalResponseTimeMin) AS AvgResponseTime,
MAX(TotalResponseTimeMin) AS MaxResponseTime,
SUM(CASE WHEN TotalResponseTimeMin > 9 THEN 1 ELSE 0 END) AS CallsOver9Min, -- Standard SLA
(SUM(CASE WHEN TotalResponseTimeMin > 9 THEN 1 ELSE 0 END) / CAST(COUNT(IncidentID) AS DOUBLE)) * 100 AS PercentSLA_Breach
FROM HealthcareEMSDB.Silver.ResponseMetrics
WHERE Priority IN ('DELTA', 'ECHO') -- Focus on life-threatening
GROUP BY ZipCode;
-------------------------------------------------------------------------------
-- 4. DREMIO AGENT PROMPTS
-------------------------------------------------------------------------------
/*
Run the following prompts in Dremio's AI Chat to visualize these insights:
1. "Show me the Zip Codes with the highest percentage of SLA breaches."
2. "What is the average Chute Time?"
3. "Map the call volume by Zip Code."
4. "List all ECHO priority calls that took longer than 9 minutes."
*/