on 12-15-2016 11:35 AM
Prezados, bom dia!
Estou tentando fazer um relatório onde parte dele apresenta o total de clientes novos dentro do período selecionado, ou seja, tem que considerar todo meu banco de dados até a maior data que selecionei, mesmo podendo ter selecionado apenas o dia de ontem.
Dando sequência no problema, também preciso criar um gráfico onde ele apresente o nº de clientes novos por mês.
Segue parte do script:
SELECT 'NF SAÍDA'AS 'TIPO DOC',
T0.[DocEntry],
T0.[DocDate],
T0.[CardCode] 'Código Cliente',
T0.[CardName] 'Nome Cliente',
cast(T7.[ItmsGrpCod] as varchar),
T7.[ItmsGrpNam]'Grupo do Item',
T5.[ItemCode],
T5.[Dscription] 'Descrição do Item',
T5.[Price] 'Valor Unitário',
T5.[Quantity],
T5.[LineTotal],
T2.[CityS],
T2.[StateS],
T0.[Serial],
T3.[SlpName],
T4.[BPLName],
T5.[ObjType],
(SELECT SUM(TAXSUM)FROM INV4 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum and staType in('3','12'))'Imposto',
ISNULL((SELECT LineTotal FROM INV2 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum AND GroupNum=0), 0.00)'Frete',
ISNULL((SELECT LineTotal+VatSum FROM INV2 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum AND GroupNum=1), 0.00)'Embalagem',
(T5.LineTotal+(SELECT SUM(TAXSUM)FROM INV4 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum and staType in('3','12'))+
ISNULL((SELECT LineTotal FROM INV2 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum AND GroupNum=0), 0.00)+
ISNULL((SELECT LineTotal+VatSum FROM INV2 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum AND GroupNum=1), 0.00))'Total NF',
(Select CASE
when count(*) = 1 then 1
else 0
end
from OINV
WHERE CardCode = T0.CardCode)'Contagem de clientes novos'
FROM OINV T0 INNER JOIN INV12 T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode
INNER JOIN OBPL T4 ON T0.BPLId = T4.BPLId
INNER JOIN INV1 T5 ON T0.DocEntry = T5.DocEntry
INNER JOIN OITM T6 ON T5.ItemCode = T6.ItemCode
INNER JOIN OITB T7 ON T6.ItmsGrpCod = T7.ItmsGrpCod
WHERE T0.DocDate between convert(varchar(10), @DataInicial, 103) and convert(varchar(10), @DataFinal, 103)
AND T0.Canceled ='N'
AND(T0.InvntSttus <> 'C'
AND T5.DocEntry NOT in(select ISNULL(T11.BaseEntry, 0) from ORIN T00
inner join RIN1 T11 ON T00.DocEntry = T11.DocEntry
where T00.SeqCode <> '1'))
Olá Erikson,
Pelo que pude observar na sua query, um cliente deve ser conderado novo na primeira emissão de nota para ele, com esse raciocínio, fiz uma alteração na sua query onde quando for a primeira emissão de um documento para determinado cliente, indicará que ele é novo.
SELECT 'NF SAÍDA'AS 'TIPO DOC',
T0.[DocEntry],
T0.[DocDate],
T0.[CardCode] 'Código Cliente',
T0.[CardName] 'Nome Cliente',
cast(T7.[ItmsGrpCod] as varchar),
T7.[ItmsGrpNam]'Grupo do Item',
T5.[ItemCode],
T5.[Dscription] 'Descrição do Item',
T5.[Price] 'Valor Unitário',
T5.[Quantity],
T5.[LineTotal],
T2.[CityS],
T2.[StateS],
T0.[Serial],
T3.[SlpName],
T4.[BPLName],
T5.[ObjType],
(SELECT SUM(TAXSUM)FROM INV4 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum and staType in('3','12'))'Imposto',
ISNULL((SELECT LineTotal FROM INV2 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum AND GroupNum=0), 0.00)'Frete',
ISNULL((SELECT LineTotal+VatSum FROM INV2 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum AND GroupNum=1), 0.00)'Embalagem',
(T5.LineTotal+(SELECT SUM(TAXSUM)FROM INV4 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum and staType in('3','12'))+
ISNULL((SELECT LineTotal FROM INV2 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum AND GroupNum=0), 0.00)+
ISNULL((SELECT LineTotal+VatSum FROM INV2 WHERE DocEntry=T5.DocEntry AND LineNum=T5.LineNum AND GroupNum=1), 0.00))'Total NF',
(SELECT TOP 1
CASE WHEN DocEntry = T0.DocEntry THEN 1
ELSE 0
END
FROM
OINV
WHERE
CardCode = T0.CardCode) AS 'Cliente Novo'
FROM
OINV T0
INNER JOIN INV12 T2 ON T0.DocEntry = T2.DocEntry
INNER JOIN OSLP T3 ON T0.SlpCode = T3.SlpCode
INNER JOIN OBPL T4 ON T0.BPLId = T4.BPLId
INNER JOIN INV1 T5 ON T0.DocEntry = T5.DocEntry
INNER JOIN OITM T6 ON T5.ItemCode = T6.ItemCode
INNER JOIN OITB T7 ON T6.ItmsGrpCod = T7.ItmsGrpCod
WHERE
T0.DocDate between convert(varchar(10), @DataInicial, 103) and convert(varchar(10), @DataFinal, 103)
AND T0.Canceled ='N'
AND(T0.InvntSttus <> 'C'
AND T5.DocEntry NOT in(select ISNULL(T11.BaseEntry, 0) from ORIN T00
inner join RIN1 T11 ON T00.DocEntry = T11.DocEntry
where T00.SeqCode <> '1'))
Agora para saber quantos clientes novos teve cada mês, sugiro uma query separada como esta:
SELECT
ano,
mes,
COUNT(CardCode) 'Numero clientes Novos'
FROM (
SELECT
CardCode,
YEAR(MIN(DocDate)) as ano,
MONTH(MIN(DocDate)) as mes
FROM
OINV
GROUP BY
CardCode) T0
GROUP BY
T0.ano, T0.mes
Espero que ajude.
Att,
Diego Lother
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
103 | |
12 | |
11 | |
6 | |
6 | |
4 | |
3 | |
3 | |
3 | |
3 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.