Ir para conteúdo

POWERED BY:

Arquivado

Este tópico foi arquivado e está fechado para novas respostas.

George C. Lopes

consulta

Por , em SQL Server

Recommended Posts

George C. Lopes    0

George C. Lopes
Postado

Pessoal, bom dia!

 

Utilizamos o ERP da Totvs aqui na empresa, o Protheus. Estou organizando uma consulta para calculo de aliquotas de impostos.

A consulta consiste em pegar a tabela SD2010, e calcular todas as notas de 12 meses atrás para atraves dela calcular o imposto, até aí, tudo ok.

Consigo calcular a soma dos valores e calcular o imposto.

 

Mas na mesma consulta, gostaria de pegar desta mesma tabela (SD2010) a soma das notas do mês atual e multiplicar com a aliquota calculada dos 12 meses, para saber quanto vamos pagar de imposto no mês.

 

Criei dois alias para a mesma tabela, mas quando defino as datas ( uma de 12 meses atras e outra do mes atual ) a consulta para de me retornar resultados, apenas me tras tudo como NULL.

 

Segue abaixo a consulta, quem conseguir me ajudar, agradeço.

 

SELECT 

CASE WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '0' AND '180000' THEN '4.00%'
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '180000' AND '360000' THEN '5.47%'
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '360000' AND '540000' THEN '6.84%'
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '540000' AND '720000' THEN '7.54%'
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '720000' AND '900000' THEN '7.60%'
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '900000' AND '1080000' THEN '8.28%'
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '1080000' AND '1260000' THEN '8.36%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '1260000' AND '1440000' THEN '8.45%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '1440000' AND '1620000' THEN '9.03%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '1620000' AND '1800000' THEN '9.12%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '1800000' AND '1980000' THEN '9.95%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '1980000' AND '2160000' THEN '10.04%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '2160000' AND '2340000' THEN '10.13%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '2340000' AND '2520000' THEN '10.23%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '2520000' AND '2700000' THEN '10.32%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '2700000' AND '2880000' THEN '11.23%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '2880000' AND '3060000' THEN '11.32%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '3060000' AND '3240000' THEN '11.42%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '3240000' AND '3420000' THEN '11.51%' 
	  WHEN SUM(AL.D2_TOTAL + AL.D2_VALIPI + AL.D2_ICMSRET) BETWEEN '3420000' AND '3600000' THEN '11.61%' END ALIQUOTA
, SUM(AT.D2_TOTAL + AT.D2_VALIPI + AT.D2_ICMSRET) TOTAL



FROM SD2010 AL WITH(NOLOCK)
LEFT JOIN SF2010 WITH(NOLOCK) ON AL.D2_FILIAL = F2_FILIAL AND AL.D2_CLIENTE = F2_CLIENTE AND AL.D2_LOJA = F2_LOJA
	AND AL.D2_DOC = F2_DOC AND AL.D2_SERIE = F2_SERIE AND SF2010.D_E_L_E_T_ = ' '
LEFT JOIN SA3010 WITH(NOLOCK) ON A3_COD = F2_VEND1 AND SA3010.D_E_L_E_T_ = ' '
INNER JOIN SA1010 WITH(NOLOCK) ON  AL.D2_CLIENTE = A1_COD AND AL.D2_LOJA = A1_LOJA
INNER JOIN SB1010 WITH(NOLOCK) ON  AL.D2_COD = B1_COD
LEFT JOIN SBM010 WITH(NOLOCK) ON  B1_GRUPO = BM_GRUPO AND SBM010.D_E_L_E_T_ = ' '
LEFT JOIN SD2010 AT ON AT.D2_COD = B1_COD AND AT.D2_CLIENTE = A1_COD AND AT.D2_LOJA = A1_LOJA AND AT.D2_FILIAL = F2_FILIAL
				   AND AT.D2_LOJA = F2_LOJA AND AT.D2_DOC = F2_DOC AND AT.D2_SERIE = F2_SERIE AND AT.D_E_L_E_T_ = ' ' AND AL.D2_TOTAL = AT.D2_TOTAL
				   AND AL.D2_CLIENTE = AT.D2_CLIENTE
WHERE
AL.D_E_L_E_T_ = ' '
AND SA1010.D_E_L_E_T_ = ' '
AND SB1010.D_E_L_E_T_ = ' '
AND AL.D2_FILIAL = '01'
AND (AL.D2_TES = '501' OR AL.D2_TES = '502')
AND SUBSTRING(AT.D2_EMISSAO,1,6) = CONVERT(VARCHAR(6),GETDATE(),112)
AND AL.D2_EMISSAO BETWEEN DATEADD(yy, -1, DATEADD(mm, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) AND DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
AND (AL.D2_SERIE = '10' OR AL.D2_SERIE = '1')

 

 

 

Att,

 

George C. Lopes

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

1) Join com tabelas TOTVS é complicado pois se tem de tratar esta deleção lógica (D_E_L_E_T_ = ' ')

