Jump to content
Fórum Script Brasil
  • 0
Sign in to follow this  
fahledu

Melhorar código

Question

Boas pessoal

SQL não é meu forte, mas fiz uma query para identificar cadastros que possuem vínculos com outros cadastros e possuem a mesma base.

Está funcionando (aparentemente ^^) porem acredito ter feito de uma forma menos pratica, então como eu poderia melhorar esse script?

select distinct
SUBSTRING(B1.STCD1KNA1,1,8) [Base CNPJ]
,B2.KUNNR [Cliente]
,VKORG [Sales Org]
,KUNN2 [Payer]
from B2
INNER JOIN B1 ON B2.KUNNR = B1.KUNNR
WHERE PARVW = 'RG'
AND B2.KUNNR <> KUNN2
and B1.STCD1KNA1 <> ''
and LAND1KNA1 = 'BR'
and  SUBSTRING(B1.STCD1KNA1,1,8) in (
select a.[Base CNPJ]  from (
select distinct
SUBSTRING(B1.STCD1KNA1,1,8) [Base CNPJ]
,count(SUBSTRING(B1.STCD1KNA1,1,8)) as [qtde A]
from B2
INNER JOIN B1 ON B2.KUNNR = B1.KUNNR
WHERE PARVW = 'RG'
AND B2.KUNNR <> KUNN2
and B1.STCD1KNA1 <> ''
and LAND1KNA1 = 'BR'
group by SUBSTRING(B1.STCD1KNA1,1,8)
having count(SUBSTRING(B1.STCD1KNA1,1,8)) > 1 ) as a
join (
select distinct
(SUBSTRING(B1.STCD1KNA1,1,8)+kunn2) [Base CNPJ]
,count(SUBSTRING(B1.STCD1KNA1,1,8)+kunn2) [qtde B]
from B2
INNER JOIN B1 ON B2.KUNNR = B1.KUNNR
WHERE PARVW = 'RG'
AND B2.KUNNR <> KUNN2
and B1.STCD1KNA1 <> ''
and LAND1KNA1 = 'BR'
group by (SUBSTRING(B1.STCD1KNA1,1,8)+kunn2)
having count(SUBSTRING(B1.STCD1KNA1,1,8)+kunn2) > 1) B on a.[Base CNPJ] = substring(B.[Base CNPJ],1,8)
where [qtde A] <> [qtde B]
) 
group by SUBSTRING(B1.STCD1KNA1,1,8), B2.KUNNR, B2.VKORG, B2.KUNN2 
order by [Base CNPJ], Payer 

Abrax

Share this post


Link to post
Share on other sites

1 answer to this question

Recommended Posts

  • 0

Opa.

Vê se dessa forma te ajuda:

/*
	NOTA:

	- O TIPO DE DADO DE CADA CAMPO VOCÊ ALTERA CONFORME A SUA NECESSIDADE;
	- FIZ COM VARIÁVEL TABELA, POIS NÃO SEI A QUANTIDADE DE REGISTRO QUE VAI TRABALHAR,
	MAS SE FOR UM VOLUME GRANDE USE TABELA TEMPORÁRIA COM CHAVE PRIMÁRIA: (CREATE TABLE #TB_BASE)
*/

DECLARE @TB_BASE TABLE
(
	BASE_CNPJ		VARCHAR(8),
	CLIENTE			VARCHAR(8),
	SALES_ORG		VARCHAR(8),
	PAYER			VARCHAR(8)			
)

INSERT INTO @TB_BASE
	SELECT DISTINCT
		   SUBSTRING(B1.STCD1KNA1, 1, 8) AS BASE_CNPJ,
		   B2.KUNNR AS CLIENTE,
		   VKORG AS SALES_ORG,
		   KUNN2 AS PAYER
	  FROM B2
INNER JOIN B1 ON B2.KUNNR = B1.KUNNR
	 WHERE PARVW = 'RG'
	   AND B2.KUNNR <> KUNN2
	   AND B1.STCD1KNA1 <> ''
	   AND LAND1KNA1 = 'BR'

-- FINAL
	SELECT TB1.BASE_CNPJ,
		   TB1.CLIENTE,
		   TB1.SALES_ORG,
		   TB1.PAYER
	  FROM @TB_BASE TB1
INNER JOIN (SELECT A.BASE_CNPJ
			  FROM (SELECT DISTINCT
						   TB.BASE_CNPJ,
						   COUNT(TB.BASE_CNPJ) AS QTDE_A
					  FROM @TB_BASE TB
					 GROUP BY TB.BASE_CNPJ
					HAVING COUNT(TB.BASE_CNPJ) > 1
				   ) AS A
		INNER JOIN (SELECT DISTINCT
						   TB.BASE_CNPJ + TB.PAYER AS BASE_CNPJ,
						   COUNT(TB.BASE_CNPJ + TB.PAYER) AS QTDE_B
					  FROM @TB_BASE TB
					 GROUP BY TB.BASE_CNPJ + TB.PAYER
					HAVING COUNT(TB.BASE_CNPJ + TB.PAYER) > 1
				   ) B ON A.BASE_CNPJ = SUBSTRING(B.BASE_CNPJ, 1, 8)
			 WHERE QTDE_A <> QTDE_B
		   ) TB2 ON TB1.BASE_CNPJ = TB2.BASE_CNPJ
	 ORDER BY TB1.BASE_CNPJ,
			  TB1.PAYER

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Answer this question...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  



  • Forum Statistics

    • Total Topics
      148405
    • Total Posts
      643812
×
×
  • Create New...