Generating Alert Volume Reports by Communication Type

Purpose: To provide the team with standardized SQL queries for fulfilling client requests regarding alert counts, broken down by communication type (Email, SMS, Voice) and month.

---

1. Overview

When a client requests the total count of alerts sent for specific campaigns, we must query both the Main and Archive databases to ensure a complete data set for the requested time frame.

Campaign Reference (Examples used in queries below):

Campaign ID	Alert Name
1004	Usage - Electric Cost
1005	Usage - Electric Usage
1006	Usage - Water Cost
1007	Usage - Water Usage

[!IMPORTANT]

Note for Analysts: You must modify the campaignid_int list and the schedule_dt range in the queries below to match the specific request requirements.

---

2. Main Database Queries

Host IP: 10.10.4.10

Use these queries for recent data currently held in the active queues.

 

/* EMAIL QUEUE - MAIN */
SELECT campaignid_int, COUNT(*) AS total_count, MONTHNAME(schedule_dt) AS MonthName
FROM jea.emailqueue 
WHERE campaignid_int IN (1004, 1005, 1006, 1007) 
  AND schedule_dt >= '2025-01-01 00:00:00' 
  AND schedule_dt <= '2025-12-31 23:59:59'
GROUP BY campaignid_int, MONTH(schedule_dt);

/* SMS QUEUE - MAIN */
SELECT campaignid_int, COUNT(*) AS total_count, MONTHNAME(schedule_dt) AS MonthName
FROM jea.smsqueue 
WHERE campaignid_int IN (1004, 1005, 1006, 1007) 
  AND schedule_dt >= '2025-01-01 00:00:00' 
  AND schedule_dt <= '2025-12-31 23:59:59'
GROUP BY campaignid_int, MONTH(schedule_dt);

/* VOICE QUEUE - MAIN */
SELECT campaignid_int, COUNT(*) AS total_count, MONTHNAME(schedule_dt) AS MonthName
FROM jea.voicequeue 
WHERE campaignid_int IN (1004, 1005, 1006, 1007) 
  AND schedule_dt >= '2025-01-01 00:00:00' 
  AND schedule_dt <= '2025-12-31 23:59:59'
GROUP BY campaignid_int, MONTH(schedule_dt);

---

3. Archive Database Queries

Host IP: 10.10.4.8

Use these queries for historical data that has been moved to the archive tables.

 

/* EMAIL QUEUE - ARCHIVE */
SELECT campaignid_int, COUNT(*) AS total_count, MONTHNAME(schedule_dt) AS MonthName
FROM jea.emailqueue_archive 
WHERE campaignid_int IN (1004, 1005, 1006, 1007) 
  AND schedule_dt >= '2025-01-01 00:00:00' 
  AND schedule_dt <= '2025-12-31 23:59:59'
GROUP BY campaignid_int, MONTH(schedule_dt);

/* SMS QUEUE - ARCHIVE */
SELECT campaignid_int, COUNT(*) AS total_count, MONTHNAME(schedule_dt) AS MonthName
FROM jea.smsqueue_archive 
WHERE campaignid_int IN (1004, 1005, 1006, 1007) 
  AND schedule_dt >= '2025-01-01 00:00:00' 
  AND schedule_dt <= '2025-12-31 23:59:59'
GROUP BY campaignid_int, MONTH(schedule_dt);

/* VOICE QUEUE - ARCHIVE */
SELECT campaignid_int, COUNT(*) AS total_count, MONTHNAME(schedule_dt) AS MonthName
FROM jea.voicequeue_archive 
WHERE campaignid_int IN (1004, 1005, 1006, 1007) 
  AND schedule_dt >= '2025-01-01 00:00:00' 
  AND schedule_dt <= '2025-12-31 23:59:59'
GROUP BY campaignid_int, MONTH(schedule_dt);

---

4. Data Consolidation

Once the results are fetched from both DBs:

1. Sum the total_count for each Campaign ID and Month from both the Main and Archive results.

2. Organize the final report in an Excel sheet with columns for Campaign, Type, Month, and Total Sent.