Fórum Consulta Agrupando Dia do intervalo da consulta!! #384291

SELECT b.nome,SUM(valor) AS valor,to_char(a.vencto,'dd/mm/yyyy') 
FROM ctreceber a
left join categfinanc b on a.categoria = b.id
WHERE (cast(a.vencto as date) >= '28/08/2010' AND 
cast(a.vencto as date) <= '11/09/2010') AND 
(a.dtrec is null) 
GROUP BY a.vencto,b.nome
order by a.vencto,b.nome

 
 
categfinanc.Sql.Add('Select');
    categfinanc.Sql.Add('A.id,');
    categfinanc.Sql.Add('A.nome,');
    categfinanc.Sql.Add('TabPagoJan.PagoJan ,');
    categfinanc.Sql.Add('TabPagoFev.PagoFev ,');
    categfinanc.Sql.Add('TabPagoMar.PagoMar ,');
    categfinanc.Sql.Add('TabPagoAbr.PagoAbr ,');
    categfinanc.Sql.Add('TabPagoMai.PagoMai ,');
    categfinanc.Sql.Add('TabPagoJun.PagoJun ,');
    categfinanc.Sql.Add('TabPagoJul.Pagojul ,');
    categfinanc.Sql.Add('TabPagoAgo.PagoAgo ,');
    categfinanc.Sql.Add('TabPagoSet.PagoSet ,');
    categfinanc.Sql.Add('TabPagoOut.Pagoout ,');
    categfinanc.Sql.Add('TabPagoNov.Pagonov ,');
    categfinanc.Sql.Add('TabPagoDez.PagoDez ,');
    categfinanc.Sql.Add('TabRecJan.RecJan   ,');
    categfinanc.Sql.Add('TabRecFev.RecFev   ,');
    categfinanc.Sql.Add('TabRecMar.RecMar   ,');
    categfinanc.Sql.Add('TabRecAbr.RecAbr   ,');
    categfinanc.Sql.Add('TabRecMai.RecMai   ,');
    categfinanc.Sql.Add('TabRecJun.Recjun   ,');
    categfinanc.Sql.Add('TabRecJul.Recjul   ,');
    categfinanc.Sql.Add('TabRecAgo.RecAgo   ,');
    categfinanc.Sql.Add('TabRecSet.Recset   ,');
    categfinanc.Sql.Add('TabRecOut.Recout   ,');
    categfinanc.Sql.Add('TabRecNov.Recnov   ,');
    categfinanc.Sql.Add('TabRecDez.RecDez   ');
    categfinanc.Sql.Add('FROM categfinanc a');
 
    // pagos Janeiro
    categfinanc.sql.add('LEFT JOIN (SELECT Round(CAST(SUM(valorpago) AS NUMERIC),2) AS PagoJan  ,categoria FROM contasPagar');
    categfinanc.sql.add('WHERE (CATEGORIA = :pCateg OR :pCateg = 0) ');
    categfinanc.sql.add('AND (COALESCE(valorpago,0) <> 0)');
    categfinanc.sql.add('AND (Extract(YEAR  FROM datapagto) = :pAnoPg) ');
    categfinanc.sql.add('AND (Extract(MONTH FROM datapagto) = :pPgJan) ');
    categfinanc.sql.add('GROUP BY categoria) TabPagoJan ON A.id = TabPagoJan.categoria');
 
    // pagos Fevereiro
    categfinanc.sql.add('LEFT JOIN (SELECT Round(CAST(SUM(valorpago) AS NUMERIC),2)   AS PagoFev ,categoria FROM contasPagar');
    categfinanc.sql.add('WHERE (CATEGORIA = :pCateg OR :pCateg = 0) ');
    categfinanc.sql.add('AND (COALESCE(valorpago,0) <> 0)  ');
    categfinanc.sql.add('AND (Extract(YEAR  from datapagto) = :pAnoPg) ');
    categfinanc.sql.add('AND (Extract(Month from datapagto) = :pPgFev) ');
    categfinanc.sql.add('GROUP BY categoria) TabPagoFev ON A.id = TabPagoFev.categoria');

 // pagos Março
    categfinanc.sql.add('LEFT JOIN (SELECT Round(CAST(SUM(valorpago) AS NUMERIC),2)   AS PagoMar ,categoria FROM contasPagar');
    categfinanc.sql.add('WHERE (CATEGORIA = :pCateg OR :pCateg = 0) ');
    categfinanc.sql.add('AND (COALESCE(valorpago,0) <> 0)  ');
    categfinanc.sql.add('AND (Extract(YEAR  from datapagto) = :pAnoPg) ');
    categfinanc.sql.add('AND (Extract(Month from datapagto) = :pPgMar) ');
    categfinanc.sql.add('GROUP BY categoria) TabPagoMar ON A.id = TabPagoMar.categoria');
 
   ...  SEGUE PARA TODOS OS MESES....