Advanced SQL Help - Line Chart

Advanced SQL Help - Line Chart

  
I've got an advanced query where the results are being plotted out to a line chart, but it is only plotting it out if the count is >0 and we would like to plot out zeros as well. There are two different date ranges being passed into it - one is used if the applicant has submitted thier applcation, the other is if they are still in process. The date ranges may not be the same and they could be looking to query on only submitted, only in process, or both.  Assuming I need to use a CTE, I'm having a hard time figuring out how to write it.  Here is the current query:

SELECT Count(*), {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 {Country}.[Description], to_char({Applicant}.[StartDate], 'YYYY-MM-"01"')
ORDER BY  to_char({Applicant}.[StartDate], 'YYYY-MM-"01"') asc

Any help is appreciated. 
Cory
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