2) O TOTVS não tem nenhum relatório próprio que atenda a este caso ?!

2.1) Já abriu chamado

3) Eu tentaria fazer duas sql´s e depois faria o joins como tabela virtuais

3.1) ex :

  
        select *
        from (select ...
              from   sd2010
              where ....) mes,
             (select ...
              from   sd2010
              where ....) acumulado
       where mes.chave = acumulado.chave

Compartilhar este post

Link para o post
Compartilhar em outros sites

George C. Lopes    0

George C. Lopes
Postado

Boa tarde Motta,

 

Agradeço sua resposta.

 

A TOTVS não possue nenhum relatório deste caso, nem há nenhum parecido.

Não abri chamado, pois como geralmente acontece, eles oferecem consultoria paga ao invés de soluções de problemas.

 

Vou tentar de algum outra forma, caso consiga, volto a postar neste mesmo tópico.

Se alguem mais tiver alguma idéia, agradeço.

 

Att,

 

George C. Lopes

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

Tente fazer duas queries

( uma de 12 meses atras e outra do mes atual )
e depois juntá-las.

 

Passo os mesmos problemas com a TOTVS...

Compartilhar este post

Link para o post
Compartilhar em outros sites

George C. Lopes    0

George C. Lopes
Postado

Motta, não sei se é o jeito mais correto de se fazer, mas consegui realizar a consulta. Vou postar aqui, caso alguem esteja necessitando disto também.

 

 

SELECT 
CONVERT (INT,SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET)) TOTAL
,(SELECT CASE WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '0' AND '180000' THEN '6.00%'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '180000' AND '360000' THEN '8.21%'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '360000' AND '540000' THEN '10.26%'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '540000' AND '720000' THEN '11.31%'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '720000' AND '900000' THEN '11.40%'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '900000' AND '1080000' THEN '12.42%'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1080000' AND '1260000' THEN '12.54%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1260000' AND '1440000' THEN '12.68%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1440000' AND '1620000' THEN '13.55%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1620000' AND '1800000' THEN '13.68%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1800000' AND '1980000' THEN '14.93%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1980000' AND '2160000' THEN '15.06%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2160000' AND '2340000' THEN '15.20%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2340000' AND '2520000' THEN '15.35%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2520000' AND '2700000' THEN '15.48%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2700000' AND '2880000' THEN '16.85%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2880000' AND '3060000' THEN '16.98%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '3060000' AND '3240000' THEN '17.13%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '3240000' AND '3420000' THEN '17.27%' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '3420000' AND '3600000' THEN '17.42%' END ALIQUOTA

FROM SD2010  WITH(NOLOCK)
LEFT JOIN SF2010 WITH(NOLOCK) ON D2_FILIAL = F2_FILIAL AND D2_CLIENTE = F2_CLIENTE AND D2_LOJA = F2_LOJA
	AND D2_DOC = F2_DOC AND D2_SERIE = F2_SERIE AND SF2010.D_E_L_E_T_ = ' '
LEFT JOIN SA3010 WITH(NOLOCK) ON A3_COD = F2_VEND1 AND SA3010.D_E_L_E_T_ = ' '
INNER JOIN SA1010 WITH(NOLOCK) ON  D2_CLIENTE = A1_COD AND D2_LOJA = A1_LOJA
INNER JOIN SB1010 WITH(NOLOCK) ON  D2_COD = B1_COD
LEFT JOIN SBM010 WITH(NOLOCK) ON  B1_GRUPO = BM_GRUPO AND SBM010.D_E_L_E_T_ = ' '
WHERE
SD2010.D_E_L_E_T_ = ' '
AND SA1010.D_E_L_E_T_ = ' '
AND SB1010.D_E_L_E_T_ = ' '
AND SD2010.D2_FILIAL = '02'
AND (SD2010.D2_TES = '501' OR SD2010.D2_TES = '502')
AND SD2010.D2_EMISSAO BETWEEN DATEADD(yy, -1, DATEADD(mm, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) AND DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
AND (SD2010.D2_SERIE = '20' OR SD2010.D2_SERIE = '3')) ALIQUOTA

, 'R$ ' + CAST(SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET)*(SELECT CASE WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '0' AND '180000' THEN '6.00'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '180000' AND '360000' THEN '8.21'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '360000' AND '540000' THEN '10.26'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '540000' AND '720000' THEN '11.31'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '720000' AND '900000' THEN '11.40'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '900000' AND '1080000' THEN '12.42'
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1080000' AND '1260000' THEN '12.54' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1260000' AND '1440000' THEN '12.68' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1440000' AND '1620000' THEN '13.55' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1620000' AND '1800000' THEN '13.68' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1800000' AND '1980000' THEN '14.93' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '1980000' AND '2160000' THEN '15.06' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2160000' AND '2340000' THEN '15.20' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2340000' AND '2520000' THEN '15.35' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2520000' AND '2700000' THEN '15.48' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2700000' AND '2880000' THEN '16.85' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '2880000' AND '3060000' THEN '16.98' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '3060000' AND '3240000' THEN '17.13' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '3240000' AND '3420000' THEN '17.27' 
	  WHEN SUM(D2_TOTAL + D2_VALIPI + D2_ICMSRET) BETWEEN '3420000' AND '3600000' THEN '17.42' END ALIQUOTA

FROM SD2010  WITH(NOLOCK)
LEFT JOIN SF2010 WITH(NOLOCK) ON D2_FILIAL = F2_FILIAL AND D2_CLIENTE = F2_CLIENTE AND D2_LOJA = F2_LOJA
	AND D2_DOC = F2_DOC AND D2_SERIE = F2_SERIE AND SF2010.D_E_L_E_T_ = ' '
LEFT JOIN SA3010 WITH(NOLOCK) ON A3_COD = F2_VEND1 AND SA3010.D_E_L_E_T_ = ' '
INNER JOIN SA1010 WITH(NOLOCK) ON  D2_CLIENTE = A1_COD AND D2_LOJA = A1_LOJA
INNER JOIN SB1010 WITH(NOLOCK) ON  D2_COD = B1_COD
LEFT JOIN SBM010 WITH(NOLOCK) ON  B1_GRUPO = BM_GRUPO AND SBM010.D_E_L_E_T_ = ' '
WHERE
SD2010.D_E_L_E_T_ = ' '
AND SA1010.D_E_L_E_T_ = ' '
AND SB1010.D_E_L_E_T_ = ' '
AND SD2010.D2_FILIAL = '02'
AND (SD2010.D2_TES = '501' OR SD2010.D2_TES = '502')
AND SD2010.D2_EMISSAO BETWEEN DATEADD(yy, -1, DATEADD(mm, -1, DATEADD(MONTH, DATEDIFF(MONTH, 0, GETDATE()), 0))) AND DATEADD(DAY, -(DAY(GETDATE())), GETDATE())
AND (SD2010.D2_SERIE = '20' OR SD2010.D2_SERIE = '3'))/100 AS VARCHAR(12)) IMPOSTO

