Consult results using SQL or tips on programming in XAILER

Asked

Viewed 64 times

1

I have the following relation between 3 tables:

'SELECT contareceber.codcontareceber, contareceber.codorganizacao, 
contareceber.dtdocumento, contareceber.dtvencimento, 
contareceber.valortotalbruto, contareceber.valortotalrecebido, 
contareceber.dtcancelamento, contareceberquitacao.dtquitacao 
FROM contareceber 
LEFT JOIN contareceberquitacaoitem ON 
contareceberquitacaoitem.codcontareceber = contareceber.codcontareceber 
LEFT JOIN contareceberquitacao ON 
contareceberquitacao.codcontareceberquitacao = 
contareceberquitacaoitem.codcontareceberquitacao 
WHERE contareceber.dtdocumento>='+ValToSQL(::oDataInicial:Value)+' and 
contareceber.dtdocumento<='+ValToSQL(::oDataFinal:Value)+' 
ORDER BY contareceber.codorganizacao'

That being ::oDataInitial:Value and ::oDataFinal:Value are the date values that the user will enter in my program, I just made a program to run this SELECT and bring me the information I need in a temporary table.

After I have this information I need to deliver the following data: (either through another SQL command or any other way.)

  • TITLES
  • PAID SECURITIES

  • OPEN TITLES

  • PAID ON TIME

  • PAID IN UP TO 30 DAYS
  • PAID BETWEEN 30 AND 60 DAYS
  • PAID BETWEEN 60 AND 90 DAYS
  • PAID BETWEEN 90 AND 120 DAYS
  • PAID BETWEEN 120 AND 180 DAYS
  • PAID OVER 180 DAYS

The temporario is created this way:

DBCreate('TEMPORARIO.DBF',{{'CODRECEB'   ,'C',12,0},; //codigo do contas a receber
                           {'CODORG'     ,'C', 3,0},; //codigo da empresa
                           {'DTDOC'      ,'D', 8,0},; //data de emissao
                           {'DTVENC'     ,'D', 8,0},; //data de vencimento
                           {'VLRTOT'     ,'N',10,2},; //valor total bruto
                           {'VLRTREC'    ,'N',12,2},; //valor total recebido
                           {'DTCANC'     ,'D', 8,0},; //data cancelamento
                           {'DTQUIT'     ,'D', 8,0},; //data quitação
                           {'PRAZO'      ,'N',10,0},; //prazo em dias para quitar
                           {'DIASATEPAG' ,'N',10,0},; //qtd de dias até pagar
                           {'EXCEDENTE'  ,'N',10,0}}) //quantos dias passou do prazo

I have this information to compare and bring the results of the dates. On the tables I’m looking for, they have this structure:

CREATE TABLE CONTARECEBER (
CODCONTARECEBER          VARCHAR(12) NOT NULL COLLATE WIN_PTBR,
CODORGANIZACAO           VARCHAR(3) COLLATE WIN_PTBR,
STTIPOCONTARECEBER       VARCHAR(1) COLLATE WIN_PTBR,
DTDOCUMENTO              DATE,
DTVENCIMENTO             DATE,
SEQUENCIALPARCELA        INTEGER,
STMANUALSISTEMA          VARCHAR(1) COLLATE WIN_PTBR,
NUMERODOCUMENTO          VARCHAR(20) COLLATE WIN_PTBR,
VALORTOTALBRUTO          NUMERIC(10,2),
VALORTOTALJUROS          NUMERIC(8,2),
VALORTOTALDESCONTO       NUMERIC(8,2),
VALORTOTALRECEBIDO       NUMERIC(12,2),
OBSERVACAO               VARCHAR(50) COLLATE WIN_PTBR,
CODVENDA                 VARCHAR(15) COLLATE WIN_PTBR,
CODCLIENTE               VARCHAR(10) COLLATE WIN_PTBR,
CPFCNPJ                  VARCHAR(14) COLLATE WIN_PTBR,
CODCHEQUE                VARCHAR(10) COLLATE WIN_PTBR,
CODCONVENIO              VARCHAR(8) COLLATE WIN_PTBR,
CODCARTAO                VARCHAR(6) COLLATE WIN_PTBR,
NUMEROTEF                VARCHAR(20) COLLATE WIN_PTBR,
CODUSUARIO               VARCHAR(8) COLLATE WIN_PTBR,
ST_EXPORT                VARCHAR(1) COLLATE WIN_PTBR,
DTULTIMAATUALIZACAO      DATE,
HRULTIMAATUALIZACAO      TIME,
STCANCELADA              VARCHAR(1) COLLATE WIN_PTBR,
CODUSUARIOCANCELAMENTO   VARCHAR(8) COLLATE WIN_PTBR,
DTCANCELAMENTO           DATE,
HRCANCELAMENTO           TIME,
STVENDAFINANCIADA        VARCHAR(1) COLLATE WIN_PTBR,
CODFORMAPAGTO            VARCHAR(2) COLLATE WIN_PTBR,
OBSDEVOLUCAOPRODUTO      BLOB SUB_TYPE 1 SEGMENT SIZE 80,
ORGORIGEMATUALIZACAO     VARCHAR(3) COLLATE WIN_PTBR,
ID_REP_ORIGEM            BIGINT
);


