Validation › Monthly Crime Trend - Agency C (2024)
Compare results
MatchChecks
| Check | Source (Web Intelligence) | Target (Power BI) | Result |
|---|---|---|---|
| Row count | 12 | 12 | Match |
| Total of incidents | 1,967 | 1,967 | Match |
| Record hash | 7f73b79c3890 | 7f73b79c3890 | Match |
Access and audit
- Data location
- Agency network SC-28
- Run by
- Validation service, read only AC-6
- This view
- Logged AU-2
- Evidence
- Retained with the release AU-11
Source query
SELECT substr(i.occurred_date,1,7) AS month, COUNT(*) AS incidents FROM incident i WHERE i.agency_id = 3 AND i.occurred_date BETWEEN '2024-01-01' AND '2024-12-31' GROUP BY substr(i.occurred_date,1,7)
Power BI dataset query
SELECT CONVERT(char(7), i.occurred_date, 126) AS month, COUNT(*) AS incidents FROM incident i WHERE i.agency_id = @Agency AND i.occurred_date BETWEEN @DateFrom AND @DateTo GROUP BY CONVERT(char(7), i.occurred_date, 126)