none
Como obter 3 vendas de cada Funcionário por Mês RRS feed

  • Pergunta

  • Bom dia,

    Tenho os seguintes campos em uma tabela:

    Cod_Venda   Nome  Tipo   Data

    Estou tentando criar uma query que retorne as 3 vendas de cada funcionário em uma base do ano 2021, porém as vendas tem ser aleatórias e não pode ser dentro do mesmo mês.

    Exemplo:


    segunda-feira, 20 de setembro de 2021 15:05

Respostas

  • Bom dia,

    Edvaldo, segue uma outra sugestão para testes:

    with CTE_Rank as
    (
        select
            Cod_Venda,
            Nome,
            Tipo,
            Data,
            dense_rank() over
                (partition by Nome 
                 order by Year(Data), Month(Data)) as dr,
            row_number() over
                (partition by Nome, Year(Data), Month(Data)
                 order by Nome) as rn
        from Tabela
    )
    
    select * from CTE_Rank
    where dr <= 3 and rn = 1

    Espero que ajude


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

    • Marcado como Resposta Edvaldo A terça-feira, 21 de setembro de 2021 16:13
    terça-feira, 21 de setembro de 2021 13:00
  • Edvaldo,

    Neste caso para se determinar a 3 vendas devemos levar em consideração a coluna Cod_Venda?

    Pensei em um cenário, não levando em consideração as suas regras de negócio, bem como, as suas questões de dados

    Veja se poderá lhe ajudar:

    -- Criando a Tabela de Vendas --
    Create Table Vendas
    (CodigoVenda Int Primary Key Identity(1,1),
     Nome Varchar(20),
     Tipo Char(3) Default 'QRA',
     Data As (GetDate()+CodigoVenda))
    Go
    
    -- Inserindo Vendas Leonardo --
    Insert Into Vendas (Nome) Values ('Leonardo')
    Go 50
    
    -- Inserindo Vendas Lincoln --
    Insert Into Vendas (Nome) Values ('Lincoln')
    Go 65
    
    
    -- Consultando --
    Select Distinct Nome,
               (Select Top 1 FIRST_VALUE(Data) Over (Partition By Nome Order By NewId()) From Vendas) As Data1,
               Tipo
    From Vendas
    Union
    Select Distinct Nome,
               (Select Top 1 Lead(Data,1) Over (Partition By Nome Order By NewId()) From Vendas) As Data2,
    		   Tipo
    From Vendas
    
    Union
    Select Distinct Nome,
               (Select Top 1 Lead(Data,2) Over (Partition By Nome Order By NewId()) From Vendas) As Data3,
    		   Tipo
    From Vendas
    Go
    

    Espero que possa lhe ajudar.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta Edvaldo A terça-feira, 21 de setembro de 2021 11:21
    terça-feira, 21 de setembro de 2021 01:05
  • Edvaldo,

    Veja se este complemento poderá lhe ajudar:

    -- Utitilizando CTE adicionando a coluna CodigoVenda para consultar as 3 vendas aleatórias de cada vendedor --
    ;With CTEVenda1 (Data)
    As
    (
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data From Vendas
    ),
    CTEVenda2 (Data)
    As
    (
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data From Vendas
    ),
    CTEVenda3 (Data)
    As
    (
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data From Vendas
    )
    Select V.CodigoVenda, V.Nome, V.Tipo, C.Data From CTEVenda1 C Left Join Vendas V
                                                                                                                 On C.Data = V.Data
    Union 
    
    Select V.CodigoVenda, V.Nome, V.Tipo, C.Data From CTEVenda2 C Left Join Vendas V
                                                                                                                 On C.Data = V.Data								
    												
    Union 
    
    Select V.CodigoVenda, V.Nome, V.Tipo, C.Data From CTEVenda3 C Left Join Vendas V
                                                                                                                 On C.Data = V.Data			
    Go	

    Ou este outro exemplo mais simples:

    -- Utilizando uma única CTE aplicando a junção a esquerda --
    ;With CTEVenda (Data)
    As
    (
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data 
    From Vendas
    
    Union
    
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data
    From Vendas
    
    Union 
    
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data
    From Vendas
    )
    Select V.CodigoVenda, V.Nome, V.Tipo, Format(V.Data, 'dd/MM/yyyy') As Data
    From CTEVenda C Left Join Vendas V
                                  On C.Data = V.Data
    Go

    Nos testes observei que em algumas situações não estão sendo retornadas as 3 vendas pois a minha porção de dados é pequena.....


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]




    terça-feira, 21 de setembro de 2021 11:43

