Sumarização no Union

Question

Olá, 

Montei uma consulta para unificar as vendas de todas as empresas de um determinado vendedor usando a função UNION.

No entanto, a consulta me retorna Linhas dos mesmos ANOS, ou seja, 2013 e 2013. Eu sei que são as vendas feitas em lojas diferentes.

O que eu preciso é que some e uma unica linha do ANO as vendas das tres loja conforme o Mês.

abaixo o select:

declare @Hoje date,
@Data_de date,
@Data_até date;

 --define período de emissão

set @Hoje = Current_Timestamp;

set @Data_até = DateAdd (day, -day (@Hoje), @Hoje);

set @Data_de = DateAdd ( month, -24, (DateAdd ( day, ( -day (@Data_até) +1), @Data_até) ) );

SELECT fun_nm as Vendedor, [ANO], ISNULL([1],0) AS JAN, ISNULL([2],0) AS FEV, ISNULL ([3],0) AS MAR, ISNULL ([4],0) as ABR, ISNULL ([5],0) AS MAI, ISNULL([6],0) AS JUN, ISNULL([7],0) AS JUL, ISNULL([8],0) AS AGO, ISNULL([9],0) AS [SET], ISNULL ([10],0) AS OUT, ISNULL ([11],0) AS NOV, ISNULL ([12],0) AS DEZ 

FROM 

( select fun_nm, nf_vlliquido, YEAR(nf_dtemis) as Ano, MONTH(nf_dtemis) as Mês

FROM IVECO_BARUERI..ger_nfs bar join tab_me nat on bar.me_cd=nat.me_cd join tab_fun func on func.fun_cd=bar.fun_vend

where bar.nf_dtcanc IS NULL 
and bar.nf_serie = 'u'
and NAT.me_tpmov = 've' 
and est_cd = 'pe' 
and FUNC.fun_tp = 'vpa'
and func.fun_cd= 349
and bar.nf_dtemis between @Data_de and @Data_até) P
PIVOT
(
 
sum(nf_vlliquido) FOR Mês IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
 ) AS PVT
 
 
UNION

SELECT fun_nm as Vendedor,  [ANO], ISNULL([1],0) AS JAN, ISNULL([2],0) AS FEV, ISNULL ([3],0) AS MAR, ISNULL ([4],0) as ABR, ISNULL ([5],0) AS MAI, ISNULL([6],0) AS JUN, ISNULL([7],0) AS JUL, ISNULL([8],0) AS AGO, ISNULL([9],0) AS [SET], ISNULL ([10],0) AS OUT, ISNULL ([11],0) AS NOV, ISNULL ([12],0) AS DEZ 


FROM 

( select fun_nm, nf_vlliquido, YEAR(nf_dtemis) as Ano, MONTH(nf_dtemis) as Mês

FROM IVECO_SBC..ger_nfs sbc join tab_me nat on sbc.me_cd=nat.me_cd join tab_fun func on func.fun_cd=sbc.fun_vend

where sbc.nf_dtcanc IS NULL 
and sbc.nf_serie = 'u'
and NAT.me_tpmov = 've' 
and est_cd = 'pe' 
and FUNC.fun_tp = 'vpa'
and func.fun_cd= 349
and sbc.nf_dtemis between @Data_de and @Data_até) P
PIVOT
(
 
sum(nf_vlliquido) FOR Mês IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
 ) AS PVT
 
UNION

--*********************************************************************************************************************************************************************

SELECT fun_nm as Vendedor, [ANO], ISNULL([1],0) AS JAN, ISNULL([2],0) AS FEV, ISNULL ([3],0) AS MAR, ISNULL ([4],0) as ABR, ISNULL ([5],0) AS MAI, ISNULL([6],0) AS JUN, ISNULL([7],0) AS JUL, ISNULL([8],0) AS AGO, ISNULL([9],0) AS [SET], ISNULL ([10],0) AS OUT, ISNULL ([11],0) AS NOV, ISNULL ([12],0) AS DEZ 


FROM 

( select fun_nm, nf_vlliquido, YEAR(nf_dtemis) as Ano, MONTH(nf_dtemis) as Mês

FROM IVECO_SANTOS..ger_nfs sts join tab_me nat on sts.me_cd=nat.me_cd join tab_fun func on func.fun_cd=sts.fun_vend

where sts.nf_dtcanc IS NULL 
and sts.nf_serie = 'u'
and NAT.me_tpmov = 've' 
and est_cd = 'pe' 
and FUNC.fun_tp = 'vpa'
and func.fun_cd= 349
and sts.nf_dtemis between @Data_de and @Data_até) P
PIVOT
(
 
sum(nf_vlliquido) FOR Mês IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
 ) AS PVT
 
group by fun_nm, Ano,[1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12]
order by Vendedor , Ano 

