Hi Cory,

If your query already returns what you need (except for the 0) its because you're doing the count on top of existing records.

if you need to get those counts per country even when theres nothing in a specific interval you need to transform that in a way that makes the db return all countries and then left join with what you have.

Something like:

SELECT {Country}.[Description], B.CTCOUNT FROM {Country} LEFT JOIN (your query) B ON ({Country}.[Description] = B.DESCRIPTION)

Remember in your query to give alias to the country description and to the count. And you'll probably get some NULLS instead of 0 but you can overcome that for instance with a CASE condirion.

Hope this helps
Guilherme

Thank you  - I ended up getting it to work with the following:

WITH CTE_Dates (cte_date, country_name)  AS (
    SELECT cast(TRUNC(@OldestFromDate , 'MONTH') as date), {Country}.[Description]
        FROM dual, {Country}
        WHERE {Country}.[Id] in (@CountryId) 
    UNION ALL
    SELECT cast(ADD_MONTHS(TRUNC(cte_date, 'MONTH'), 1) as date), country_name
        FROM CTE_Dates
        WHERE ADD_MONTHS(TRUNC(cte_date, 'MONTH'), 1) <= @NewestToDate 
),
    country_counts (num, description, moyear) as (
    SELECT Count(*) num, {Country}.[Description], to_char({Applicant}.[StartDate], 'YYYY-MM-"01"')
        FROM {Applicant}
        JOIN {Country} on {Country}.[Id] = {Applicant}.[CountryId]
        JOIN {ApplicantPosition} on {ApplicantPosition}.[ApplicantId]={Applicant}.[Id]
        JOIN {Position} on {Position}.[Id] = {ApplicantPosition}.[PositionId]
        WHERE
        (
        (@ApplicationSubmitted = 1 and ({Applicant}.[StartDate] BETWEEN @FromDate AND @ToDate and {Applicant}.[SubmitDate] <> to_date('01-01-1900','DD-MM-YYYY')) 
        )

        or 

        (@ApplicationInProcess = 1 and ({Applicant}.[StartDate] BETWEEN @InProcessFromDate  AND @InProcessToDate and {Applicant}.[SubmitDate] = to_date('01-01-1900','DD-MM-YYYY'))
        ))
   
        and
  
        {Applicant}.[CountryId] IN (@CountryId) 
   
        and 
   
        {Position}.[Id] IN (@ProfessionId)

        group by to_char({Applicant}.[StartDate], 'YYYY-MM-"01"'),{Country}.[Description]
        ORDER BY  to_char({Applicant}.[StartDate], 'YYYY-MM-"01"') asc)

SELECT to_char(cte_date, 'YYYY-MM-DD'), country_name, COALESCE(country_counts.num, 0)
FROM CTE_Dates
left join   country_counts on moyear=to_char(cte_date, 'YYYY-MM-DD') and
            description = country_name
WHERE       (cte_date BETWEEN  @FromDate  AND @ToDate
             OR cte_date BETWEEN  @InProcessFromDate  AND @InProcessToDate ) 
ORDER BY CTE_Date, country_name asc