secondbrain/QUICK_REFERENCE.md

# CDM Customer Service - Quick Reference Guide

## 🎯 Your Complete Database is Ready!

**Server:** localhost:1433  
**Database:** CDM_CustomerService  
**Auth:** SA / Welcome1  
**Tables:** 26  
**Views:** 4  
**Procedures:** 19  

---

## 📊 Viewing Dashboards

### Executive Dashboard (All Key Metrics)
```sql
SELECT 
    activeCases, 
    closedCases, 
    totalCustomers, 
    activeContracts,
    activeContractValue
FROM cdm.v_ExecutiveDashboard;
```

### Case Health Dashboard
```sql
SELECT caseNumber, title, priority, status, statusHealth, daysOpen
FROM cdm.v_CasePerformanceMetrics
ORDER BY daysOpen DESC;
```

### Agent Performance
```sql
SELECT agentName, totalCasesOwned, closedCases, avgHoursToResolution
FROM cdm.v_AgentPerformance
ORDER BY closedCases DESC;
```

### Contract Status
```sql
SELECT contractNumber, accountName, utilizationPercent, daysRemaining, utilizationStatus
FROM cdm.v_ContractUtilizationDashboard
WHERE utilizationStatus != 'NORMAL';
```

---

## 🔍 Running Reports

### Case Analysis
```sql
-- Cases by priority (last 90 days)
EXEC sp_CaseVolumeByPriority @Days = 90;

-- Customer satisfaction (last 6 months)
EXEC sp_CustomerSatisfactionAnalysis @Months = 6;

-- SLA Compliance (real-time)
EXEC sp_SLAComplianceReport;
```

### Schedule & Workload
```sql
-- This week's activities
EXEC sp_ServiceActivitySchedule @Days = 7;

-- Resource utilization (last 30 days)
EXEC sp_ResourceUtilizationReport @Days = 30;

-- Agent workload
SELECT agentName, activeCases, criticalCases, highPriorityCases
FROM cdm.v_AgentPerformance;
```

### Business Metrics
```sql
-- Monthly summary
EXEC sp_MonthlyActivityReport @Year = 2026, @Month = 9;

-- Customer revenue
EXEC sp_CustomerRevenueAnalysis;

-- KB article usage
EXEC sp_KnowledgeArticleEffectiveness;
```

---

## 🛠️ Common Tasks

### Find Cases by Status
```sql
-- Active cases
SELECT caseNumber, title, priority FROM cdm.[Case]
WHERE status = 'Active' AND isActive = 1
ORDER BY priority DESC, createdDate;

-- Overdue cases (> 30 days)
EXEC sp_UpdateCaseOverdueStatus @ThresholdDays = 30;

-- Cases needing attention
SELECT * FROM cdm.v_CasePerformanceMetrics
WHERE statusHealth IN ('Overdue', 'Awaiting Close')
ORDER BY daysOpen DESC;
```

### Find Contracts
```sql
-- Active contracts
SELECT contractNumber, contractName, accountName, status
FROM cdm.v_ContractSummary
WHERE status = 'Active'
ORDER BY contractValue DESC;

-- Expiring soon
EXEC sp_ContractRenewalAlerts @DaysUntilExpiry = 90;

-- Overutilized contracts
SELECT contractNumber, accountName, utilizationPercent
FROM cdm.v_ContractUtilizationDashboard
WHERE utilizationPercent > 80;
```

### Find Data Quality Issues
```sql
-- Missing data
EXEC sp_CasesWithMissingData;

-- Orphaned records
EXEC sp_FindOrphanedRecords;

-- Incomplete cases
SELECT caseNumber, title
FROM cdm.[Case]
WHERE accountId IS NULL OR ownerUserId IS NULL;
```

---

## 📅 Maintenance Commands

### Daily
```sql
-- Check SLA status
EXEC sp_SLAComplianceReport;

-- Today's schedule
EXEC sp_ServiceActivitySchedule @Days = 1;
```