RESULTADO

Vendedor	ANO	JAN	FEV	MAR	ABR	MAI	JUN	JUL	AGO	SET	OUT	NOV	DEZ
KLEBER DE ABREU CARDOSO	2013	0	0	0	0	0	0	0	1150	0	1968,78	650	0
KLEBER DE ABREU CARDOSO	2013	0	0	0	0	48990,44	54905,32	82224,4	66054,51	90648,41	69938,56	88593,88	64813,41
KLEBER DE ABREU CARDOSO	2014	0	315,79	473,25	224	1064,2	2474,39	1837,95	0	0	0	0	0
KLEBER DE ABREU CARDOSO	2014	70030,8	9511,76	71375,2	76498,82	74810,33	88089,04	103674,12	84242,76	69279,12	88701,48	76604,24	81650,35
KLEBER DE ABREU CARDOSO	2015	120648,29	59808,77	55240,46	83375,53	106413,31	0	0	0	0	0	0	0

O QUE PRECISO É

Vendedor	ANO	JAN	FEV	MAR	ABR	MAI	JUN	JUL	AGO	SET	OUT	NOV	DEZ
KLEBER DE ABREU CARDOSO	2013	0	0	0	0	48990,44	54905,32	82224,4	67204,51	90648,41	71907,34	89243,88	64813,41
KLEBER DE ABREU CARDOSO	2014	70030,8	9827,55	71848,45	76722,82	75874,53	90563,43	105512,07	84242,76	69279,12	88701,48	76604,24	81650,35
KLEBER DE ABREU CARDOSO	2015	120648,29	59808,77	55240,46	83375,53	106413,31	0	0	0	0	0	0	0

Answer 1

Bom dia,

Experimente utilizar o Union All na origem do Pivot mais ou menos dessa forma:

SELECT fun_nm as Vendedor, [ANO], ISNULL([1],0) AS JAN, ISNULL([2],0) AS FEV, ISNULL ([3],0) AS MAR, ISNULL ([4],0) as ABR, ISNULL ([5],0) AS MAI, ISNULL([6],0) AS JUN, ISNULL([7],0) AS JUL, ISNULL([8],0) AS AGO, ISNULL([9],0) AS [SET], ISNULL ([10],0) AS OUT, ISNULL ([11],0) AS NOV, ISNULL ([12],0) AS DEZ 

FROM 

( 

select fun_nm, nf_vlliquido, YEAR(nf_dtemis) as Ano, MONTH(nf_dtemis) as Mês
FROM IVECO_BARUERI..ger_nfs bar join tab_me nat on bar.me_cd=nat.me_cd join tab_fun func on func.fun_cd=bar.fun_vend
where bar.nf_dtcanc IS NULL 
and bar.nf_serie = 'u'
and NAT.me_tpmov = 've' 
and est_cd = 'pe' 
and FUNC.fun_tp = 'vpa'
and func.fun_cd= 349
and bar.nf_dtemis between @Data_de and @Data_até

UNION ALL

select fun_nm, nf_vlliquido, YEAR(nf_dtemis) as Ano, MONTH(nf_dtemis) as Mês
FROM IVECO_SBC..ger_nfs sbc join tab_me nat on sbc.me_cd=nat.me_cd join tab_fun func on func.fun_cd=sbc.fun_vend
where sbc.nf_dtcanc IS NULL 
and sbc.nf_serie = 'u'
and NAT.me_tpmov = 've' 
and est_cd = 'pe' 
and FUNC.fun_tp = 'vpa'
and func.fun_cd= 349
and sbc.nf_dtemis between @Data_de and @Data_até

UNION ALL

select fun_nm, nf_vlliquido, YEAR(nf_dtemis) as Ano, MONTH(nf_dtemis) as Mês
FROM IVECO_SANTOS..ger_nfs sts join tab_me nat on sts.me_cd=nat.me_cd join tab_fun func on func.fun_cd=sts.fun_vend
where sts.nf_dtcanc IS NULL 
and sts.nf_serie = 'u'
and NAT.me_tpmov = 've' 
and est_cd = 'pe' 
and FUNC.fun_tp = 'vpa'
and func.fun_cd= 349
and sts.nf_dtemis between @Data_de and @Data_até

) P
PIVOT
(
sum(nf_vlliquido) FOR Mês IN ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) AS PVT

order by Vendedor , Ano 

Espero que ajude.

---

Assinatura: http://www.imoveisemexposicao.com.br

Answer 2

Valeuu!!!

eu tinha usado já o Union ALL e não tinha dado certo. Não vi ainda que voce mudou mas deu certo rsrs

Obrigadão.

Answer 3

Putz

agora vi rsrs

Valeu tão facil não tinha pensado assim ... rsrs

abraços