none
Agrupamentos por linhas / colunas RRS feed

  • Pergunta

  • Boa Tarde!

    Gostaria de realizar o agrupamento de linhas e colunas conforme abaixo;

    - agrupamento por linha (soma quantidade vendida)

    - agrupamento por coluna (separar por mês as quantidades vendidas) pelo campo (Data da Venda)

    Ex. Código / Descrição / Fabricante / Grupo de Itens / Preço Venda NF / Janeiro 2017 / Fevereiro 2017

        001271 / Luva       / Medix        / Descartável      /   22.00            /  68               /     56

    terça-feira, 29 de agosto de 2017 21:57

Respostas

  • Bulcão,

    Já pensou em utilizar Grouping Sets?

    Veja se este exemplo te ajuda:

    -- Desabilitando a contagem de linhas --
    Set NoCount On;
    
    -- Verificando se a Tabela OrdemProducao já esta cadastrada --
    If OBJECT_ID('dbo.OrdemProducao','U') Is Not Null
     Drop Table dbo.OrdemProducao
    Go
    
    -- Criando a Tabela OrdemProducao --
    Create Table OrdemProducao
     (NumProducao Int Not Null,
      Data Date Not Null,
      CodFuncionario Int Not Null,
      Setor Varchar(2) Not Null, 
      Quantidade Int Not Null         
     ) 
    Go
    
    -- Adicionando Chave Primária na Tabela OrdemProducao --
    Alter Table OrdemProducao
     Add Constraint [PK_OrdemProducao_NumProducao] Primary Key Clustered (NumProducao)
    Go 
    -- Inserindo dados na Tabela OrdemProducao --
    Insert Into OrdemProducao (NumProducao, Data, CodFuncionario, Setor, Quantidade)
    Values 
    (1000, '20091001',1,'A',20),
    (1001, '20091002',2,'B',30),
    (1002, '20091003',3,'D',15),
    (1003, '20091005',2,'D',12),
    (2000, '20100210',4,'C',40),
    (2001, '20100211',4,'C',35),
    (2002, '20100312',2,'A',22),
    (2004, '20100414',2,'B',12),			  
    (3005, '20110601',1,'C',50),
    (3006, '20110602',3,'B',60)
    
    Insert Into OrdemProducao (NumProducao, Data, CodFuncionario, Setor, Quantidade)
    Values 
    (10000, '20091001',1,'A',20),
    (10001, '20091002',2,'B',30),
    (10002, '20091003',3,'D',15),
    (10003, '20091005',2,'D',12),
    (20000, '20100210',4,'C',40),
    (20001, '20100211',4,'C',35),
    (20002, '20100312',2,'A',22),
    (20004, '20100414',2,'B',12),			  
    (30005, '20110601',1,'C',50),
    (30006, '20110602',3,'B',60)
    
    -- Aplicando o Grouping Sets (Setor, CodFuncionario e AnoProducao) na Tabela OrdemProducao --
    Select Setor, CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
    from dbo.OrdemProducao
    Group By GROUPING Sets
    (
     (Setor, CodFuncionario, YEAR(DATA)) 
    )
    Go
    
    -- Aplicando o Grouping Sets (Setor, CodFuncionario e AnoProducao + AnoProd) na Tabela OrdemProducao --
    Select Setor, CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
    from dbo.OrdemProducao
    Group By GROUPING Sets
    (
     (Setor, CodFuncionario, YEAR(DATA)),
     (YEAR(Data))
    )
    Go
    
    -- Simultando o Grouping Sets utilizando Union All --
    Select Setor, CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
    from dbo.OrdemProducao
    Group By Setor, CodFuncionario, YEAR(Data)
    
    Union All
    
    Select Null As Setor, Null As CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
    from dbo.OrdemProducao
    Group By YEAR(Data)


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 30 de agosto de 2017 12:17

