Query 1 Example

CREATE TABLE tickets ( 

	ticket_id INT AUTO_INCREMENT PRIMARY KEY,

	project_name VARCHAR(255),

	member_id INT,

	subject VARCHAR(255) NOT NULL,

	description TEXT,

	intent VARCHAR(255),

	status ENUM('open', 'closed', 'pending') NOT NULL DEFAULT 'open',

	priority ENUM('low', 'medium', 'high') NOT NULL DEFAULT 'medium',

	assignedto INT,

	updatedby INT,

	channel VARCHAR(255),

	created DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,

	first_response DATETIME,

	update_response DATETIME,

	resolved DATETIME,

	member VARCHAR(255),

	sv_month DECIMAL(10,2),

	sv_quarter DECIMAL(10,2),

	sv_life DECIMAL(25,2), 

	tags longtext, 

	csat DECIMAL(3,2),

	sentiment VARCHAR(255),  

	INDEX (member_id),

	INDEX (assignedto),

	INDEX (updatedby)

	);

Query 2 Example.

SELECT
    t.ticket_id,
    g.proj_name AS "Game Name",
    g.studio_name,
    g.proj_status AS "Life Cycle",
    t.subject as "Breadcrumb",
    t.description as "Help Request"
FROM
    tickets t
LEFT JOIN
    game_db g ON t.project_name = g.proj_name
WHERE
    t.tags LIKE '%rpg%';
	--t.lable LIKE '%rpg%'; 

Search for results by agent 203

SELECT 
    t.project_name,
    t.date_created AS "Contact Date",
    t.subject AS "Breadcrumb",
    t.player_feedback AS "Sentiments",
    t.status,
    a.agent_login,
    a.agent_team,
    g.studio_name AS "Game Studio",
    t.tags,
    t.lastdate_closed
FROM tickets t
JOIN agent a ON t.assignedto = a.agent_id
JOIN game_db g ON t.project_name = g.proj_name
WHERE a.agent_id = 203
  AND t.player_feedback IS NOT NULL
  AND t.player_feedback <> ''
  -- AND t.date_created >= NOW() - INTERVAL 30 DAY
  AND t.date_created BETWEEN '2024-12-01' AND '2024-12-13'
ORDER BY t.date_created DESC;

This time I'm searching by an Agent Team instead of a specific Agent

SELECT 
    t.project_name,
    t.date_created AS "Contact Date",
    t.subject AS "Breadcrumb",
    t.player_feedback AS "Sentiments",
    t.status,
    a.agent_id,
    a.agent_team,
    g.studio_name AS "Game Studio",
    t.tags,
    t.lastdate_closed
FROM tickets t
JOIN agent a ON t.assignedto = a.agent_id
JOIN game_db g ON t.project_name = g.proj_name
-- WHERE a.agent_id = 203
  WHERE a.agent_team = 'ClientCare Hub'
  AND t.player_feedback IS NOT NULL
  AND t.player_feedback <> ''
  -- AND t.date_created >= NOW() - INTERVAL 30 DAY
  AND t.date_created BETWEEN '2024-12-01' AND '2024-12-13'
ORDER BY t.date_created DESC;