### Weekly
```sql
-- Overdue cases
EXEC sp_UpdateCaseOverdueStatus @ThresholdDays = 30;

-- Team performance
SELECT * FROM cdm.v_AgentPerformance
ORDER BY activeCases DESC;

-- Data quality
EXEC sp_CasesWithMissingData;
```

### Monthly
```sql
-- Month summary
EXEC sp_MonthlyActivityReport @Year = 2026, @Month = 9;

-- Customer satisfaction
EXEC sp_CustomerSatisfactionAnalysis @Months = 1;

-- Revenue analysis
EXEC sp_CustomerRevenueAnalysis;

-- Auto-close old resolved cases
EXEC sp_CloseResolvedCases @DaysOld = 7;
```

### Quarterly
```sql
-- Contract renewals
EXEC sp_ContractRenewalAlerts @DaysUntilExpiry = 90;

-- Resource utilization
EXEC sp_ResourceUtilizationReport @Days = 90;

-- KB effectiveness
EXEC sp_KnowledgeArticleEffectiveness;
```

### Annually
```sql
-- Archive old cases (2+ years)
EXEC sp_ArchiveOldCases @YearsOld = 2;

-- Update contract status
EXEC sp_DeactivateExpiredContracts;
EXEC sp_UpdateEntitlementStatus;
```

---

## 🔗 Table Reference

### Create Case
```sql
INSERT INTO cdm.[Case] 
(caseNumber, title, accountId, contactId, priority, status, createdDate)
VALUES ('CSE-2026-0005', 'New Issue Title', @accountId, @contactId, 'High', 'Active', GETUTCDATE());
```

### Create Contract
```sql
INSERT INTO cdm.Contract 
(contractNumber, contractName, accountId, startDate, endDate, status, contractValue)
VALUES ('CNT-2026-001', 'Contract Name', @accountId, '2026-01-01', '2026-12-31', 'Active', 50000.00);
```

### Create Service Activity
```sql
INSERT INTO cdm.ServiceActivity 
(subject, serviceId, caseId, ownerUserId, startTime, endTime, duration, status)
VALUES ('Activity Name', @serviceId, @caseId, @userId, '2026-09-24 10:00:00', '2026-09-24 12:00:00', 120, 'Scheduled');
```

### Update Case
```sql
UPDATE cdm.[Case]
SET status = 'Resolved', resolvedDate = GETUTCDATE()
WHERE caseId = @caseId;
```

---

## 📈 Sample Queries for Analysis

### Cases by Account
```sql
SELECT 
    a.accountName,
    COUNT(c.caseId) AS caseCount,
    SUM(CASE WHEN c.status = 'Active' THEN 1 ELSE 0 END) AS activeCases,
    SUM(CASE WHEN c.status = 'Closed' THEN 1 ELSE 0 END) AS closedCases
FROM cdm.Account a
LEFT JOIN cdm.[Case] c ON a.accountId = c.accountId
WHERE a.isActive = 1
GROUP BY a.accountId, a.accountName
ORDER BY activeCases DESC;
```

### Average Case Duration
```sql
SELECT 
    a.accountName,
    AVG(DATEDIFF(DAY, c.createdDate, c.resolvedDate)) AS avgDaysToResolve,
    COUNT(*) AS totalCasesClosed
FROM cdm.[Case] c
JOIN cdm.Account a ON c.accountId = a.accountId
WHERE c.status = 'Closed' AND c.resolvedDate IS NOT NULL AND c.isActive = 1
GROUP BY a.accountId, a.accountName
ORDER BY avgDaysToResolve DESC;
```

