C # - Check if dates are missing in the selected date range

1

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 that had batch finalized, if there is no batch finalized on that day should bring the day only with the total amount zeroed. The problem is he is not bringing the date.

How to handle this in C # or SQL ?

My class

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

            }
            return new List<TaxaProducaoOV>();
        }
    }
    
asked by anonymous 08.12.2017 / 18:19

1 answer

2

In fact, you have to use LEFT JOIN , that you get the results there independently if you have relation to tb_routes and COUNT() will return 0.

INNER JOIN you say you want to get everything from tb_batches to tb_routes

    
08.12.2017 / 18:29