Todas as Respostas

  • terça-feira, 29 de agosto de 2017 21:59
  • Segue abaixo código;

    SELECT
    T0.[ItemCode] AS 'Código',
    T0.[ItemName] AS 'Descrição',
    T3.[FirmName] AS 'Fabricante',
    T4.[ItmsGrpNam] AS 'Grupo de Itens',
    --T0.[OnOrder] AS 'Pedidos em Trânsito',
    --T1.[OnHand] AS 'Estoque Atual',
    --T1.[AvgPrice] AS 'Preço Unitário de Custo',
    --T2.[Price] AS 'Preço de Venda - Base',
    T5.[Quantity] AS 'Quantidade Vendida',
    T5.[Price] AS 'Preço de Venda - NF',
    --T6.[Serial] AS [Numero NF],
    T6.[DocDate] as 'Data da Venda'
    --SUM ((T2.[Price] - T1.[AvgPrice])/T2.[Price]) AS 'Margem Vendas Vlr SAP',
    --SUM ((T5.[Price] - T1.[AvgPrice])/(NULLIF(T5.[Price],0))) AS 'Margem Vendas NF',
    --ROUND((ISNULL(ic.ICMS,0)),2) as 'ICMS',
    --ROUND((ISNULL(ipi.IPI,0)),2) as 'IPI',
    --ROUND((ISNULL(pis.PIS,0)),2) as 'PIS',
    --ROUND((ISNULL(cofins.COFINS,0)),2) as 'COFINS',
    --ROUND((ISNULL(st.ST,0)),2) as 'ICMS-ST',
    --(ROUND((ISNULL(ic.ICMS,0)),2) - ROUND((ISNULL(ipi.IPI,0)),2) - ROUND((ISNULL(pis.PIS,0)),2) - ROUND((ISNULL(cofins.COFINS,0)),2) - ROUND((ISNULL(st.ST,0)),2)) as 'Total Impostos',
    --ISNULL(T5.LineTotal,0)-ISNULL(T5.StockValue,0)-(ROUND((ISNULL(ic.ICMS,0)),2)-ROUND((ISNULL(ipi.IPI,0)),2)-ROUND((ISNULL(pis.PIS,0)),2)-ROUND((ISNULL(cofins.COFINS,0)),2)-ROUND((ISNULL(st.ST,0)),2)) as 'Lucro Líquido',
    --ISNULL(T5.LineTotal,0)-ISNULL(T5.stockvalue,0)-(ROUND((ISNULL(ic.ICMS,0)),2)-ROUND((ISNULL(ipi.IPI,0)),2)-ROUND((ISNULL(pis.PIS,0)),2)-ROUND((ISNULL(cofins.COFINS,0)),2)-ROUND(ISNULL(st.ST,0),2))/NULLIF(T5.LineTotal,0)*100 as 'Lucro Líquido%',
    --(Select Sum(IsNull(M.InQty,0) - IsNull(M.OutQty,0)) From OINM as M where M.DocDate Between '2017-01-31' and '2017-01-31'and M.ItemCode = T0.ItemCode) as 'Janeiro/2017',
    --(Select Sum(IsNull(M.OutQty,0)) From OINM as M where M.DocDate Between '2017-01-01' and '2017-01-31' and M.ItemCode = T0.ItemCode) as 'S_Janeiro/17',
    --(Select Sum(IsNull(M.InQty,0) - IsNull(M.OutQty,0)) From OINM as M where M.DocDate Between '2017-02-28' and '2017-02-28'and M.ItemCode = T0.ItemCode) as 'Fevereiro/2017',
    --(Select Sum(IsNull(M.OutQty,0)) From OINM as M where M.DocDate Between '2017-02-01' and '2017-02-28' and M.ItemCode = T0.ItemCode) as 'S_Fevereiro/17',
    --(Select Sum(IsNull(M.OutQty,0)) From OINM as M where M.DocDate Between '2017-03-01' and '2017-03-31' and M.ItemCode = T0.ItemCode) as 'S_Marco/17'

    FROM OITM T0 INNER JOIN OITW T1
    ON T0.[ItemCode] = T1.[ItemCode]INNER JOIN ITM1 T2
    ON T0.[ItemCode] = T2.[ItemCode]INNER JOIN OMRC T3
    ON T0.[FirmCode] = T3.[FirmCode]INNER JOIN OITB T4
    ON T0.[ItmsGrpCod] = T4.[ItmsGrpCod] INNER JOIN INV1 T5
    ON T0.[ItemCode] = T5.[ItemCode] INNER JOIN OINV T6
    ON T5.[DocEntry] = T6.[DocEntry]

    --impostos de saida
    left join (select t0.docentry,t0.linenum,SUM(t0.TaxSum) as ICMS

    from INV4 t0 where t0.staType in (select a.absid from OSTT a
    inner join onft b on a.NfTaxId = b.absid and code like 'ICMS') group by t0.DocEntry,t0.LineNum)
    ic on ic.DocEntry = T6.DocEntry and ic.linenum = T5.LineNum


    left join (select t0.docentry, t0.LineNum, SUM(t0.TaxSum) as IPI

    from INV4 t0 where t0.staType in (select a.absid from OSTT a
    inner join ONFT b on a.NfTaxId = b.AbsId and code like 'IPI') group by t0.DocEntry,t0.LineNum)
    ipi on ipi.DocEntry = T6.DocEntry and ic.linenum = T5.LineNum


    left join (select t0.docentry, t0.LineNum, SUM(t0.TaxSum) as PIS

    from INV4 t0 where t0.staType in (select a.absid from OSTT a
    inner join ONFT b on a.NfTaxId = b.AbsId and code like 'PIS') group by t0.DocEntry, t0.LineNum)
    pis on pis.DocEntry = T6.DocEntry and pis.LineNum = T5.LineNum


    left join (select t0.docentry, t0.LineNum, SUM(t0.TaxSum) as COFINS

    from INV4 t0 where t0.staType in (select a.absid from OSTT a
    inner join ONFT b on a.NfTaxId = b.AbsId and code like 'COFINS') group by t0.DocEntry, t0.LineNum)
    cofins on cofins.DocEntry = T6.DocEntry and cofins.LineNum = T5.LineNum


    left join (select t0.docentry, t0.LineNum, SUM(t0.TaxSum) as ST

    from INV4 t0 where t0.staType in (select a.absid from OSTT a
    inner join ONFT b on a.NfTaxId = b.AbsId and code like 'ICMS-ST') group by t0.DocEntry, t0.LineNum)
    st on st.DocEntry = T6.DocEntry and st.LineNum = T5.LineNum


    --condições
    WHERE T1.[WhsCode] ='01' AND T0.[validFor]='Y' AND T2.[PriceList]='1' AND T0.[SellItem] = 'Y' AND T2.[Price] > '0'
    and T6.[DocDate] <= '2017-02-28' and T6.DocDate >= '2017-01-01'

    --agrupamentos
    GROUP BY
    T0.[ItemCode],
    T0.[ItemName],
    T1.[WhsCode],
    T0.[validFor],
    T3.[FirmName],
    T0.[BuyUnitMsr],
    T0.[NumInBuy],
    T0.[SalUnitMsr],
    T0.[NumInSale],
    T1.[OnHand],
    T1.[AvgPrice],
    T1.[StockValue],
    T2.[Price],
    T5.[Price],
    T0.[OnOrder],
    T4.[ItmsGrpNam],
    T6.[DocDate],
    T6.[Serial],
    T5.[LineTotal],
    T5.StockValue,
    T5.[Quantity],
    ic.ICMS,
    ipi.IPI,
    pis.PIS,
    cofins.COFINS,
    st.ST

    --ordenação
    ORDER BY T0.[ItemCode], T6.[DocDate]

    terça-feira, 29 de agosto de 2017 21:59
  • Bulcão,

    Já pensou em utilizar Grouping Sets?

    Veja se este exemplo te ajuda:

    -- Desabilitando a contagem de linhas --
    Set NoCount On;
    
    -- Verificando se a Tabela OrdemProducao já esta cadastrada --
    If OBJECT_ID('dbo.OrdemProducao','U') Is Not Null
     Drop Table dbo.OrdemProducao
    Go
    
    -- Criando a Tabela OrdemProducao --
    Create Table OrdemProducao
     (NumProducao Int Not Null,
      Data Date Not Null,
      CodFuncionario Int Not Null,
      Setor Varchar(2) Not Null, 
      Quantidade Int Not Null         
     ) 
    Go
    
    -- Adicionando Chave Primária na Tabela OrdemProducao --
    Alter Table OrdemProducao
     Add Constraint [PK_OrdemProducao_NumProducao] Primary Key Clustered (NumProducao)
    Go 
    -- Inserindo dados na Tabela OrdemProducao --
    Insert Into OrdemProducao (NumProducao, Data, CodFuncionario, Setor, Quantidade)
    Values 
    (1000, '20091001',1,'A',20),
    (1001, '20091002',2,'B',30),
    (1002, '20091003',3,'D',15),
    (1003, '20091005',2,'D',12),
    (2000, '20100210',4,'C',40),
    (2001, '20100211',4,'C',35),
    (2002, '20100312',2,'A',22),
    (2004, '20100414',2,'B',12),			  
    (3005, '20110601',1,'C',50),
    (3006, '20110602',3,'B',60)
    
    Insert Into OrdemProducao (NumProducao, Data, CodFuncionario, Setor, Quantidade)
    Values 
    (10000, '20091001',1,'A',20),
    (10001, '20091002',2,'B',30),
    (10002, '20091003',3,'D',15),
    (10003, '20091005',2,'D',12),
    (20000, '20100210',4,'C',40),
    (20001, '20100211',4,'C',35),
    (20002, '20100312',2,'A',22),
    (20004, '20100414',2,'B',12),			  
    (30005, '20110601',1,'C',50),
    (30006, '20110602',3,'B',60)
    
    -- Aplicando o Grouping Sets (Setor, CodFuncionario e AnoProducao) na Tabela OrdemProducao --
    Select Setor, CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
    from dbo.OrdemProducao
    Group By GROUPING Sets
    (
     (Setor, CodFuncionario, YEAR(DATA)) 
    )
    Go
    
    -- Aplicando o Grouping Sets (Setor, CodFuncionario e AnoProducao + AnoProd) na Tabela OrdemProducao --
    Select Setor, CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
    from dbo.OrdemProducao
    Group By GROUPING Sets
    (
     (Setor, CodFuncionario, YEAR(DATA)),
     (YEAR(Data))
    )
    Go
    
    -- Simultando o Grouping Sets utilizando Union All --
    Select Setor, CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
    from dbo.OrdemProducao
    Group By Setor, CodFuncionario, YEAR(Data)
    
    Union All
    
    Select Null As Setor, Null As CodFuncionario, YEAR(Data) As 'Ano de Produção', SUM(Quantidade) As Soma
    from dbo.OrdemProducao
    Group By YEAR(Data)


    Pedro Antonio Galvao Junior [MVP | MCC | MSTC | MIE | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 30 de agosto de 2017 12:17