Array
(
)

Script apresentando um dado a mais no resultado.

Alex Lekao
   - 06 out 2011

Ola Boa tarde!!!
Estou precisando de uma ajuda com um Script onde todos os dados sao apresentados corretamente e uma informacao de um determinado vendedor, um vendedor geral, eh apresentado com uma informacao que a principio nao deveria ser apresentado, ja verifiquei o script varias vezes e nao consigo encontrar o ponto que esta causando este problema.
Preciso de uma ajuda com relacao a isto, estou ficando de cabelos brancos. rsrsr
Ja tentei postar o script aqui e nao estou conseguindo.
vou tentar postar em separado depois.
Abraco.

Alex Lekao
   - 06 out 2011

Script referente ao post.

DECLARE @INICIO DATETIME, @FIM DATETIME, @DATA DATETIME, @QUANTIDADE DECIMAL(10)
SET @INICIO = 01.10.2011
SET @FIM = 30.10.2011
SET @DATA = @INICIO
SET @QUANTIDADE = 0

WHILE @DATA <= @FIM
BEGIN
IF (DATEPART(WEEKDAY, @DATA) = 7)
SET @QUANTIDADE = @QUANTIDADE + 1

SET @DATA = DATEADD(DAY,1,@DATA)
END

DECLARE @INICIO2 DATETIME, @FIM2 DATETIME, @DATA2 DATETIME, @QUANTIDADE2 DECIMAL(10)
SET @INICIO2 = 30.10.2011
SET @FIM2 = 30.10.2011
SET @DATA2 = @INICIO2
SET @QUANTIDADE2 = 0

WHILE @DATA2 <= @FIM2
BEGIN
IF (DATEPART(WEEKDAY, @DATA2) = 7)
SET @QUANTIDADE2 = @QUANTIDADE2 + 1

SET @DATA2 = DATEADD(DAY,1,@DATA2)
END

DECLARE @INICIO3 DATETIME, @FIM3 DATETIME, @DATA3 DATETIME, @QUANTIDADE3 DECIMAL(10)
SET @INICIO3 = 01.10.2011
SET @FIM3 = 30.10.2011
SET @DATA3 = @INICIO3
SET @QUANTIDADE3 = 0

WHILE @DATA3 <= @FIM3
BEGIN
IF (DATEPART(WEEKDAY, @DATA3) = 7)
SET @QUANTIDADE3 = @QUANTIDADE3 + 1

SET @DATA3 = DATEADD(DAY,1,@DATA3)
END