### Technician Workload
```sql
SELECT 
    u.firstName + ' ' + u.lastName AS technician,
    COUNT(c.caseId) AS casesAssigned,
    COUNT(CASE WHEN c.status = 'Active' THEN 1 END) AS activeCases,
    COUNT(CASE WHEN c.status = 'Closed' THEN 1 END) AS closedCases,
    ROUND(AVG(DATEDIFF(HOUR, c.createdDate, ISNULL(c.resolvedDate, GETUTCDATE()))), 2) AS avgHours
FROM cdm.[User] u
LEFT JOIN cdm.[Case] c ON u.userId = c.ownerUserId
WHERE u.isActive = 1 AND c.isActive = 1
GROUP BY u.userId, u.firstName, u.lastName
ORDER BY activeCases DESC;
```

### Contract Value by Account
```sql
SELECT 
    a.accountName,
    COUNT(c.contractId) AS contractCount,
    SUM(c.contractValue) AS totalValue,
    AVG(c.contractValue) AS avgValue,
    SUM(CASE WHEN c.status = 'Active' THEN c.contractValue ELSE 0 END) AS activeValue
FROM cdm.Account a
LEFT JOIN cdm.Contract c ON a.accountId = c.accountId
WHERE a.accountType = 'Customer' AND a.isActive = 1
GROUP BY a.accountId, a.accountName
ORDER BY totalValue DESC;
```

### Service Activity by Technician
```sql
SELECT 
    u.firstName + ' ' + u.lastName AS technician,
    s.serviceName,
    COUNT(sa.activityId) AS activityCount,
    SUM(sa.duration) AS totalMinutes,
    ROUND(SUM(sa.duration) / 60.0, 1) AS totalHours,
    COUNT(CASE WHEN sa.status = 'Completed' THEN 1 END) AS completed
FROM cdm.ServiceActivity sa
JOIN cdm.[User] u ON sa.ownerUserId = u.userId
JOIN cdm.Service s ON sa.serviceId = s.serviceId
WHERE sa.isActive = 1 AND sa.startTime >= DATEADD(MONTH, -1, GETUTCDATE())
GROUP BY u.userId, u.firstName, u.lastName, s.serviceId, s.serviceName
ORDER BY totalMinutes DESC;
```

---

## 🎓 Learning Path

1. **Start:** Review current state with `v_ExecutiveDashboard`
2. **Explore:** Check `v_CasePerformanceMetrics` for details
3. **Analyze:** Run `sp_CaseVolumeByPriority` and `sp_SLAComplianceReport`
4. **Plan:** Use `sp_ServiceActivitySchedule` for scheduling
5. **Monitor:** Run reports from the procedures list regularly

---

## 💡 Pro Tips

### Create a Dashboard in Excel
1. Connect to CDM_CustomerService database
2. Create queries pulling from views
3. Set up auto-refresh
4. Use Excel charts for visualization

### Create Power BI Reports
1. Import data from all 4 views
2. Import result sets from procedures
3. Create visual dashboards
4. Set up data refresh schedule

### Set Up Alerts
```sql
-- Create a job to auto-close resolved cases (weekly)
EXEC sp_CloseResolvedCases @DaysOld = 7;

-- Monitor overdue cases (daily)
EXEC sp_UpdateCaseOverdueStatus @ThresholdDays = 30;

-- Check contract renewals (weekly)
EXEC sp_ContractRenewalAlerts @DaysUntilExpiry = 30;
```

---

## 📞 Quick Links

- **Full Documentation:** CDM_Implementation_Guide.md
- **Reporting Details:** REPORTING_PROCEDURES_SUMMARY.md
- **Database Setup:** CDM_QUICKSTART.md
- **Sample Data:** Already loaded in tables
- **View Definitions:** Use `sp_helptext 'viewName'`
- **Procedure Help:** Use `sp_helptext 'sp_ProcedureName'`

---

## 🚀 Next Steps

1. ✅ **Database Created** - CDM_CustomerService ready
2. ✅ **Tables Created** - 26 tables with sample data
3. ✅ **Views Added** - 4 analytical views
4. ✅ **Procedures Added** - 19 reporting/admin procedures
5. 📋 **Now:** Start running queries and building reports!

---

**Your database is production-ready. Start analyzing your data!**

Last Updated: 2026-09-23