Devart Forums

Code: Select all

 
SELECT
sum( if(bsp is null,0,bsp)-IF(assumido IS NULL,0,assumido) )as devido,
 (SELECT SUM(b.fatbsp) FROM bspfatura b WHERE b.dtbsp = :dtbsp  AND cia=bspfatura.cia AND b.nfaturabsp IS NULL GROUP BY b.cia) as semfatura, /* tkt não cobrado */
 sum(IF(assumido IS NULL,0,assumido)) as assumido,
 sum(if(bsp is null,0,bsp)-
IF((SELECT b.saldo  FROM bspfatura b WHERE b.dtbsp = :dtbsp  AND b.cia=bspfatura.cia AND b.numero=bspfatura.numero AND b.nfaturabsp IS NULL GROUP BY b.cia) IS NULL,0, 
     (SELECT b.saldo FROM bspfatura b WHERE b.dtbsp = :dtbsp  AND b.cia=bspfatura.cia AND b.numero=bspfatura.numero AND b.nfaturabsp IS NULL GROUP BY b.cia))
)as bsp,           /*saldo devido*/

 sum(if( pago is null,0,pago)) as pago,      /*Já pago*/
 sum(if(saldo is null,0,saldo)) as saldo,    /*A pagar*/
 sum(if(fatbsp is null,0,fatbsp)) as fatbsp, /*fatura*/
 sum( if(diferenca is null,0,diferenca)) as diferenca,
 (SELECT ciacode.nomecia FROM ciacode WHERE ciacode.codigo=bspfatura.cia) as cia,
 dtbsp,
 sum(txus) as txus, 
 sum(txrs) as txrs, 
 sum(txseg) as txseg, 
 sum(txsegrs) as txsegrs, 
 sum(vlbil) as vlbil, 
 sum(cccf) as cccf,
 nfaturabsp,
 SUBSTRING(
 (SELECT GROUP_CONCAT(DISTINCT nrcarta) 
  FROM bspfatura b 
  WHERE
   b.dtbsp = :dtbsp        AND
   b.cia   = bspfatura.cia AND 
   b.nfaturabsp = bspfatura.nfaturabsp
  GROUP BY b.cia ),1,50 ) as nrcarta
FROM
 bspfatura
where 
 dtbsp = :dtbsp  
group by
 cia

Code: Select all

 Qsoma.Close;
 Qsoma.ParamByName('dtbsp').Value :=FormatDateTime('yyyy-mm-dd',StrToDate(CBbsp.Items.Strings[CBbsp.ItemIndex])) ;
 Qsoma.Execute;