Manual Técnico de Consultas do Dashboard

Manual Técnico com os SQLs das consultas de cada gráfico do dashboard. ** Essas são as consultas padrões, os quadros podem ser customizados para cada cliente.

Manual Técnico de consultas do Dashboard

1. Objetivo

Este manual tem por finalidade, demostrar as consultas SQLs utilizadas em cada um dos quadrantes do Dashboard.

2. Informações importantes

As consultas demonstradas abaixo são as consultas padrões, as mesmas podem ser customizadas a pedido do cliente.

3. Conteúdo

As consultas demonstradas abaixo são as consultas padrões, as mesmas podem ser customizadas a pedido do cliente.

MAIOR VENDA 30 DIAS

 

select max(total_doc) as REG from acem1401 doc left outer join afvc0901 op On (doc.operacao_doc=op.cod_op) where op.tipo_op in('1','2','4','7')  and doc.data >= '10/22/19'

 

 

MARIO VENDA EM 12 MESES

 

select sum(total_doc)/12 as REG from acem1401 doc left outer join afvc0901 op On (doc.operacao_doc=op.cod_op) where op.tipo_op in('1','2','4','7')  and doc.data >= '11/21/18'

 

 

 

ESTOQUE

 

select sum(estoque*pco_custo) as REG from acec1101  where estoque>0 and pco_custo>0

 

 

 

QUANTIDADE CLIENTES

 

select count( distinct doc.cod_cli) as REG from acem1401 doc left outer join afvc0901 op On (doc.operacao_doc=op.cod_op) where op.tipo_op in('1','2','4','7')  and doc.data >= '11/21/18'

 

 

 

META VENDA MENSAL

 

Valor Vendido =  select sum(total_doc) as REG from acem1401 doc left outer join afvc0901 op On (doc.operacao_doc=op.cod_op) where op.tipo_op in('1','2','4','7')  and extract(month from doc.data) = '11' and extract(year from doc.data) = '2019'

 

 

Valor Configurado = select sum(MMvenda) as REG from ea where 1=1

 

 

 

 

 

 

 

VALOR RECEBIMENTO MENSAL

 

 

Valor Recebido = select sum(valor_entrada) as REG from acxm9201 m left outer join acxc9001 c on (m.conta=c.conta)        where (c.conta <='000100' or (c.conta_de_banco='S') ) and extract(month from m.DATA_MVTO) = '11' and extract(year from m.DATA_MVTO) = '2019'

 

Valor Configurado = select sum(MMReceita) as REG from ea where 1=1

 

 

 

 

% META DE DESPESAS DO MÊS

 

Valor Despesas = select sum(valor_saida) as REG from acxm9201 m left outer join acxc9001 c on (m.conta=c.conta)        where (c.conta <='000100' or (c.conta_de_banco='S') ) and extract(month from m.DATA_MVTO) = '11' and extract(year from m.DATA_MVTO) = '2019'

 

Valor Configurado = select sum(MMDespesa) as REG from ea where 1=1

 

 

 

VENDAS POR REGIÃO

 

select first 3 cast( sum(total_doc) as integer) as reg, coalesce(vend.fnome,0) as reg2   from acem1401 doc left outer join afvc0901 op On (doc.operacao_doc=op.cod_op) left outer join augc0501 vend On (doc.vendedor_doc=vend.fcod) where op.tipo_op in('1','2','4','7')  and doc.data >= '10/22/19' and coalesce(vend.fnome,'') <> ''  group by 2  order by 1 desc 

 

 

 

VENDAS POR PRODUTO 30 DIAS

 

select first 3 cast( sum(it.valor_total_unit) as integer) as reg, coalesce(pr.descricao,0) as reg2   from acem14it it  join afvc0901 op On (it.cod_op=op.cod_op)           join acec1101 pr On (it.codigoproduto=pr.codigo)  where op.tipo_op in('1','2','4','7')  and not(pr.descricao is null)  and it.data >= '10/21/19' group by 2  order by 1 desc 

 

 

 

 

 

 

VENDAS MENSAIS 6 MESES

 

select cast( sum(total_doc) as integer), extract(month from doc.data)||'/'|| extract(year from doc.data),extract(month from doc.data), extract(year from doc.data)    from acem1401 doc left outer join afvc0901 op On (doc.operacao_doc=op.cod_op) where op.tipo_op in('1','2','4','7')   and doc.data >= '05/21/19'  Group By 2, 3, 4 Order by 3, 2

 

 

 

DESPESAS MES A MES

 

-- JUN/19

