none
Sumarização no Union RRS feed

  • Pergunta

  • 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


    quinta-feira, 25 de junho de 2015 13:03

Respostas

  • 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

    • Marcado como Resposta Everton.moreira quinta-feira, 25 de junho de 2015 13:46
    quinta-feira, 25 de junho de 2015 13:37

Todas as Respostas

  • 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

    • Marcado como Resposta Everton.moreira quinta-feira, 25 de junho de 2015 13:46
    quinta-feira, 25 de junho de 2015 13:37
  • 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.

    quinta-feira, 25 de junho de 2015 13:47
  • Putz

    agora vi rsrs

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

    abraços

    quinta-feira, 25 de junho de 2015 13:48