Pessoal, bom dia!
Preciso rodar uma consulta do SQL no script do qlik e nao estou conseguindo. Nao sei onde estou errando. Alguem poderia me ajudar.
Segue script:
SELECT DISTINCT
CONVERT(VARCHAR,vd.DT_VD,103)AS "Data",
vd.CD_FILIAL AS "Filial",
SUM(vd.VLR_PMC_TOTAL) AS "VlrPMC",
SUM(vd.VLR_DESC_OFERTA) AS "VlrDescOferta",
SUM((vd.VLR_IT)*QT_IT) AS "VlrVenda",
SUM(vd.VLR_DESC_IT) AS "VlrDescItens",
SUM(vd.VLR_DESC_VERBA) AS "VlrDescVerba",
SUM(vd.VLR_DESC_IT) AS "VlrDescTotalItens",
SUM(vd.VLR_DESC) AS "VlrDesc",
SUM(vd.VLR_DESC_IT+vd.VLR_DESC_VERBA+vd.VLR_DESC_OFERTA)/ROUND(SUM(vd.VLR_PMC_TOTAL),6)*100 AS "%DescontoFinal",
SUM(vd.TOT_IT) AS "ValorLiquido",
COUNT(DISTINCT vd.NR_ECF) AS "QtdeVendas",
SUM(vd.QT_IT) AS "QtdeItem",
SUM(vd.QT_IT * vd.VLR_CUSTO)AS "VlrCusto",
SUM(vd.TOT_IT)-SUM(vd.QT_IT * vd.VLR_CUSTO)AS "VlrMargemBruta",
(SUM(vd.TOT_IT)-SUM(vd.QT_IT * vd.VLR_CUSTO))/SUM(vd.TOT_IT)*100 AS "%MargemBruta",
SUM(vd.TOT_IT)/COUNT(DISTINCT vd.NR_ECF)AS "TickeMedio",
SUM(CASE
WHEN mix.CD_ARV_MERC_LINHA IN(10,100,109,110) THEN vd.TOT_IT
ELSE 0
END )AS 'Genéricos',
SUM(CASE
WHEN mix.CD_ARV_MERC_LINHA IN(1,2,9,101,107) THEN vd.TOT_IT
ELSE 0
END )AS 'Medicamentos',
SUM(CASE
WHEN mix.CD_ARV_MERC_LINHA IN(3,6,8,99,102,103,108) THEN vd.TOT_IT
ELSE 0
END )AS 'Não Medicamentos'
FROM
V_PDV_VD_IT vd WITH (NOLOCK)
INNER JOIN V_EST_PROD_CPL_ARV_MERCADOLOGICA mix WITH (NOLOCK) ON
vd.CD_EMP = mix.CD_EMP
and vd.CD_FILIAL = mix.CD_FILIAL
and vd.CD_PROD = mix.CD_PROD
WHERE
vd.CD_EMP = 1
AND vd.CD_FILIAL BETWEEN 1 AND 998
AND NOT vd.CD_FILIAL = 99
AND NOT vd.CD_FILIAL BETWEEN 300 AND 997
AND vd.DT_VD between '20130601' AND '20130617'
AND NOT mix.CD_ARV_MERC_LINHA = 105
GROUP BY
vd.DT_VD,
vd.CD_FILIAL
