It giving zero because there might be not data matching against the join you are using, try to change the query to the inner join to check if there any results you find, 2ndly there is not need to CTE, desire result can be achieve through a simple query.