C# - Check if dates are missing in the selected period

Question

I have the following SQL

SELECT 
    dt_finished::date AS "DataFinalizacao",
    count(DISTINCT tba.id_batches) AS "Quantidade Total"
FROM tb_batches AS tba
INNER JOIN tb_routes tro ON (tro.fk_id_products = tba.fk_id_products 
    AND tro.fk_id_sessions = 70 
    AND tba.finished = TRUE)
WHERE tba.dt_finished::date <= now()::date 
    AND tba.dt_finished::date >= (now() - INTERVAL '15 day')::date
GROUP BY tba.dt_finished::date
ORDER BY tba.dt_finished::date DESC

I need to return every day they’ve had batch completed, if there is no batch finished that day should bring the day only with the total amount zeroed. The problem is he’s not bringing the date.

How to treat this in C# or in SQL?

My class

public class TaxaProducaoBLL : ITaxaProducaoBLL
    {
        public IEnumerable<TaxaProducaoOV> CalcularTaxaProducao(IEnumerable<TaxaProducaoOV> taxaProducao)
        {
            foreach (var taxa in taxaProducao)
            {

            }
            return new List<TaxaProducaoOV>();
        }
    }

Answer 1

In reality you have to use LEFT JOIN, that there you bring the results independent if it has relationship with the tb_routes and the COUNT() will return 0.

The INNER JOIN you say you want to take everything from tb_batches who is in the tb_routes