CREATE TABLE CONTARECEBERQUITACAOITEM (
CODCONTARECEBERQUITACAO  VARCHAR(12) NOT NULL COLLATE WIN_PTBR,
CODCONTARECEBER          VARCHAR(12) NOT NULL COLLATE WIN_PTBR,
VALORJUROS               NUMERIC(8,2),
VALORRECEBIDO            NUMERIC(8,2),
VALORDESCONTO            NUMERIC(8,2),
ST_EXPORT                VARCHAR(1) COLLATE WIN_PTBR,
DTULTIMAATUALIZACAO      DATE,
HRULTIMAATUALIZACAO      TIME,
ORGORIGEMATUALIZACAO     VARCHAR(3) COLLATE WIN_PTBR,
ID_REP_ORIGEM            BIGINT
);

CREATE TABLE CONTARECEBERQUITACAO (
CODCONTARECEBERQUITACAO       VARCHAR(12) NOT NULL COLLATE WIN_PTBR,
CODUSUARIO                    VARCHAR(8) COLLATE WIN_PTBR,
CODORGANIZACAO                VARCHAR(3) COLLATE WIN_PTBR,
STTIPOQUITACAO                VARCHAR(1) COLLATE WIN_PTBR,
STFORMAPAGTO                  VARCHAR(1) COLLATE WIN_PTBR,
CODFUNCIONARIO                VARCHAR(7) COLLATE WIN_PTBR,
CODCLIENTE                    VARCHAR(10) COLLATE WIN_PTBR,
CODCONVENIO                   VARCHAR(8) COLLATE WIN_PTBR,
CODCARTAO                     VARCHAR(6) COLLATE WIN_PTBR,
DTQUITACAO                    DATE,
HRQUITACAO                    TIME,
STCOBRARJUROS                 VARCHAR(1) COLLATE WIN_PTBR,
PERCJUROS                     NUMERIC(5,2),
PERCMULTA                     NUMERIC(5,2),
STACERTOAUTOMATICO            VARCHAR(1) COLLATE WIN_PTBR,
STATUALIZARPRECOATUAL         VARCHAR(1) COLLATE WIN_PTBR,
VALORTOTAL                    NUMERIC(10,2),
VALORDESCONTO                 NUMERIC(10,2),
VALORTOTALJUROS               NUMERIC(10,2),
STCANCELADA                   VARCHAR(1) COLLATE WIN_PTBR,
DTCANCELAMENTO                DATE,
HRCANCELAMENTO                TIME,
CODUSUARIOCANCELAMENTO        VARCHAR(8) COLLATE WIN_PTBR,
CODLANCAMENTOCONTAFINANCEIRA  VARCHAR(10) COLLATE WIN_PTBR,
ST_EXPORT                     VARCHAR(1) COLLATE WIN_PTBR,
DTULTIMAATUALIZACAO           DATE,
HRULTIMAATUALIZACAO           TIME,
STCHEQUETERCEIROS             VARCHAR(1) COLLATE WIN_PTBR,
OBSCANCELAMENTO               BLOB SUB_TYPE 1 SEGMENT SIZE 80,
NR_ACERTO                     VARCHAR(8) COLLATE WIN_PTBR,
CODECF                        VARCHAR(8) COLLATE WIN_PTBR,
NUMEROCOO                     VARCHAR(6) COLLATE WIN_PTBR,
NUMEROGNF                     VARCHAR(6) COLLATE WIN_PTBR,
CODSESSAOECFTEF               VARCHAR(15) COLLATE WIN_PTBR,
STCUPOMECFCANCELADO           VARCHAR(1) COLLATE WIN_PTBR,
ORGORIGEMATUALIZACAO          VARCHAR(3) COLLATE WIN_PTBR,
ID_REP_ORIGEM                 BIGINT
);
sql firebird clipper

  • Do you have any way of informing the structure of the table in question? Because what you need is to add more filters to your WHERE, but without knowing what the fields are, it is difficult to determine what you will put.

  • Yes, I’ll add it to the question. Thank you!