select cast( sum(m.valor_saida) as integer) as reg, cast( sum(m.valor_entrada) as integer) as reg2   from acxm9201 m left outer join acxc9001 c on (m.conta=c.conta)        where (c.conta <='000100' or (c.conta_de_banco='S') )   and extract(month from m.data_mvto) = '06'  and extract(year from m.data_mvto) = '2019'

 

 

--JUL/19

select cast( sum(m.valor_saida) as integer) as reg, cast( sum(m.valor_entrada) as integer) as reg2   from acxm9201 m left outer join acxc9001 c on (m.conta=c.conta)        where (c.conta <='000100' or (c.conta_de_banco='S') )   and extract(month from m.data_mvto) = '07'  and extract(year from m.data_mvto) = '2019'

 

--AGO/19

select cast( sum(m.valor_saida) as integer) as reg, cast( sum(m.valor_entrada) as integer) as reg2   from acxm9201 m left outer join acxc9001 c on (m.conta=c.conta)        where (c.conta <='000100' or (c.conta_de_banco='S') )   and extract(month from m.data_mvto) = '08'  and extract(year from m.data_mvto) = '2019'

 

--SET/19

select cast( sum(m.valor_saida) as integer) as reg, cast( sum(m.valor_entrada) as integer) as reg2   from acxm9201 m left outer join acxc9001 c on (m.conta=c.conta)        where (c.conta <='000100' or (c.conta_de_banco='S') )   and extract(month from m.data_mvto) = '09'  and extract(year from m.data_mvto) = '2019'

 

--OUT/19

select cast( sum(m.valor_saida) as integer) as reg, cast( sum(m.valor_entrada) as integer) as reg2   from acxm9201 m left outer join acxc9001 c on (m.conta=c.conta)        where (c.conta <='000100' or (c.conta_de_banco='S') )   and extract(month from m.data_mvto) = '10'  and extract(year from m.data_mvto) = '2019'

 

--NOV/19

select cast( sum(m.valor_saida) as integer) as reg, cast( sum(m.valor_entrada) as integer) as reg2   from acxm9201 m left outer join acxc9001 c on (m.conta=c.conta)        where (c.conta <='000100' or (c.conta_de_banco='S') )   and m.data_mvto >= '11/01/19'  and m.data_mvto <= '11/21/19'

 

PREVISAO DIARIO CAIXA

--1º DIA

VALOR CREDITO = select cast( sum(c.valor_documento) as integer) as REG from ACRC5001 c  WHERE coalesce( C.situacao_documento, '') IN ('', ' ')   and c.data_vencimento = '11/22/19'

VALOR DEBITO = select cast( sum(c.valor_dcto) as integer) as REG from acpc6001 c  WHERE coalesce( c.situacao, '') IN ('', ' ')   and c.data_vcto = '11/22/19'

 

--2º DIA

VALOR CREDITO = select cast( sum(c.valor_documento) as integer) as REG from ACRC5001 c  WHERE coalesce( C.situacao_documento, '') IN ('', ' ')   and c.data_vencimento = '11/23/19'

VALOR DEBITO = select cast( sum(c.valor_dcto) as integer) as REG from acpc6001 c  WHERE coalesce( c.situacao, '') IN ('', ' ')   and c.data_vcto = '11/23/19'

 

--3º DIA

VALOR CREDITO = select cast( sum(c.valor_documento) as integer) as REG from ACRC5001 c  WHERE coalesce( C.situacao_documento, '') IN ('', ' ')   and c.data_vencimento = '11/24/19'

VALOR DEBITO = select cast( sum(c.valor_dcto) as integer) as REG from acpc6001 c  WHERE coalesce( c.situacao, '') IN ('', ' ')   and c.data_vcto = '11/24/19'

 

 

--4º DIA até 30º

-- Repete o SQL só troca o dia

 

 

 

 

SALDO CURVA TENDENCIAL CAIXA

 

--Saldo

select first 1 s.data as REG1 ,    sum(s.valor)   as REG2 from acxc9301 s left outer join acxc9001 c on (s.conta=c.conta)   where (c.conta <='000100' or (c.conta_de_banco='S') ) group by data order by s.data desc

 

--1º dia  credito-debito

select  sum(m.valor_entrada)  - sum(m.valor_saida)  as REG from acxm9201 m left outer join acxc9001 c on (m.conta=c.conta)        where (c.conta <='000100' or (c.conta_de_banco='S') )   and m.data_mvto <= '11/20/19'  and m.data_mvto > '01/31/18'