SELECT
R.CODLOJ+R.CODVEN AS CHAVE,
CASE WHEN R.CODLOJ = 001 THEN 001 - LAPA
WHEN R.CODLOJ = 002 THEN 002 - FREG
WHEN R.CODLOJ = 003 THEN 003 - PINH
WHEN R.CODLOJ = 004 THEN 004 - JUND
ELSE SEM LOJA
END AS LOJA,
CASE WHEN DATEPART(MONTH,01.10.2011) = 01 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP01
WHEN DATEPART(MONTH,01.10.2011) = 02 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP02
WHEN DATEPART(MONTH,01.10.2011) = 03 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP03
WHEN DATEPART(MONTH,01.10.2011) = 04 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP04
WHEN DATEPART(MONTH,01.10.2011) = 05 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP05
WHEN DATEPART(MONTH,01.10.2011) = 06 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP06
WHEN DATEPART(MONTH,01.10.2011) = 07 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP07
WHEN DATEPART(MONTH,01.10.2011) = 08 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP08
WHEN DATEPART(MONTH,01.10.2011) = 09 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP09
WHEN DATEPART(MONTH,01.10.2011) = 10 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP10
WHEN DATEPART(MONTH,01.10.2011) = 11 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP11
WHEN DATEPART(MONTH,01.10.2011) = 12 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP12
ELSE MTJ.MTP13
END AS METALOJ,
R.CODVEN+ - +V.NOME AS VENDEDOR,
CASE WHEN DATEPART(MONTH,01.10.2011) = 01 THEN M.MTP01
WHEN DATEPART(MONTH,01.10.2011) = 02 THEN M.MTP02
WHEN DATEPART(MONTH,01.10.2011) = 03 THEN M.MTP03
WHEN DATEPART(MONTH,01.10.2011) = 04 THEN M.MTP04
WHEN DATEPART(MONTH,01.10.2011) = 05 THEN M.MTP05
WHEN DATEPART(MONTH,01.10.2011) = 06 THEN M.MTP06
WHEN DATEPART(MONTH,01.10.2011) = 07 THEN M.MTP07
WHEN DATEPART(MONTH,01.10.2011) = 08 THEN M.MTP08
WHEN DATEPART(MONTH,01.10.2011) = 09 THEN M.MTP09
WHEN DATEPART(MONTH,01.10.2011) = 10 THEN M.MTP10
WHEN DATEPART(MONTH,01.10.2011) = 11 THEN M.MTP11
WHEN DATEPART(MONTH,01.10.2011) = 12 THEN M.MTP12
ELSE M.MTP13
END AS REPRE,
SUM(R.TOTGERAL*0.000) AS METAPORREPRE,
SUM(R.TOTGERAL*0.000) AS METAREPREDIA,
SUM(R.TOTGERAL*0.000) AS METADIA,
(((SELECT
(DATEDIFF(dd, @INICIO2, 30.10.2011))
-(DATEDIFF(wk, @INICIO2, 30.10.2011) * 2)
)+(@QUANTIDADE2/3))-(0)) AS DUTEISFALTAM,
CASE WHEN (R.TIPOREQ=VD)THEN COUNT(DISTINCT(R.NUMREQ)) ELSE 0 END AS QTDVDA,
CASE WHEN (R.TIPOREQ=VD)THEN SUM(R.TOTGERAL) ELSE 0 END AS VENDA,
CASE WHEN (R.TIPOREQ=DV)THEN COUNT(DISTINCT(R.NUMREQ)) ELSE 0 END AS QTDDEV,
CASE WHEN (R.TIPOREQ=DV)THEN SUM(R.TOTGERAL) ELSE 0 END AS Devolucao,
(SELECT SUM(R.TOTGERAL) FROM JACSYSDB.DBO.REQVDA AS R
WHERE (R.TIPOREQ = VD)
AND(R.CODLOJ IN (001))
AND(R.DATEMI >= 01.10.2011)
AND(R.DATEMI <= 30.10.2011)
AND(R.CODCPV NOT IN (80))
) AS TOTGERAL,
SUM(r.totgeral*0.00) AS TOTAL,
SUM(r.totgeral*0.00) AS TOTALDIA,
(SELECT SUM(R2.TOTGERAL) FROM JACSYSDB.DBO.REQVDA AS R2
WHERE (R2.TIPOREQ = VD)
AND(R.CODLOJ IN (001))
AND(R2.DATEMI = CONVERT(VARCHAR(10),GETDATE(),101))
AND(R2.CODCPV NOT IN (80))
AND(R2.CODVEN = R.CODVEN)
) AS TOTDIA,
SUM(R.TOTGERAL*0.000) AS CUMPRIR,
SUM(R.TOTGERAL*0.000) AS CUMPRIRDIA,
SUM(r.totgeral*0.00) AS PARTIC,
SUM(R.TOTGERAL*0.00) AS TIQMEDIO,
(((SELECT
(DATEDIFF(dd, 01.10.2011, 30.10.2011) + 1)
-(DATEDIFF(wk, 01.10.2011, 30.10.2011) * 2)
)+(@QUANTIDADE/3))-(0)) AS DUTEIS,
(((SELECT
(DATEDIFF(dd, 01.10.2011, 30.10.2011) + 1)
-(DATEDIFF(wk, 01.10.2011, 30.10.2011) * 2)
)+(@QUANTIDADE3/3))-(0)) AS DUTEISMETA,
SUM(0.00) AS MEDIAVDA,
SUM(0.00) AS MEDIALIQVDA
FROM JACSYSDB.DBO.REQVDA AS R
LEFT JOIN JACSYSDB.DBO.CLIENTES AS V ON(V.CODCLI=R.CODVEN)
LEFT JOIN JACSYSDB.DBO.CADMTA AS M ON(M.CODVEN=V.CODVEN)
LEFT JOIN JACSYSDB.DBO.CADMTA AS MTJ ON (MTJ.CODLOJ = M.CODLOJ) AND (MTJ.ANO = M.ANO) AND (MTJ.TIPOMTA = LOJ)
WHERE (R.TIPOREQ IN (VD,DV))
AND(R.CODLOJ IN (001))
AND(R.DATEMI >= 01.10.2011)
AND(R.DATEMI <= 30.10.2011)
--AND(R.CODVEN = [DADO3])
AND(R.CODCPV NOT IN (80))
--AND(R.CODVEN NOT IN [DADO7])
GROUP BY R.CODLOJ, R.CODVEN, R.TIPOREQ, V.NOME, CASE WHEN DATEPART(MONTH,01.10.2011) = 01 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP01
WHEN DATEPART(MONTH,01.10.2011) = 02 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP02
WHEN DATEPART(MONTH,01.10.2011) = 03 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP03
WHEN DATEPART(MONTH,01.10.2011) = 04 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP04
WHEN DATEPART(MONTH,01.10.2011) = 05 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP05
WHEN DATEPART(MONTH,01.10.2011) = 06 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP06
WHEN DATEPART(MONTH,01.10.2011) = 07 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP07
WHEN DATEPART(MONTH,01.10.2011) = 08 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP08
WHEN DATEPART(MONTH,01.10.2011) = 09 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP09
WHEN DATEPART(MONTH,01.10.2011) = 10 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP10
WHEN DATEPART(MONTH,01.10.2011) = 11 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP11
WHEN DATEPART(MONTH,01.10.2011) = 12 AND MTJ.CODLOJ = R.CODLOJ THEN MTJ.MTP12
ELSE MTJ.MTP13
END, CASE WHEN DATEPART(MONTH,01.10.2011) = 01 THEN M.MTP01
WHEN DATEPART(MONTH,01.10.2011) = 02 THEN M.MTP02
WHEN DATEPART(MONTH,01.10.2011) = 03 THEN M.MTP03
WHEN DATEPART(MONTH,01.10.2011) = 04 THEN M.MTP04
WHEN DATEPART(MONTH,01.10.2011) = 05 THEN M.MTP05
WHEN DATEPART(MONTH,01.10.2011) = 06 THEN M.MTP06
WHEN DATEPART(MONTH,01.10.2011) = 07 THEN M.MTP07
WHEN DATEPART(MONTH,01.10.2011) = 08 THEN M.MTP08
WHEN DATEPART(MONTH,01.10.2011) = 09 THEN M.MTP09
WHEN DATEPART(MONTH,01.10.2011) = 10 THEN M.MTP10
WHEN DATEPART(MONTH,01.10.2011) = 11 THEN M.MTP11
WHEN DATEPART(MONTH,01.10.2011) = 12 THEN M.MTP12
ELSE M.MTP13
END

