SQL_OPS.sql

CREATE DATABASE OPS;

USE OPS;

--- DATA SANITY CHECK

SELECT COUNT(*) AS TOTAL_ROWS FROM client_ops_data;

SELECT TOP 5 * FROM client_ops_data;

--- DATE AND NUMBER VALIDATION

SELECT MIN(CASE_DATE) AS MIN_DATE,
MAX(CASE_DATE) AS MAX_DATE
FROM client_ops_data;

SELECT MIN(TURNAROUND_HRS) AS MIN_HRS,
MAX(TURNAROUND_HRS) AS MAX_HRS
FROM client_ops_data;

--- DAILY MIS SUMMARY

SELECT CASE_DATE,
COUNT(*) AS TOTAL_CASES,
SUM(CASE WHEN STATUS = 'SUCCESS' THEN 1 ELSE 0 END) AS SUCCESS_CASES,
SUM(CASE WHEN STATUS = 'FAILED' THEN 1 ELSE 0 END) AS FAILED_CASES,
SUM(CASE WHEN STATUS = 'PENDING' THEN 1 ELSE 0 END) AS PENDING_CASES,
CAST(
	SUM(CASE WHEN SLA_FLAG = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2)
	) AS SLA_MET_PCT
FROM client_ops_data
GROUP BY case_date
ORDER BY case_date;

--- TEAM-WISE PERFORMANCE

SELECT TEAM,
COUNT(*) AS TOTAL_CASES,
SUM(CASE WHEN SLA_FLAG = 0 THEN 1 ELSE 0 END) AS SLA_BREACH_CASES
FROM client_ops_data
GROUP BY team;

SELECT TEAM,
COUNT(*) AS TOTAL_CASES,
SUM(CASE WHEN SLA_FLAG = 1 THEN 1 ELSE 0 END) AS SLA_SUCCESS_CASES
FROM client_ops_data
GROUP BY team;

--- PRIORITY IMPACT ANALYSIS

SELECT PRIORITY,
COUNT(*) AS TOTAL_CASES,
SUM(CASE WHEN SLA_FLAG = 0 THEN 1 ELSE 0 END) AS BREACH_CASES
FROM client_ops_data
GROUP BY priority;

--- VIEWS

CREATE VIEW VW_MIS_SUMMARY
AS
SELECT
	CASE_DATE,
	COUNT(*) AS TOTAL_CASES,
	SUM(CASE WHEN STATUS = 'SUCCESS' THEN 1 ELSE 0 END) AS SUCCESS_CASES,
	SUM(CASE WHEN STATUS = 'PENDING' THEN 1 ELSE 0 END) AS PENDING_CASES,
	CAST(
		SUM(CASE WHEN SLA_FLAG = 1 THEN 1 ELSE 0 END) * 100.0 / COUNT(*) AS DECIMAL(5,2))
		AS SLA_MET_PCT
FROM client_ops_data
GROUP BY case_date;

SELECT * FROM VW_MIS_SUMMARY;

SELECT * FROM VW_MIS_SUMMARY
WHERE case_date='2024-06-20';


--- AUTOMATION

SELECT * FROM client_ops_data
WHERE case_date = '2024-06-20';

UPDATE client_ops_data SET status = 'PENDING', sla_flag = 0
WHERE case_date = '2024-06-20'
	AND status = 'SUCCESS';

SELECT
    case_date,
    COUNT(*) AS total_cases,
    SUM(CASE WHEN status='PENDING' THEN 1 ELSE 0 END) AS pending_cases,
    CAST(
      SUM(CASE WHEN sla_flag=1 THEN 1 ELSE 0 END)*100.0/COUNT(*) AS DECIMAL(5,2)
    ) AS sla_pct
FROM client_ops_data
WHERE case_date = '2024-06-20'
GROUP BY case_date;

--- REVERT TEST CHANGE

UPDATE client_ops_data SET status = 'SUCCESS', sla_flag = 1
WHERE case_date = '2024-06-20';