SDR COUNTS BY COUNTRY AND YEAR

SEE PBI-137119

https://dev.azure.com/transport-canada/DSD-CIVAV%20Support/_workitems/edit/137119

WSDRS - Report - SDR count

The latest number of database SDRs broken out by countries and the last 5 years of Canadian SDR submitted

Run the following code in SQL Developer, TOAD, or DBEAVER, then export results to XLS

SELECT TO_CHAR(X.YEAR_CALENDAR_NBR) AS YR,
(SELECT COUNT(*) FROM M72_SDR WHERE
TO_NUMBER(SUBSTR(SDR_NUMBER_NUM,1,4)) = X.YEAR_CALENDAR_NBR) AS TOT,
(SELECT COUNT() FROM M72_SDR WHERE COUNTRY_CD = 'CF'
AND TO_NUMBER(SUBSTR(SDR_NUMBER_NUM,1,4)) = X.YEAR_CALENDAR_NBR) AS CF,
(SELECT COUNT() FROM M72_SDR WHERE COUNTRY_CD = 'AU'
AND TO_NUMBER(SUBSTR(SDR_NUMBER_NUM,1,4)) = X.YEAR_CALENDAR_NBR) AS AU,
(SELECT COUNT() FROM M72_SDR WHERE COUNTRY_CD = 'US'
AND TO_NUMBER(SUBSTR(SDR_NUMBER_NUM,1,4)) = X.YEAR_CALENDAR_NBR) AS US
FROM TR26_REPORTING_YEAR X
WHERE X.YEAR_CALENDAR_NBR BETWEEN 2016 AND 2021 ; /* 5-yr specified time period */