Alex Lekao
   - 06 out 2011

Caso seja util, Segue o Resultado.

O problema esta com o vendedor 191 onde apresenta uma metaloj com valor zero, nao deveria aparecer.

CHAVE LOJA METALOJ VENDEDOR REPRE METAPORREPRE METAREPREDIA METADIA DUTEISFALTAM QTDVDA VENDA QTDDEV Devolucao TOTGERAL TOTAL TOTALDIA TOTDIA CUMPRIR CUMPRIRDIA PARTIC TIQMEDIO DUTEIS DUTEISMETA MEDIAVDA MEDIALIQVDA
--------- ---------- --------------------------------------- --------------------------------------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ----------- --------------------------------------- ----------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- --------------------------------------- ---------------------------------------
001000008 001 - LAPA 1751878.00 000008 - ELIAS MOREIRA CARVALHO 0.00 0.00000 0.00000 0.00000 0.000000 1 161.86 0 0.00 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000025 001 - LAPA 1751878.00 000025 - EDVÃ ASSIS FERREIRA DOS SANTOS 4.50 0.00000 0.00000 0.00000 0.000000 0 0.00 11 810.92 351545.60 0.0000 0.0000 1907.91 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000025 001 - LAPA 1751878.00 000025 - EDVÃ ASSIS FERREIRA DOS SANTOS 4.50 0.00000 0.00000 0.00000 0.000000 186 20265.22 0 0.00 351545.60 0.0000 0.0000 1907.91 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000028 001 - LAPA 1751878.00 000028 - MAX FERREIRA PRAXEDES 2.50 0.00000 0.00000 0.00000 0.000000 0 0.00 13 844.90 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000028 001 - LAPA 1751878.00 000028 - MAX FERREIRA PRAXEDES 2.50 0.00000 0.00000 0.00000 0.000000 111 10311.11 0 0.00 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000051 001 - LAPA 1751878.00 000051 - ANTONIO MARCOS AMARAL 10.00 0.00000 0.00000 0.00000 0.000000 0 0.00 5 207.30 351545.60 0.0000 0.0000 8321.08 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000051 001 - LAPA 1751878.00 000051 - ANTONIO MARCOS AMARAL 10.00 0.00000 0.00000 0.00000 0.000000 332 39298.35 0 0.00 351545.60 0.0000 0.0000 8321.08 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000060 001 - LAPA 1751878.00 000060 - PEDRO SEVERIANO MACEDO (ZEZINHO) 12.00 0.00000 0.00000 0.00000 0.000000 0 0.00 14 555.72 351545.60 0.0000 0.0000 13620.49 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000060 001 - LAPA 1751878.00 000060 - PEDRO SEVERIANO MACEDO (ZEZINHO) 12.00 0.00000 0.00000 0.00000 0.000000 398 45810.66 0 0.00 351545.60 0.0000 0.0000 13620.49 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000094 001 - LAPA 1751878.00 000094 - EDCARLOS SANTOS 6.00 0.00000 0.00000 0.00000 0.000000 0 0.00 4 357.35 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000094 001 - LAPA 1751878.00 000094 - EDCARLOS SANTOS 6.00 0.00000 0.00000 0.00000 0.000000 202 22989.30 0 0.00 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000116 001 - LAPA 1751878.00 000116 - JOSE CARLOS 10.00 0.00000 0.00000 0.00000 0.000000 0 0.00 19 1192.00 351545.60 0.0000 0.0000 10970.93 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000116 001 - LAPA 1751878.00 000116 - JOSE CARLOS 10.00 0.00000 0.00000 0.00000 0.000000 306 35702.01 0 0.00 351545.60 0.0000 0.0000 10970.93 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000132 001 - LAPA 1751878.00 000132 - RONALDO BAPTISTA DA SILVA 9.00 0.00000 0.00000 0.00000 0.000000 0 0.00 4 853.46 351545.60 0.0000 0.0000 5199.00 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000132 001 - LAPA 1751878.00 000132 - RONALDO BAPTISTA DA SILVA 9.00 0.00000 0.00000 0.00000 0.000000 181 25035.13 0 0.00 351545.60 0.0000 0.0000 5199.00 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000175 001 - LAPA 1751878.00 000175 - ANDRE AMARAL FERNANDES 8.50 0.00000 0.00000 0.00000 0.000000 0 0.00 12 1344.25 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000175 001 - LAPA 1751878.00 000175 - ANDRE AMARAL FERNANDES 8.50 0.00000 0.00000 0.00000 0.000000 278 26116.92 0 0.00 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000191 001 - LAPA 0.00 000191 - VENDAS GERAL 0.00 0.00000 0.00000 0.00000 0.000000 0 0.00 8 2034.66 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000191 001 - LAPA 1751878.00 000191 - VENDAS GERAL 0.00 0.00000 0.00000 0.00000 0.000000 0 0.00 8 678.22 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000230 001 - LAPA 1751878.00 000230 - THIAGO O. CAMPOS 6.50 0.00000 0.00000 0.00000 0.000000 0 0.00 11 1076.75 351545.60 0.0000 0.0000 5804.91 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000230 001 - LAPA 1751878.00 000230 - THIAGO O. CAMPOS 6.50 0.00000 0.00000 0.00000 0.000000 232 22508.91 0 0.00 351545.60 0.0000 0.0000 5804.91 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000264 001 - LAPA 1751878.00 000264 - EVERTON DA SILVA ALVES 0.00 0.00000 0.00000 0.00000 0.000000 0 0.00 2 252.27 351545.60 0.0000 0.0000 5691.07 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000280 001 - LAPA 1751878.00 000280 - PAULO ROBERTO DA SILVA 6.00 0.00000 0.00000 0.00000 0.000000 0 0.00 5 539.76 351545.60 0.0000 0.0000 2748.74 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000280 001 - LAPA 1751878.00 000280 - PAULO ROBERTO DA SILVA 6.00 0.00000 0.00000 0.00000 0.000000 158 16709.09 0 0.00 351545.60 0.0000 0.0000 2748.74 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000655 001 - LAPA 1751878.00 000655 - LEANDRO RODRIGUES DE FREITAS 12.50 0.00000 0.00000 0.00000 0.000000 0 0.00 5 165.00 351545.60 0.0000 0.0000 10202.52 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000655 001 - LAPA 1751878.00 000655 - LEANDRO RODRIGUES DE FREITAS 12.50 0.00000 0.00000 0.00000 0.000000 322 45811.48 0 0.00 351545.60 0.0000 0.0000 10202.52 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000698 001 - LAPA 1751878.00 000698 - ARNALDO FERREIRA RODRIGUES 6.00 0.00000 0.00000 0.00000 0.000000 0 0.00 4 235.47 351545.60 0.0000 0.0000 6098.90 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000698 001 - LAPA 1751878.00 000698 - ARNALDO FERREIRA RODRIGUES 6.00 0.00000 0.00000 0.00000 0.000000 143 19342.05 0 0.00 351545.60 0.0000 0.0000 6098.90 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000736 001 - LAPA 1751878.00 000736 - ANDRE LUIZ 6.50 0.00000 0.00000 0.00000 0.000000 0 0.00 4 169.36 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00
001000736 001 - LAPA 1751878.00 000736 - ANDRE LUIZ 6.50 0.00000 0.00000 0.00000 0.000000 180 21483.51 0 0.00 351545.60 0.0000 0.0000 NULL 0.00000 0.00000 0.0000 0.0000 21.666666 21.666666 0.00 0.00

(30 linha(s) afetadas)

Alex Lekao
   - 17 out 2011

Ola, boa tarde!!!

Obrigado por quem tentou me ajudar neste problemas, se eh que alguem tentou. rsrsr

Mas ja encontrei onde estava o problema e o mesmo ja foi corrido.

Se alguem puder marcar esse topico como encerrado eu agradeco.

Obrigado mais uma vez.

Abraco.