FROM SD2010  WITH(NOLOCK)
LEFT JOIN SF2010 WITH(NOLOCK) ON D2_FILIAL = F2_FILIAL AND D2_CLIENTE = F2_CLIENTE AND D2_LOJA = F2_LOJA
	AND D2_DOC = F2_DOC AND D2_SERIE = F2_SERIE AND SF2010.D_E_L_E_T_ = ' '
LEFT JOIN SA3010 WITH(NOLOCK) ON A3_COD = F2_VEND1 AND SA3010.D_E_L_E_T_ = ' '
INNER JOIN SA1010 WITH(NOLOCK) ON  D2_CLIENTE = A1_COD AND D2_LOJA = A1_LOJA
INNER JOIN SB1010 WITH(NOLOCK) ON  D2_COD = B1_COD
LEFT JOIN SBM010 WITH(NOLOCK) ON  B1_GRUPO = BM_GRUPO AND SBM010.D_E_L_E_T_ = ' '
WHERE
SD2010.D_E_L_E_T_ = ' '
AND SA1010.D_E_L_E_T_ = ' '
AND SB1010.D_E_L_E_T_ = ' '
AND SD2010.D2_FILIAL = '02'
AND (SD2010.D2_TES = '501' OR SD2010.D2_TES = '502')
AND SUBSTRING(SD2010.D2_EMISSAO,1,6) = CONVERT(VARCHAR(6),GETDATE(),112)
AND (SD2010.D2_SERIE = '20' OR SD2010.D2_SERIE = '3')

Compartilhar este post

Link para o post
Compartilhar em outros sites

Motta    645

Motta
Postado

Vou depois tentar converter para Oracle e tentar rodar aqui, talvez possa ser útil aqui.

Compartilhar este post

Link para o post
Compartilhar em outros sites
Ir para a lista de tópicos SQL Server
×

Informação importante

Ao usar o fórum, você concorda com nossos Termos e condições.

  Aceito