Todas as Respostas

  • Edvaldo,

    Neste caso para se determinar a 3 vendas devemos levar em consideração a coluna Cod_Venda?

    Pensei em um cenário, não levando em consideração as suas regras de negócio, bem como, as suas questões de dados

    Veja se poderá lhe ajudar:

    -- Criando a Tabela de Vendas --
    Create Table Vendas
    (CodigoVenda Int Primary Key Identity(1,1),
     Nome Varchar(20),
     Tipo Char(3) Default 'QRA',
     Data As (GetDate()+CodigoVenda))
    Go
    
    -- Inserindo Vendas Leonardo --
    Insert Into Vendas (Nome) Values ('Leonardo')
    Go 50
    
    -- Inserindo Vendas Lincoln --
    Insert Into Vendas (Nome) Values ('Lincoln')
    Go 65
    
    
    -- Consultando --
    Select Distinct Nome,
               (Select Top 1 FIRST_VALUE(Data) Over (Partition By Nome Order By NewId()) From Vendas) As Data1,
               Tipo
    From Vendas
    Union
    Select Distinct Nome,
               (Select Top 1 Lead(Data,1) Over (Partition By Nome Order By NewId()) From Vendas) As Data2,
    		   Tipo
    From Vendas
    
    Union
    Select Distinct Nome,
               (Select Top 1 Lead(Data,2) Over (Partition By Nome Order By NewId()) From Vendas) As Data3,
    		   Tipo
    From Vendas
    Go
    

    Espero que possa lhe ajudar.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    • Marcado como Resposta Edvaldo A terça-feira, 21 de setembro de 2021 11:21
    terça-feira, 21 de setembro de 2021 01:05
  • Obrigado, vou adaptar seu exemplo ao meu caso.
    terça-feira, 21 de setembro de 2021 11:21
  • Edvaldo,

    Veja se este complemento poderá lhe ajudar:

    -- Utitilizando CTE adicionando a coluna CodigoVenda para consultar as 3 vendas aleatórias de cada vendedor --
    ;With CTEVenda1 (Data)
    As
    (
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data From Vendas
    ),
    CTEVenda2 (Data)
    As
    (
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data From Vendas
    ),
    CTEVenda3 (Data)
    As
    (
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data From Vendas
    )
    Select V.CodigoVenda, V.Nome, V.Tipo, C.Data From CTEVenda1 C Left Join Vendas V
                                                                                                                 On C.Data = V.Data
    Union 
    
    Select V.CodigoVenda, V.Nome, V.Tipo, C.Data From CTEVenda2 C Left Join Vendas V
                                                                                                                 On C.Data = V.Data								
    												
    Union 
    
    Select V.CodigoVenda, V.Nome, V.Tipo, C.Data From CTEVenda3 C Left Join Vendas V
                                                                                                                 On C.Data = V.Data			
    Go	

    Ou este outro exemplo mais simples:

    -- Utilizando uma única CTE aplicando a junção a esquerda --
    ;With CTEVenda (Data)
    As
    (
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data 
    From Vendas
    
    Union
    
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data
    From Vendas
    
    Union 
    
    Select Distinct First_Value(Data) Over (Partition By Nome Order By NewId()) As Data
    From Vendas
    )
    Select V.CodigoVenda, V.Nome, V.Tipo, Format(V.Data, 'dd/MM/yyyy') As Data
    From CTEVenda C Left Join Vendas V
                                  On C.Data = V.Data
    Go

    Nos testes observei que em algumas situações não estão sendo retornadas as 3 vendas pois a minha porção de dados é pequena.....


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]




    terça-feira, 21 de setembro de 2021 11:43
  • Bom dia,

    Edvaldo, segue uma outra sugestão para testes:

    with CTE_Rank as
    (
        select
            Cod_Venda,
            Nome,
            Tipo,
            Data,
            dense_rank() over
                (partition by Nome 
                 order by Year(Data), Month(Data)) as dr,
            row_number() over
                (partition by Nome, Year(Data), Month(Data)
                 order by Nome) as rn
        from Tabela
    )
    
    select * from CTE_Rank
    where dr <= 3 and rn = 1

    Espero que ajude


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

    • Marcado como Resposta Edvaldo A terça-feira, 21 de setembro de 2021 16:13
    terça-feira, 21 de setembro de 2021 13:00
  • Obrigado gapimex, ótima solução também.
    terça-feira, 21 de setembro de 2021 16:14
  • Bom dia,

    Edvaldo, segue uma outra sugestão para testes:

    with CTE_Rank as
    (
        select
            Cod_Venda,
            Nome,
            Tipo,
            Data,
            dense_rank() over
                (partition by Nome 
                 order by Year(Data), Month(Data)) as dr,
            row_number() over
                (partition by Nome, Year(Data), Month(Data)
                 order by Nome) as rn
        from Tabela
    )
    
    select * from CTE_Rank
    where dr <= 3 and rn = 1

    Espero que ajude


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

    Gapimex,

    Muito legal sua sugestão. Parabéns.

    Pensando na questão de retornar sempre as vendas de forma aleatória, conforme o Edvaldo solicitou, tomei a liberdade de fazer uma mudança no seu exemplo, justamente para atender esta regra de negócio.

    Veja abaixo:

    ;With CTERank 
    As
    (Select CodigoVenda, Nome, Tipo, Data,
                Dense_Rank() Over (Partition By Nome Order By NewId()) As NumeroVenda
      From Vendas
    )
    Select CodigoVenda, Nome, Tipo, Data, NumeroVenda From CTERank
    Where NumeroVenda <= 3
    Go

    Desta forma, são retornadas as 3 vendas aleatórias, e não precisamos aplicar a questão do filtro relacionado a função Row_Number().

    Mais uma vez sua solução foi extremamente prática, simples e elegante.

    Abraços.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]


    terça-feira, 21 de setembro de 2021 19:20
  • Obrigado Junior.

    Sobre a sua sugestão, não cheguei a testar mas pelo que entendi as vendas não podem estar dentro do mesmo mês, então acho que está faltando atender a esse critério também.

    Abs


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

    terça-feira, 21 de setembro de 2021 21:03
  • Gapimex,

    Sim, concordo com você! Mas acredito que o Edvaldo vai conseguir ajustar.

    Abraços.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | MTAC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados Relacional e Data Warehouse | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    terça-feira, 21 de setembro de 2021 21:16
  • Isso mesmo, já adaptei aqui o código, obrigado a todos.
    quinta-feira, 23 de setembro de 2021 11:56