1 answer

1


Well I’ll do a translation here of what you said you need:

  • TITLES - Would be all the titles, inside the filter you’ve already determined.
  • PAID SECURITIES - Would be all titles, inside the filter, and that have the DTQUITACAO different from null
  • OPEN TITLES - Would be all titles, inside the filter, and with the DTQUITACAO with null value
  • PAID ON TIME - Would be all securities, within the filter, and with DTQUITACAO less than or equal to DTVENCIMENTO.
  • PAID UP TO... BETWEEN ... MORE - Would be all titles, within the filter, that have the DTQUITACAO different from null and with the amount of days within a period of DTVENCIMENTO. (It wasn’t very clear if that’s what you’d like, correct me if I’m wrong).

Given that the respective Sqls within what gave to capture from your database would be:

TITLES:

SELECT CONTARECEBER.CODCONTARECEBER,
       CONTARECEBER.CODORGANIZACAO,
       CONTARECEBER.DTDOCUMENTO,
       CONTARECEBER.DTVENCIMENTO,
       CONTARECEBER.VALORTOTALBRUTO,
       CONTARECEBER.VALORTOTALRECEBIDO,
       CONTARECEBER.DTCANCELAMENTO,
       CONTARECEBERQUITACAO.DTQUITACAO
FROM CONTARECEBER
LEFT OUTER JOIN CONTARECEBERQUITACAOITEM ON
       CONTARECEBER.CODCONTARECEBER = CONTARECEBERQUITACAOITEM.CODCONTARECEBER
LEFT OUTER JOIN CONTARECEBERQUITACAO ON
       CONTARECEBERQUITACAOITEM.CODCONTARECEBERQUITACAO = CONTARECEBERQUITACAO.CODCONTARECEBERQUITACAO
WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
ORDER BY CONTARECEBER.CODORGANIZACAO

The others will change only the WHERE to simplify.

PAID SECURITIES:

WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
AND CONTARECEBERQUITACAO.DTQUITACAO IS NOT NULL

OPEN TITLES:

WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
AND CONTARECEBERQUITACAO.DTQUITACAO IS NULL

PAID ON TIME:

WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
AND CONTARECEBERQUITACAO.DTQUITACAO <= CONTARECEBER.DTVENCIMENTO

PAID UP TO... BETWEEN ... MORE

WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal
AND CONTARECEBERQUITACAO.DTQUITACAO IS NOT NULL 
AND CONTARECEBER.DTVENCIMENTO BETWEEN '2017-06-08' AND '2017-07-08'

Being this last example seeking today up to 30 days forward, just adapt it to the situations you cited. This is clear if I understood right this your last need.

  • The first 4 solutions served me very well, if you have any way to return me the added values too, have records in codcontareceber that repeats. The Fifth solution, I didn’t quite understand... by what type, has to be 30 days from the due date.. pro we have those who were paid on time... as I would add up to 30 days on due dates?

  • I think I understand now what you want. Testa por favor o seguinte comando:&#xA;&#xA;WHERE CONTARECEBER.DTDOCUMENTO BETWEEN :DataInicial AND :DataFinal&#xA; AND CONTARECEBERQUITACAO.DTQUITACAO IS NOT NULL &#xA; AND CONTARECEBER.DTVENCIMENTO BETWEEN CONTARECEBER.DTVENCIMENTO AND DATEADD(30 day to CONTARECEBER.DTEXPIRATION) Using the DATEADD function to add a period to the field.

Browser other questions tagged sql firebird clipper

You are not signed in. Login or sign up in order to post.