Remove From My Forums

Agrupar Clientes em uma unica linha, colocar como colunas os tipos de produtos

Pergunta

Entrar para Votar

Tenho um Select que mostra todas as vendas, separado por clientes, tipo de produtos e por periodo

SELECT 
	 ST.SALESNAME
	 ,ST.CUSTACCOUNT
	,ST.SALESGROUP
	,IT.ITEMGROUPID
	
	,sum(case when datepart(month,ST.ShippingDateRequested) = 1 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'JAN' 
	,sum(case when datepart(month,ST.ShippingDateRequested) = 2 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'FEV'
	,sum(case when datepart(month,ST.ShippingDateRequested) = 3 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'MAR'
	,sum(case when datepart(month,ST.ShippingDateRequested) = 4 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'ABR'
	,sum(case when datepart(month,ST.ShippingDateRequested) = 5 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'MAI'
	,sum(case when datepart(month,ST.ShippingDateRequested) = 6 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'JUN'
	,sum(case when datepart(month,ST.ShippingDateRequested) = 7 and datepart(YEAR,ST.ShippingDateRequested) = @ANO then SL.LINEAMOUNT end) as 'JUL'      
    --,ST.ShippingDateRequested
	, SUM(SL.LINEAMOUNT) AS TOTAL
   FROM 
	SALESTABLE ST 
		INNER JOIN SALESLINE SL ON ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID 
	    INNER JOIN SALESOPERATIONTYPE_BR	SPT	 ON	 ST.DATAAREAID	=	SPT.DATAAREAID AND	ST.OPERATIONID	=	SPT.OPERATIONID 
	    INNER JOIN INVENTTABLE IT ON SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID 
	
	WHERE 
		
		
		AND DATEPART(YEAR, ST.SHIPPINGDATEREQUESTED) = @ANO
		
		
		
GROUP BY 
    ST.SALESNAME,
    ST.CUSTACCOUNT,
    ST.SALESGROUP,
    IT.ITEMGROUPID

O retorno repete o cliente para cada tipo de produto, como posso transformar o tipo de produto em coluna ?

Resultado hoje

Resultado Atual

SALESNAME| CUSTACCOUNT |SALESGROUP| ITENGROU | JAN |ETC
Cliente1        001              vende1         roupas              100       200
Cliente1         001             vende1         calçad           200       200


Resutaldo Desejado

SALESNAME|CUSTACCOUNT|SALESGROUP| roupas-jan |calçad-jan
Cliente1              001                vend1            100            200

quarta-feira, 2 de julho de 2014 17:55

Respostas

Entrar para Votar

- Diminui a quantidade de grupo de itens para testar

- Só não consegui colocar mais uma condição dentro do set @SQL :

-- código 3
declare @ANO int = 2014;
declare @SQL varchar(max);

-- monta títulos das colunas de valores mensais de ITEMGROUPID
declare @Meses table (Mes char(3));
INSERT into @Meses values 
    ('jan'),('fev'),('mar'),('abr'),('mai'),('jun'),
    ('jul'),('ago'),('set'),('out'),('nov'),('dez');
declare @MesItem varchar(2000);
set @MesItem= space(0);

SELECT @MesItem+= '[' + I.ITEMGROUPID + '-' + M.Mes + '],'
  from @Meses as M cross join
       (SELECT lower(ITEMGROUPID) as ITEMGROUPID
          from INVENTITEMGROUP
          where DATAAREAID = 'DAT' AND ITEMGROUPID IN ('ACESS. OL', 'Alarmes', 'ALARMES OL', 'FASTCONNEC')) as I;

set @MesItem= Left(@MesItem, DataLength(@MesItem) -1);
--print @MesItem

-- monta o comando SQL com o código da consulta
set @SQL= 
'declare @Data1 date, @Data2 date; ' +
'set @Data1= Convert(date, ''1/1/' + Cast(@ANO as char(4)) + ''', 103); ' +
'set @Data2= Convert(date, ''1/1/' + Cast(@ANO+1 as char(4)) + ''', 103); ' +
'with VendaAnual as (' +
'SELECT ' +
       'ST.SALESNAME '+
      ',ST.CUSTACCOUNT '+
      ',ST.SALESGROUP ' +
      ',(Lower(IT.ITEMGROUPID) + ''-'' + Left( Lower( DateName(month, ST.SHIPPINGDATEREQUESTED) ) ,3)) as MesItem ' +
      ',SL.LINEAMOUNT ' +
   'FROM ' +
       'SALESTABLE ST  inner join ' +
       'SALESLINE SL on ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID  inner join ' +
       'SALESOPERATIONTYPE_BR SPT on ST.DATAAREAID = SPT.DATAAREAID and	ST.OPERATIONID = SPT.OPERATIONID  inner join ' +
       'INVENTTABLE IT on SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID ' +
   'WHERE ST.SHIPPINGDATEREQUESTED >= @Data1 ' +
         'and ST.SHIPPINGDATEREQUESTED < @Data2 ' +
         --'and ST.DATAAREAID =  ' +
') ' +
'SELECT SALESNAME, CUSTACCOUNT, SALESGROUP, ' + @MesItem +
  'from VendaAnual ' +
       'pivot (sum(LINEAMOUNT) for MesItem in (' + @MesItem + ')) as P';

--
--print @SQL
 execute (@SQL);

Marcado como Resposta William_droops quinta-feira, 17 de julho de 2014 12:27

quinta-feira, 10 de julho de 2014 16:08

Todas as Respostas

0

Entrar para Votar

Deleted

quarta-feira, 2 de julho de 2014 19:19

Responder

|

Citação
0

Entrar para Votar

A coluna ITEMGROUPID não tem um valor fixo, pode-se cadastrar uma nova categoria, este valor vem de uma outra tabela.

Nem com o Reporting Services eu estou conseguindo fazer.

William

quinta-feira, 3 de julho de 2014 12:01

Responder

|

Citação

Entrar para Votar

E aí garoto tudo bom? Segue exemplo abaixo (fiz apenas para o mês de Janeiro ok). A solução com Pivot direto ajudaria se você soubesse exatamente quais colunas tem no retorno. Como é dinâmico precisei calcular a quantidade de colunas e o retorno antes de usar pivot. Segue:

--query que traz os registros em colunas:
SELECT Origem, Nome, R.JAN FROM
(
	select s.Origem, t.Nome,
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 1 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JAN',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 2 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'FEV',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 3 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'MAR',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 4 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'ABR',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 5 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'MAI',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 6 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JUN',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 7 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JUL',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 8 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'AGO',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 9 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'SET',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 10 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'OUT',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 11 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'NOV',
			 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 12 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'DEZ'
	from SolicitacaoAerea s inner join TipoFaturamento t on s.TipoFaturamentoId = t.TipoFaturamentoId
	where s.Origem = 'BSB'
	GROUP BY s.Origem, t.Nome
) R 
WHERE JAN > 0

Origem Nome                                                                                                 JAN
------ ---------------------------------------------------------------------------------------------------- -----------
BSB    Cartão de Crédito                                                                                    2
BSB    CARTÃO- EMPRESA1                                                                                     2
BSB    faturado                                                                                             4
BSB    Faturado Internacional                                                                               1
BSB    faturado(Inativo)                                                                                    10
BSB    Pagamento Faturado                                                                                   6

--agora, retornando os registros em linha:
DECLARE @COLS NVARCHAR(MAX),
		  @colsPivot AS NVARCHAR(MAX),
		  @query  AS NVARCHAR(MAX)

--retorno cadeia de caracteres contendo as colunas que retornarei 
select @colsPivot = 
  STUFF((SELECT ', IsNull(' + QUOTENAME(rtrim(X.Nome)) +', 0) as '''+ rtrim(X.Nome) + '-Jan''' 
                    from (
						  SELECT Origem, Nome, R.JAN FROM
									(
										select s.Origem, t.Nome,
												 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 1 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
										from SolicitacaoAerea s inner join TipoFaturamento t on s.TipoFaturamentoId = t.TipoFaturamentoId
										where s.Origem = 'BSB'
										GROUP BY s.Origem, t.Nome
									) R 
									WHERE JAN > 0
						  ) X
                   GROUP BY X.Nome
                   ORDER BY X.Nome
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

--retorno cadeia de caracteres das colunas fisicas
select @COLS = STUFF((SELECT distinct ', ' + QUOTENAME(Nome)
                    from (
						  SELECT Origem, Nome, R.JAN FROM
									(
										select s.Origem, t.Nome,
												 SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 1 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
										from SolicitacaoAerea s inner join TipoFaturamento t on s.TipoFaturamentoId = t.TipoFaturamentoId
										where s.Origem = 'BSB'
										GROUP BY s.Origem, t.Nome
									) R 
									WHERE JAN > 0
						  ) X
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

--montagem dinamica da query:
set @query 
      = 'SELECT Origem,' + @colsPivot + ' FROM 
         (
			SELECT Origem, Nome, R.JAN FROM
					(
						select s.Origem, t.Nome,
									SUM (CASE WHEN DATEPART(MONTH, s.DataInclusao) = 1 AND DATEPART(YEAR, s.DataInclusao) = 2014 THEN 1 ELSE 0 END) AS JAN
						from SolicitacaoAerea s inner join TipoFaturamento t on s.TipoFaturamentoId = t.TipoFaturamentoId
						where s.Origem = ''BSB''
						GROUP BY s.Origem, t.Nome
					) R 
					WHERE JAN > 0
			) X
         pivot 
         (
            sum(JAN)
            for Nome in (' + @cols + ')
         ) p '


execute(@query)

Origem Cartão de Crédito-Jan CARTÃO- EMPRESA1-Jan faturado-Jan Faturado Internacional-Jan faturado(Inativo)-Jan Pagamento Faturado-Jan
------ --------------------- -------------------- ------------ -------------------------- --------------------- ----------------------
BSB    2                     2                    4            1                          10                    6

quinta-feira, 3 de julho de 2014 14:13

0

Entrar para Votar

Obrigado Fabio, vou tentar se der certo marco aqui.

quinta-feira, 3 de julho de 2014 14:19

Responder

|

Citação

Entrar para Votar

Tentei mesmo sem entender o PIVOT direito colocar no meu código e apresenta os seguintes erros

Msg 102, Level 15, State 1, Line 72
Incorrect syntax near 'X'.
Msg 102, Level 15, State 1, Line 87
Incorrect syntax near 'JAN'.

DECLARE @COLS NVARCHAR(MAX),
		  @colsPivot AS NVARCHAR(MAX),
		  @query  AS NVARCHAR(MAX)

--retorno cadeia de caracteres contendo as colunas que retornarei 
select @colsPivot = 
  STUFF((SELECT ', IsNull(' + QUOTENAME(rtrim(X.ITEMGROUPID)) +', 0) as '''+ rtrim(X.ITEMGROUPID) + '-Jan''' 
                    from (
						  SELECT SALESNAME, ITEMGROUPID, R.JAN FROM
									(
										select 
										 ST.SALESNAME
	                                    ,ST.CUSTACCOUNT
	                                    ,ST.SALESGROUP
	                                    ,IT.ITEMGROUPID
									    ,SUM (CASE WHEN DATEPART(MONTH, ST.ShippingDateRequested) = 1 AND DATEPART(YEAR, ST.ShippingDateRequested) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
										from SALESTABLE ST
											INNER JOIN SALESLINE SL ON ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID 
	                                        INNER JOIN SALESOPERATIONTYPE_BR	SPT	 ON	 ST.DATAAREAID	=	SPT.DATAAREAID AND	ST.OPERATIONID	=	SPT.OPERATIONID 
	                                        INNER JOIN INVENTTABLE IT ON SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID 
										 
										WHERE 
		                                 ST.DATAAREAID = 'DAT'
		                                 AND SPT.CHECKISSALES_TAKE = 1 
		                                 AND ST.SALESGROUP <> 'Zoope'
		                                 AND DATEPART(YEAR, ST.SHIPPINGDATEREQUESTED) = 2014
		
		                                 --AND ST.CUSTACCOUNT = '001339_028'
		
GROUP BY 
    ST.SALESNAME,
    ST.CUSTACCOUNT,
    ST.SALESGROUP,IT.ITEMGROUPID
									) R 
									WHERE JAN > 0
						  ) X
                   GROUP BY X.ITEMGROUPID
                   ORDER BY X.ITEMGROUPID
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

--retorno cadeia de caracteres das colunas fisicas
select @COLS = STUFF((SELECT distinct ', ' + QUOTENAME(ITEMGROUPID)
                    from (
						   select 
										 ST.SALESNAME
	                                    ,ST.CUSTACCOUNT
	                                    ,ST.SALESGROUP
	                                    ,IT.ITEMGROUPID
									    ,SUM (CASE WHEN DATEPART(MONTH, ST.ShippingDateRequested) = 1 AND DATEPART(YEAR, ST.ShippingDateRequested) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
										from SALESTABLE ST
											INNER JOIN SALESLINE SL ON ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID 
	                                        INNER JOIN SALESOPERATIONTYPE_BR	SPT	 ON	 ST.DATAAREAID	=	SPT.DATAAREAID AND	ST.OPERATIONID	=	SPT.OPERATIONID 
	                                        INNER JOIN INVENTTABLE IT ON SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID 
										 
										WHERE 
		                                 ST.DATAAREAID = 'DAT'
		                                 AND SPT.CHECKISSALES_TAKE = 1 
		                                 AND ST.SALESGROUP <> 'Zoope'
		                                 AND DATEPART(YEAR, ST.SHIPPINGDATEREQUESTED) = 2014
		
		                                
		
                                          GROUP BY 
                                          ST.SALESNAME,
                                          ST.CUSTACCOUNT,
                                          ST.SALESGROUP,
                                          IT.ITEMGROUPID
									) R 
									WHERE JAN > 0
						  ) X
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

--montagem dinamica da query:
set @query 
      = 'SELECT ST.SALESNAME, ST.CUSTACCOUNT, ST.SALESGROUP' + @colsPivot + ' FROM 
         (
			SELECT Origem, Nome, R.JAN FROM
					(
						 ST.SALESNAME
	                                    ,ST.CUSTACCOUNT
	                                    ,ST.SALESGROUP
	                                    ,IT.ITEMGROUPID
									    ,SUM (CASE WHEN DATEPART(MONTH, ST.ShippingDateRequested) = 1 AND DATEPART(YEAR, ST.ShippingDateRequested) = 2014 THEN 1 ELSE 0 END) AS 'JAN'
										from SALESTABLE ST
											INNER JOIN SALESLINE SL ON ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID 
	                                        INNER JOIN SALESOPERATIONTYPE_BR	SPT	 ON	 ST.DATAAREAID	=	SPT.DATAAREAID AND	ST.OPERATIONID	=	SPT.OPERATIONID 
	                                        INNER JOIN INVENTTABLE IT ON SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID 
										 
										WHERE 
		                                 ST.DATAAREAID = 'DAT'
		                                 AND SPT.CHECKISSALES_TAKE = 1 
		                                 AND ST.SALESGROUP <> 'Zoope'
		                                 AND DATEPART(YEAR, ST.SHIPPINGDATEREQUESTED) = 2014
		
		                                
		
                                          GROUP BY 
                                          ST.SALESNAME,
                                          ST.CUSTACCOUNT,
                                          ST.SALESGROUP,
                                          IT.ITEMGROUPID
					) R 
					WHERE JAN > 0
			) X
         pivot 
         (
            sum(JAN)
            for Nome in (' + @cols + ')
         ) p '


execute(@query)

quinta-feira, 3 de julho de 2014 17:06

0

Entrar para Votar

Bom dia Fabio;

Caso eu saiba a quantidade de colunas, ou seja, estou pensando em deixar Fixo, quando aparecer um grupo novo eu acrescento manualmente.

Ou então, dando um Select na tabela de grupos, como Ficaria ?

Assim, o código ficou grande e complicado para eu entender o pivot.

Obrigado

sexta-feira, 4 de julho de 2014 12:34

Responder

|

Citação
0

Entrar para Votar

Deleted

segunda-feira, 7 de julho de 2014 22:13

Responder

|

Citação

Entrar para Votar

Olá José, agradeço a atenção.

O campo esta como datetime

O código retorna sim, uma linha para cada cliente- grupo_item - mes_ano

AIRTON SILVA - ME	0000198	OLIVEIRA	Modulos	5	152.341.000.000.000
ALDO - ME	C003784	Kstc	PROD KST	5	1.989.000.000.000.000
AJAPAN COM. LTDA	C002818	NISS	Acessorios	1	1.309.580.000.000.000
AJAPAN COM. LTDA	C002818	NISS	Alarmes	1	469.860.000.000.000
AULOS LTDA	002590	NISS	Acessorios	1	4.970.250.000.000.000
ACULOS LTDA	0025908	NISS	Acessorios	4	5.997.700.000.000.000
ASMOTIVOS LTDA - ME	C003795	RR	Alarmes	5	1.951.385.800.000.000

terça-feira, 8 de julho de 2014 12:26

0

Entrar para Votar

Deleted

terça-feira, 8 de julho de 2014 18:18

Responder

|

Citação
0

Entrar para Votar

Bom dia Jose Diz, ao colar o resultado, ficaram grandes as colunas, então essa "sujeira" foi feita por mim na hora de responder no fórum, alguns espaços e algumas letras me passaram desapercebidos.

Com relação aos valores eu formato na hora de exibir

Seria apenas 152,34 .

Obrigado

quinta-feira, 10 de julho de 2014 10:37

Responder

|

Citação
0

Entrar para Votar

Deleted

quinta-feira, 10 de julho de 2014 12:50

Responder

|

Citação
0

Entrar para Votar

Agradeço a atenção, ao executar o comando, o retorno foi.

(12 row(s) affected)
[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Acessorios-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-jan],[Alarmes-ja

quinta-feira, 10 de julho de 2014 13:44

Responder

|

Citação
0

Entrar para Votar

Deleted

quinta-feira, 10 de julho de 2014 13:48

Responder

|

Citação

Entrar para Votar

Se me permite, alterei para buscar na tabela de grupos de itens

-- código 2 v3 ( Adicionei Inventitemgroup )
-- monta títulos das colunas de valores mensais de ITEMGROUPID
declare @Meses table (Mes char(3));
INSERT into @Meses values 
    ('jan'),('fev'),('mar'),('abr'),('mai'),('jun'),
    ('jul'),('ago'),('set'),('out'),('nov'),('dez');
declare @MesItem varchar(2000);
set @MesItem= space(0);
SELECT @MesItem+= '[' + I.ITEMGROUPID + '-' + M.Mes + '],'
  from @Meses as M cross join
       (SELECT distinct ITEMGROUPID from INVENTITEMGROUP) as I;

set @MesItem= Left(@MesItem, DataLength(@MesItem) -1);
print @MesItem;

(12 row(s) affected)
[Acessorios-jan],[AGUA-jan],[Alarmes-jan],[ASSESSORIA-jan],[ASSISTENCI-jan],[ATIVO-jan],[ATIVO 01-jan],[ATIVO 02-jan],[ATIVO 03-jan],[ATIVO 04-jan],[ATIVO 05-jan]

quinta-feira, 10 de julho de 2014 13:57

0

Entrar para Votar

Deleted

quinta-feira, 10 de julho de 2014 14:04

Responder

|

Citação
0

Entrar para Votar

Perfeito, uma linha para cada grupo, tem grupos que não são de venda, mas depois eu tiro da relação.

quinta-feira, 10 de julho de 2014 14:06

Responder

|

Citação
0

Entrar para Votar

Deleted

quinta-feira, 10 de julho de 2014 14:53

Responder

|

Citação
0

Entrar para Votar

Deleted

quinta-feira, 10 de julho de 2014 15:05

Responder

|

Citação

Entrar para Votar

Alterei apenas um ponto pois deu erro de sintase

-- código 2 v5
-- monta títulos das colunas de valores mensais de ITEMGROUPID
declare @Meses table (Mes char(3));
INSERT into @Meses values 
    ('jan'),('fev'),('mar'),('abr'),('mai'),('jun'),
    ('jul'),('ago'),('set'),('out'),('nov'),('dez');
declare @MesItem varchar(2000);
set @MesItem= space(0);
SELECT @MesItem+= '[' + I.ITEMGROUPID + '-' + M.Mes + '],'
  from @Meses as M cross join
       (SELECT lower(ITEMGROUPID) as ITEMGROUPID
          from INVENTITEMGROUP
          where DATAAREAID = 'dat') as I;

quinta-feira, 10 de julho de 2014 15:10

Entrar para Votar

- Diminui a quantidade de grupo de itens para testar

- Só não consegui colocar mais uma condição dentro do set @SQL :

-- código 3
declare @ANO int = 2014;
declare @SQL varchar(max);

-- monta títulos das colunas de valores mensais de ITEMGROUPID
declare @Meses table (Mes char(3));
INSERT into @Meses values 
    ('jan'),('fev'),('mar'),('abr'),('mai'),('jun'),
    ('jul'),('ago'),('set'),('out'),('nov'),('dez');
declare @MesItem varchar(2000);
set @MesItem= space(0);

SELECT @MesItem+= '[' + I.ITEMGROUPID + '-' + M.Mes + '],'
  from @Meses as M cross join
       (SELECT lower(ITEMGROUPID) as ITEMGROUPID
          from INVENTITEMGROUP
          where DATAAREAID = 'DAT' AND ITEMGROUPID IN ('ACESS. OL', 'Alarmes', 'ALARMES OL', 'FASTCONNEC')) as I;

set @MesItem= Left(@MesItem, DataLength(@MesItem) -1);
--print @MesItem

-- monta o comando SQL com o código da consulta
set @SQL= 
'declare @Data1 date, @Data2 date; ' +
'set @Data1= Convert(date, ''1/1/' + Cast(@ANO as char(4)) + ''', 103); ' +
'set @Data2= Convert(date, ''1/1/' + Cast(@ANO+1 as char(4)) + ''', 103); ' +
'with VendaAnual as (' +
'SELECT ' +
       'ST.SALESNAME '+
      ',ST.CUSTACCOUNT '+
      ',ST.SALESGROUP ' +
      ',(Lower(IT.ITEMGROUPID) + ''-'' + Left( Lower( DateName(month, ST.SHIPPINGDATEREQUESTED) ) ,3)) as MesItem ' +
      ',SL.LINEAMOUNT ' +
   'FROM ' +
       'SALESTABLE ST  inner join ' +
       'SALESLINE SL on ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID  inner join ' +
       'SALESOPERATIONTYPE_BR SPT on ST.DATAAREAID = SPT.DATAAREAID and	ST.OPERATIONID = SPT.OPERATIONID  inner join ' +
       'INVENTTABLE IT on SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID ' +
   'WHERE ST.SHIPPINGDATEREQUESTED >= @Data1 ' +
         'and ST.SHIPPINGDATEREQUESTED < @Data2 ' +
         --'and ST.DATAAREAID =  ' +
') ' +
'SELECT SALESNAME, CUSTACCOUNT, SALESGROUP, ' + @MesItem +
  'from VendaAnual ' +
       'pivot (sum(LINEAMOUNT) for MesItem in (' + @MesItem + ')) as P';

--
--print @SQL
 execute (@SQL);

Marcado como Resposta William_droops quinta-feira, 17 de julho de 2014 12:27

quinta-feira, 10 de julho de 2014 16:08

0

Entrar para Votar

Deleted

quinta-feira, 10 de julho de 2014 20:05

Responder

|

Citação
0

Entrar para Votar

Jose Diz, na base de teste atendeu, acredito que é isto mesmo a solução que preciso, vou testar de uma maneira completa, mas já agradeço a atenção e interesse.

Atendendo e não tendo mais duvidas finalizo o tópico.

quinta-feira, 10 de julho de 2014 20:21

Responder

|

Citação
0

Entrar para Votar

Deleted

quinta-feira, 10 de julho de 2014 20:26

Responder

|

Citação
0

Entrar para Votar

Teria que montar o agrupamento por colunas no reporting Service, correto ?

quinta-feira, 10 de julho de 2014 20:28

Responder

|

Citação

Entrar para Votar

Boa tarde Jose Diz, tudo bem ?

A lógica funcionou perfeitamente.

Com poucos produtos o resultado é satisfatório, porém quando coloco para buscar na tabela de Grupo de Itens, acredito que deve ter um Limite e não monta o select completo.
Ao printar no final ele corta as opções.

declare @Data1 date, @Data2 date; set @Data1= Convert(date, '1/1/2014', 103); set @Data2= Convert(date, '1/1/2015', 103); with VendaAnual as (SELECT ST.SALESNAME ,ST.CUSTACCOUNT ,ST.SALESGROUP ,(Lower(IT.ITEMGROUPID) + '-' + Left( Lower( DateName(month, ST.SHIPPINGDATEREQUESTED) ) ,3)) as MesItem ,SL.LINEAMOUNT FROM SALESTABLE ST  inner join SALESLINE SL on ST.DATAAREAID = SL.DATAAREAID AND ST.SALESID = SL.SALESID  inner join SALESOPERATIONTYPE_BR SPT on ST.DATAAREAID = SPT.DATAAREAID and	ST.OPERATIONID = SPT.OPERATIONID  inner join INVENTTABLE IT on SL.DATAAREAID = IT.DATAAREAID AND SL.ITEMID = IT.ITEMID WHERE ST.SHIPPINGDATEREQUESTED >= @Data1 and ST.SHIPPINGDATEREQUESTED < @Data2 and ST.DATAAREAID = 'dat' and IT.ITEMGROUPID not in  ('AGUA', 'ASSESSORIA', 'ASSISTENCI','ATIVO','BENS DE PE','BRINDES E', 'COMBUSTIVE','COMUNICACA','CONFRATERN', 'DESMP','DESP KASAT','DESPA','DESPCOMISS','Despesas','ELERGIA EL','EST MANUTE','ESTACIONAM','FARMACIA', 'FERRAMENTA', 'FRETE','GRAFICA', 'HIGIENE E', 'IMPRESSOS', 'INDUSTRIAL', 'INTERNET','LANCHES E', 'MANUT. E C', 'MANUT-MAQ','MAOOBRA', 'MAT AUX CO', 'MAT.TERCEI','MP','MP KASATEC', 'MP OLIMPUS','NFC','OUT ALUGUE', 'POSTAIS', 'PROMOÇÕES','PROPAGANDA','SERV PREST', 'SERVIÇO', 'SERVS.P.JU','SUCATA','TREINAMENT','VALE REFEI','VALE TRANS', 'VEICULO', 'VIAGENS E') ) SELECT SALESNAME, CUSTACCOUNT, SALESGROUP, [ACESSORIOS OLIMPUS-JAN],[ACESSÓRIOS-JAN],[ALARMES MOTO OLIMPUS-JAN],[ALARMES-JAN],[ALARMES OLIMPUS-JAN],[ANTENAS OLIMPUS-JAN],[COMFORT-JAN],[DSH OLIMPUS-JAN],[MÓDULOS FAST CONNECTION-JAN],[MÓDULOS-JAN],[MÓDULOS KT-JAN],[OUTROS OLIMPUS-JAN],[PRODUTO ACABADO KASATEC-JAN],[REVENDA KASATEC-JAN],[SAFELIFT-JAN],[SUBPRODUTO-JAN],[TRAVAS-JAN],[ACESSORIOS OLIMPUS-FEV],[ACESSÓRIOS-FEV],[ALARMES MOTO OLIMPUS-FEV],[ALARMES-FEV],[ALARMES OLIMPUS-FEV],[ANTENAS OLIMPUS-FEV],[COMFORT-FEV],[DSH OLIMPUS-FEV],[MÓDULOS FAST CONNECTION-FEV],[MÓDULOS-FEV],[MÓDULOS KT-FEV],[OUTROS OLIMPUS-FEV],[PRODUTO ACABADO KASATEC-FEV],[REVENDA KASATEC-FEV],[SAFELIFT-FEV],[SUBPRODUTO-FEV],[TRAVAS-FEV],[ACESSORIOS OLIMPUS-MAR],[ACESSÓRIOS-MAR],[ALARMES MOTO OLIMPUS-MAR],[ALARMES-MAR],[ALARMES OLIMPUS-MAR],[ANTENAS OLIMPUS-MAR],[COMFORT-MAR],[DSH OLIMPUS-MAR],[MÓDULOS FAST CONNECTION-MAR],[MÓDULOS-MAR],[MÓDULOS KT-MAR],[OUTROS OLIMPUS-MAR],[PRODUTO ACABADO KASATEC-MAR],[REVENDA KASATEC-MAR],[SAFELIFT-MAR],[SUBPRODUTO-MAR],[TRAVAS-MAR],[ACESSORIOS OLIMPUS-ABR],[ACESSÓRIOS-ABR],[ALARMES MOTO OLIMPUS-ABR],[ALARMES-ABR],[ALARMES OLIMPUS-ABR],[ANTENAS OLIMPUS-ABR],[COMFORT-ABR],[DSH OLIMPUS-ABR],[MÓDULOS FAST CONNECTION-ABR],[MÓDULOS-ABR],[MÓDULOS KT-ABR],[OUTROS OLIMPUS-ABR],[PRODUTO ACABADO KASATEC-ABR],[REVENDA KASATEC-ABR],[SAFELIFT-ABR],[SUBPRODUTO-ABR],[TRAVAS-ABR],[ACESSORIOS OLIMPUS-MAI],[ACESSÓRIOS-MAI],[ALARMES MOTO OLIMPUS-MAI],[ALARMES-MAI],[ALARMES OLIMPUS-MAI],[ANTENAS OLIMPUS-MAI],[COMFORT-MAI],[DSH OLIMPUS-MAI],[MÓDULOS FAST CONNECTION-MAI],[MÓDULOS-MAI],[MÓDULOS KT-MAI],[OUTROS OLIMPUS-MAI],[PRODUTO ACABADO KASATEC-MAI],[REVENDA KASATEC-MAI],[SAFELIFT-MAI],[SUBPRODUTO-MAI],[TRAVAS-MAI],[ACESSORIOS OLIMPUS-JUN],[ACESSÓRIOS-JUN],[ALARMES MOTO OLIMPUS-JUN],[ALARMES-JUN],[ALARMES OLIMPUS-JUN],[ANTENAS OLIMPUS-JUN],[COMFORT-JUN],[DSH OLIMPUS-JUN],[MÓDULOS FAST CONNECTION-JUN],[MÓDULOS-JUN],[MÓDULOS KT-JUN],[OUTROS OLIMPUS-JUN],[PRODUTO ACABADO KASATEC-JUN],[REVENDA KASATEC-JUN],[SAFELIFT-JUN],[from VendaAnual pivot (sum(LINEAMOUNT) for MesItem in ([ACESSORIOS OLIMPUS-JAN],[ACESSÓRIOS-JAN],[ALARMES MOTO OLIMPUS-JAN],[ALARMES-JAN],[ALARMES OLIMPUS-JAN],[ANTENAS OLIMPUS-JAN],[COMFORT-JAN],[DSH OLIMPUS-JAN],[MÓDULOS FAST CONNECTION-JAN],[MÓDULOS-JAN],[MÓDULOS KT-JAN],[OUTROS OLIMPUS-JAN],[PRODUTO ACABADO KASATEC-JAN],[REVENDA KASATEC-JAN],[SAFELIFT-JAN],[SUBPRODUTO-JAN],[TRAVAS-JAN],[ACESSORIOS OLIMPUS-FEV],[ACESSÓRIOS-FEV],[ALARMES MOTO OLIMPUS-FEV],[ALARMES-FEV],[ALARMES OLIMPUS-FEV],[ANTENAS OLIMPUS-FEV],[COMFORT-FEV],[DSH OLIMPUS-FEV],[MÓDULOS FAST CONNECTION-FEV],[MÓDULOS-FEV],[MÓDULOS KT-FEV],[OUTROS OLIMPUS-FEV],[PRODUTO ACABADO KASATEC-FEV],[REVENDA KASATEC-FEV],[SAFELIFT-FEV],[SUBPRODUTO-FEV],[TRAVAS-FEV],[ACESSORIOS OLIMPUS-MAR],[ACESSÓRIOS-MAR],[ALARMES MOTO OLIMPUS-MAR],[ALARMES-MAR],[ALARMES OLIMPUS-MAR],[ANTENAS OLIMPUS-MAR],[COMFORT-MAR],[DSH OLIMPUS-MAR],[MÓDULOS FAST CONNECTION-MAR],[MÓDULOS-MAR],[MÓDULOS KT-MAR],[OUTROS OLIMPUS-MAR],[PRODUTO ACABADO KASATEC-MAR],[REVENDA KASATEC-MAR],[SAFELIFT-MAR],[SUBPRODUTO-MAR],[TRAVAS-MAR],[ACESSORIOS OLIMPUS-ABR],[ACESSÓRIOS-ABR],[ALARMES MOTO OLIMPUS-ABR],[ALARMES-ABR],[ALARMES OLIMPUS-ABR],[ANTENAS OLIMPUS-ABR],[COMFORT-ABR],[DSH OLIMPUS-ABR],[MÓDULOS FAST CONNECTION-ABR],[MÓDULOS-ABR],[MÓDULOS KT-ABR],[OUTROS OLIMPUS-ABR],[PRODUTO ACABADO KASATEC-ABR],[REVENDA KASATEC-ABR],[SAFELIFT-ABR],[SUBPRODUTO-ABR],[TRAVAS-ABR],[ACESSORIOS OLIMPUS-MAI],[ACESSÓRIOS-MAI],[ALARMES MOTO OLIMPUS-MAI],[ALARMES-MAI],[ALARMES OLIMPUS-MAI],[ANTENAS OLIMPUS-MAI],[COMFORT-MAI],[DSH OLIMPUS-MAI],[MÓDULOS FAST CONNECTION-MAI],[MÓDULOS-MAI],[MÓDULOS KT-MAI],[OUTROS OLIMPUS-MAI],[PRODUTO ACABADO KASATEC-MAI],[REVENDA KASATEC-MAI],[SAFELIFT-MAI],[SUBPRODUTO-MAI],[TRAVAS-MAI],[ACESSORIOS OLIMPUS-JUN],[ACESSÓRIOS-JUN],[ALARMES MOTO OLIMPUS-JUN],[ALARMES-JUN],[ALARMES OLIMPUS-JUN],[ANTENAS OLIMPUS-JUN],[COMFORT-JUN],[DSH OLIMPUS-JUN],[MÓDULOS FAST CONNECTION-JUN],[MÓDULOS-JUN],[MÓDULOS KT-JUN],[OUTROS OLIMPUS-JUN],[PRODUTO ACABADO KASATEC-JUN],[REVENDA KASATEC-JUN],[SAFELIFT-JUN],[)) as P

Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ')) as P'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near ')) as P'

Teria como eu fazer de uma maneira dinâmica de 5 meses, 2 meses anterior , mes atual, 2 meses posterior.

quarta-feira, 16 de julho de 2014 18:03

0

Entrar para Votar

Deleted

quarta-feira, 16 de julho de 2014 19:55

Responder

|

Citação
0

Entrar para Votar

Perfeito, somente com Max, funcionou.

Obrigado

quarta-feira, 16 de julho de 2014 20:04

Responder

|

Citação

Usuário com melhor resposta

Agrupar Clientes em uma unica linha, colocar como colunas os tipos de produtos

Pergunta

Respostas

Todas as Respostas