none
SubSelect RRS feed

  • Pergunta

  • Boa tarde

    Galera!

    Estou fazendo esta query e gostaria de uma ajuda, preciso retornar o resultado da seguinte forma.

    Mês, Ano QuantidadePendete, QuantidadeResolvida 

    WITH CTE_OCR AS 
    		 (SELECT 
    
    	       (CASE WHEN MONTH(OcorData) = '1'   THEN 'Janeiro'  
                     WHEN MONTH(OcorData) = '2'   THEN 'Fevereiro'   
                     WHEN MONTH(OcorData) = '3'   THEN 'Março'   
                     WHEN MONTH(OcorData) = '4'   THEN 'Abril' 
                     WHEN MONTH(OcorData) = '5'   THEN 'Maio'     
                     WHEN MONTH(OcorData) = '6'   THEN 'Junho' 
                     WHEN MONTH(OcorData) = '7'   THEN 'Julho' 
                     WHEN MONTH(OcorData) = '8'   THEN 'Agosto' 
                     WHEN MONTH(OcorData) = '9'   THEN 'Setembro' 
                     WHEN MONTH(OcorData) = '10'  THEN 'Outubro' 
                     WHEN MONTH(OcorData) = '11'  THEN 'Novembro' 
                     WHEN MONTH(OcorData) = '12'  THEN 'Dezembro'  END)    AS 'Mês', 
    					   YEAR(OcorData) AS 'Ano',
                                OcorStat
    
    
    	FROM Ocorrencia
    WHERE 
    	  CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,OcorData, 21), 1, 10)) >='20190101'
    And   CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,OcorData, 21), 1, 10)) <='20190603'
    AND EmpCod = '1'
    AND MotOcorCodEstr like '01.09%'
    
    
    
    GROUP BY MONTH(OcorData), YEAR(OcorData) , OcorStat,  OcorCod
    )
    
    SELECT 
    	  CTE.Mês, 
    	  CTE.Ano, 
    	   CTE.OcorStat   ,
    	   count(CTE.OcorStat)
    	
    	
    	   FROM CTE_OCR CTE
    	   
    	
    	   group by
    	   
    	   	  CTE.Mês, 
    	      CTE.Ano, 
    	      CTE.OcorStat   
    	   
    	 


    segunda-feira, 3 de junho de 2019 15:37

Respostas

  • Boa tarde,

    Leandro, experimente fazer uns testes dessa forma:

    Select 	
        CASE Month(OcorData)
            When '1'   THEN 'Janeiro'  
            WHEN '2'   THEN 'Fevereiro'   
            WHEN '3'   THEN 'Março'   
            WHEN '4'   THEN 'Abril' 
            WHEN '5'   THEN 'Maio'     
            WHEN '6'   THEN 'Junho' 
            WHEN '7'   THEN 'Julho' 
            WHEN '8'   THEN 'Agosto' 
            WHEN '9'   THEN 'Setembro' 
            WHEN '10'  THEN 'Outubro' 
            WHEN '11'  THEN 'Novembro' 
            WHEN '12'  THEN 'Dezembro'  
        END AS 'Mês',
        Year(OcorData) As Ano, 
        Pendente = Count(case when OcorStat = 'Pendente' then 1 end),    
        Resolvido = Count(case when OcorStat = 'Resolvido' then 1 end),    
        Cancelado = Count(case when OcorStat = 'Cancelado' then 1 end)    
    From OCORRENCIA
    WHERE 
        OcorData >= '20190101' AND 
        OcorData <= '20190603'
    Group By 
        Month(OcorData), 
        Year(OcorData)
    

    Espero que ajude


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

    segunda-feira, 3 de junho de 2019 20:03

Todas as Respostas

  • Leandro,

    Quais seriam os critérios para realizar o cálculo destas quantidades?


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

    segunda-feira, 3 de junho de 2019 17:52
  • Boa tarde

    Junior

    Eu preciso saber quanto tenho pendente e resolvida  por ano e mês exemplo abaixo

    segunda-feira, 3 de junho de 2019 18:05
  • Leandro,

    Sim, mas com base em qual coluna ou condição deveremos fazer este agrupamento?


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

    segunda-feira, 3 de junho de 2019 18:07
  • A coluna OcorCod é a coluna chave que pode ser utilizada para saber quantos registros retorna na tabela, 

             
    WITH CTE_OCR AS 
    		 (SELECT 
    
    	       (CASE WHEN MONTH(OcorData) = '1'   THEN 'Janeiro'  
                     WHEN MONTH(OcorData) = '2'   THEN 'Fevereiro'   
                     WHEN MONTH(OcorData) = '3'   THEN 'Março'   
                     WHEN MONTH(OcorData) = '4'   THEN 'Abril' 
                     WHEN MONTH(OcorData) = '5'   THEN 'Maio'     
                     WHEN MONTH(OcorData) = '6'   THEN 'Junho' 
                     WHEN MONTH(OcorData) = '7'   THEN 'Julho' 
                     WHEN MONTH(OcorData) = '8'   THEN 'Agosto' 
                     WHEN MONTH(OcorData) = '9'   THEN 'Setembro' 
                     WHEN MONTH(OcorData) = '10'  THEN 'Outubro' 
                     WHEN MONTH(OcorData) = '11'  THEN 'Novembro' 
                     WHEN MONTH(OcorData) = '12'  THEN 'Dezembro'  END)    AS 'Mês', 
    					   YEAR(OcorData) AS 'Ano',
                                OcorStat,
                                OcorCod 
    
    
    	FROM Ocorrencia
    WHERE 
    	  CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,OcorData, 21), 1, 10)) >='20190101'
    And   CONVERT(DATETIME, SUBSTRING(CONVERT(VARCHAR,OcorData, 21), 1, 10)) <='20190603'
    AND EmpCod = '1'
    AND MotOcorCodEstr like '01.09%'
    AND OcorStat IN ('Pendente','Resolvido' )
    
    
    
    GROUP BY MONTH(OcorData), YEAR(OcorData) , OcorStat,  OcorCod
    )
    
    SELECT 
    	  CTE.Mês, 
    	  CTE.Ano, 
    	   CTE.OcorStat   ,
    	   Count(CTE.OcorCod) AS OcorCod
    	
    	
    	   FROM CTE_OCR CTE
    	   
    	
    	   GROUP BY
    	   
    	   	  CTE.Mês, 
    	      CTE.Ano, 
    	      CTE.OcorStat   

    segunda-feira, 3 de junho de 2019 18:16
  • Leandro,

    Certo, acho que entendi, com base, no descrição da OcorStat, veja se este esboço do código te ajuda:

    Declare @OcorrenciaQuantidades Table
    (CodigoOcorrencia Int Primary Key Identity(1,1),
     DataOcorrencia Date Not Null,
     StatusOcorrencia Varchar(20) Not Null)
    
    Insert Into @OcorrenciaQuantidades (StatusOcorrencia, DataOcorrencia)
    Values('Pendente', '2019-04-23'),
    ('Pendente', '2019-06-22'),
    ('Pendente', '2019-11-13'),
    ('Pendente', '2019-08-15'),
    ('Pendente', '2019-11-13'),
    ('Pendente', '2019-11-13'),
    ('Pendente', '2019-11-13'),
    ('Cancelado', '2019-02-16'),
    ('Resolvido', '2019-03-26'),
    ('Resolvido', '2019-12-12'),
    ('Resolvido', '2019-09-28'),
    ('Resolvido', '2019-12-12'),
    ('Resolvido', '2019-09-28')
    
    Select 	
           (CASE Month(A.DataOcorrencia)
            When '1'   THEN 'Janeiro'  
            WHEN '2'   THEN 'Fevereiro'   
            WHEN '3'   THEN 'Março'   
            WHEN '4'   THEN 'Abril' 
            WHEN '5'   THEN 'Maio'     
            WHEN '6'   THEN 'Junho' 
            WHEN '7'   THEN 'Julho' 
            WHEN '8'   THEN 'Agosto' 
            WHEN '9'   THEN 'Setembro' 
            WHEN '10'  THEN 'Outubro' 
            WHEN '11'  THEN 'Novembro' 
            WHEN '12'  THEN 'Dezembro'  
        END) AS 'Mês',
        Year(A.DataOcorrencia) As Ano, 
        Pendente = (Select Count(StatusOcorrencia) From @OcorrenciaQuantidades 
                    Where StatusOcorrencia = 'Pendente' 
                    And Month(A.DataOcorrencia) = Month(DataOcorrencia)),    
        Resolvido = (Select Count(StatusOcorrencia) From @OcorrenciaQuantidades 
                    Where StatusOcorrencia = 'Resolvido' 
                    And Month(A.DataOcorrencia) = Month(DataOcorrencia)),    
        Cancelado = (Select Count(StatusOcorrencia) From @OcorrenciaQuantidades 
                    Where StatusOcorrencia = 'Cancelado' 
                    And Month(A.DataOcorrencia) = Month(DataOcorrencia))    
    From @OcorrenciaQuantidades A
    Group By Month(A.DataOcorrencia), Year(A.DataOcorrencia), A.StatusOcorrencia


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



    segunda-feira, 3 de junho de 2019 18:42
  • Boa tarde

    Junior!

    Eu alterei deixando os dados das minhas tabelas e colocando um parâmetro de data inicial e final porém os valores não batem além de não agrupar o Ano e Mês, ficou da seguinte forma.

    Select 	
           (CASE  Month(OcorData)
            When '1'   THEN 'Janeiro'  
            WHEN '2'   THEN 'Fevereiro'   
            WHEN '3'   THEN 'Março'   
            WHEN '4'   THEN 'Abril' 
            WHEN '5'   THEN 'Maio'     
            WHEN '6'   THEN 'Junho' 
            WHEN '7'   THEN 'Julho' 
            WHEN '8'   THEN 'Agosto' 
            WHEN '9'   THEN 'Setembro' 
            WHEN '10'  THEN 'Outubro' 
            WHEN '11'  THEN 'Novembro' 
            WHEN '12'  THEN 'Dezembro'  
        END) AS 'Mês',
        Year(A.OcorData) As Ano, 
        Pendente = (Select Count(OcorStat) From OCORRENCIA 
                    Where OcorStat = 'Pendente' 
                    And Month(A.OcorData) = Month(OcorData)),    
        Resolvido = (Select Count(OcorStat) From OCORRENCIA 
                    Where OcorStat = 'Resolvido' 
                    And Month(A.OcorData) = Month(OcorData)),    
        Cancelado = (Select Count(OcorStat) From OCORRENCIA 
                    Where OcorStat = 'Cancelado' 
                    And Month(A.OcorData) = Month(OcorData) )    
    From OCORRENCIA A
    WHERE A.OcorData >= '20190101'  AND A.OcorData <= '20190603'
    Group By Month(A.OcorData), Year(A.OcorData), A.OcorStat

    Resultado:

    segunda-feira, 3 de junho de 2019 19:03
  • Boa tarde,

    Leandro, experimente fazer uns testes dessa forma:

    Select 	
        CASE Month(OcorData)
            When '1'   THEN 'Janeiro'  
            WHEN '2'   THEN 'Fevereiro'   
            WHEN '3'   THEN 'Março'   
            WHEN '4'   THEN 'Abril' 
            WHEN '5'   THEN 'Maio'     
            WHEN '6'   THEN 'Junho' 
            WHEN '7'   THEN 'Julho' 
            WHEN '8'   THEN 'Agosto' 
            WHEN '9'   THEN 'Setembro' 
            WHEN '10'  THEN 'Outubro' 
            WHEN '11'  THEN 'Novembro' 
            WHEN '12'  THEN 'Dezembro'  
        END AS 'Mês',
        Year(OcorData) As Ano, 
        Pendente = Count(case when OcorStat = 'Pendente' then 1 end),    
        Resolvido = Count(case when OcorStat = 'Resolvido' then 1 end),    
        Cancelado = Count(case when OcorStat = 'Cancelado' then 1 end)    
    From OCORRENCIA
    WHERE 
        OcorData >= '20190101' AND 
        OcorData <= '20190603'
    Group By 
        Month(OcorData), 
        Year(OcorData)
    

    Espero que ajude


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

    segunda-feira, 3 de junho de 2019 20:03
  • Leandro,

    Como você postou sobre subquery, elaborei o exemplo da forma que você postou.

    Mas acredito que a resposta do Gapimex seja a mais indicada para sua dúvida, fiz uma outra sugestão aplicada a forma de uso do comando Case:

    Select 	
           (CASE Month(A.DataOcorrencia)
            When '1'   THEN 'Janeiro'  
            WHEN '2'   THEN 'Fevereiro'   
            WHEN '3'   THEN 'Março'   
            WHEN '4'   THEN 'Abril' 
            WHEN '5'   THEN 'Maio'     
            WHEN '6'   THEN 'Junho' 
            WHEN '7'   THEN 'Julho' 
            WHEN '8'   THEN 'Agosto' 
            WHEN '9'   THEN 'Setembro' 
            WHEN '10'  THEN 'Outubro' 
            WHEN '11'  THEN 'Novembro' 
            WHEN '12'  THEN 'Dezembro'  
        END) AS 'Mês',
        Year(A.DataOcorrencia) As Ano, 
        Pendente = (Case When StatusOcorrencia = 'Pendente' Then Count(StatusOcorrencia) Else 0 End),    
        Resolvido = (Case When StatusOcorrencia = 'Resolvido' Then Count(StatusOcorrencia) Else 0 End),    
        Cancelado = (Case When StatusOcorrencia = 'Cancelado' Then Count(StatusOcorrencia) Else 0 End)    
    From OcorrenciaQuantidades A
    Group By Month(A.DataOcorrencia), Year(A.DataOcorrencia), A.StatusOcorrencia
    Order By Month(A.DataOcorrencia)



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


    segunda-feira, 3 de junho de 2019 22:47
  • Boa tarde

    Junior!

    Eu alterei deixando os dados das minhas tabelas e colocando um parâmetro de data inicial e final porém os valores não batem além de não agrupar o Ano e Mês, ficou da seguinte forma.

    Select 	
           (CASE  Month(OcorData)
            When '1'   THEN 'Janeiro'  
            WHEN '2'   THEN 'Fevereiro'   
            WHEN '3'   THEN 'Março'   
            WHEN '4'   THEN 'Abril' 
            WHEN '5'   THEN 'Maio'     
            WHEN '6'   THEN 'Junho' 
            WHEN '7'   THEN 'Julho' 
            WHEN '8'   THEN 'Agosto' 
            WHEN '9'   THEN 'Setembro' 
            WHEN '10'  THEN 'Outubro' 
            WHEN '11'  THEN 'Novembro' 
            WHEN '12'  THEN 'Dezembro'  
        END) AS 'Mês',
        Year(A.OcorData) As Ano, 
        Pendente = (Select Count(OcorStat) From OCORRENCIA 
                    Where OcorStat = 'Pendente' 
                    And Month(A.OcorData) = Month(OcorData)),    
        Resolvido = (Select Count(OcorStat) From OCORRENCIA 
                    Where OcorStat = 'Resolvido' 
                    And Month(A.OcorData) = Month(OcorData)),    
        Cancelado = (Select Count(OcorStat) From OCORRENCIA 
                    Where OcorStat = 'Cancelado' 
                    And Month(A.OcorData) = Month(OcorData) )    
    From OCORRENCIA A
    WHERE A.OcorData >= '20190101'  AND A.OcorData <= '20190603'
    Group By Month(A.OcorData), Year(A.OcorData), A.OcorStat

    Resultado:

    Leandro,

    Veja se o commando Distinct pode te ajudar a remover a duplicidade de linhas retornadas.


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

    segunda-feira, 3 de junho de 2019 23:00
  • Obrigado gapimex,  desta forma chegou no resultado esperado.
    terça-feira, 4 de junho de 2019 11:26
  • Obrigado Pela ajuda Junior Galvão, tentei desta forma mas ainda retorna as linhas sem agrupamento dos anos, fiz conforme indicado pelo gapimex e consegui chegar no resultado esperado.
    terça-feira, 4 de junho de 2019 11:29
  • Obrigado Pela ajuda Junior Galvão, tentei desta forma mas ainda retorna as linhas sem agrupamento dos anos, fiz conforme indicado pelo gapimex e consegui chegar no resultado esperado.

    Leandro,

    Perfeito, que bom, fico feliz que tenha conseguido.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | 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, 4 de junho de 2019 11:58
  • Leandro,

    Perfeito, que bom, fico feliz que tenha conseguido!

    Acredito que devido ao uso da coluna A.StatusOcorrencia no comando Group By, pois o Gapimex não fez este agrupamento.

    Eu alterei novamente:

    Select 	
           (CASE  Month(OcorData)
            When '1'   THEN 'Janeiro'  
            WHEN '2'   THEN 'Fevereiro'   
            WHEN '3'   THEN 'Março'   
            WHEN '4'   THEN 'Abril' 
            WHEN '5'   THEN 'Maio'     
            WHEN '6'   THEN 'Junho' 
            WHEN '7'   THEN 'Julho' 
            WHEN '8'   THEN 'Agosto' 
            WHEN '9'   THEN 'Setembro' 
            WHEN '10'  THEN 'Outubro' 
            WHEN '11'  THEN 'Novembro' 
            WHEN '12'  THEN 'Dezembro'  
        END) AS 'Mês',
        Year(A.OcorData) As Ano, 
        Pendente = (Select Count(OcorStat) From OCORRENCIA 
                    Where OcorStat = 'Pendente' 
                    And Month(A.OcorData) = Month(OcorData)),    
        Resolvido = (Select Count(OcorStat) From OCORRENCIA 
                    Where OcorStat = 'Resolvido' 
                    And Month(A.OcorData) = Month(OcorData)),    
        Cancelado = (Select Count(OcorStat) From OCORRENCIA 
                    Where OcorStat = 'Cancelado' 
                    And Month(A.OcorData) = Month(OcorData) )    
    From OCORRENCIA A
    WHERE A.OcorData >= '20190101'  AND A.OcorData <= '20190603'
    Group By Month(A.OcorData), Year(A.OcorData)

    Ou assim:

    Select 	
           (CASE Month(A.DataOcorrencia)
            When '1'   THEN 'Janeiro'  
            WHEN '2'   THEN 'Fevereiro'   
            WHEN '3'   THEN 'Março'   
            WHEN '4'   THEN 'Abril' 
            WHEN '5'   THEN 'Maio'     
            WHEN '6'   THEN 'Junho' 
            WHEN '7'   THEN 'Julho' 
            WHEN '8'   THEN 'Agosto' 
            WHEN '9'   THEN 'Setembro' 
            WHEN '10'  THEN 'Outubro' 
            WHEN '11'  THEN 'Novembro' 
            WHEN '12'  THEN 'Dezembro'  
        END) AS 'Mês',
        Year(A.DataOcorrencia) As Ano, 
        Pendente = (Case When StatusOcorrencia = 'Pendente' Then Count(StatusOcorrencia) Else 0 End),    
        Resolvido = (Case When StatusOcorrencia = 'Resolvido' Then Count(StatusOcorrencia) Else 0 End),    
        Cancelado = (Case When StatusOcorrencia = 'Cancelado' Then Count(StatusOcorrencia) Else 0 End)    
    From OcorrenciaQuantidades A
    Group By Month(A.DataOcorrencia), Year(A.DataOcorrencia)
    Order By Month(A.DataOcorrencia)

    Mas tranquilo, estou sempre por aqui para aprender e ajudar.


    Pedro Antonio Galvão Junior [MVP | MCC | MSTC | MIE | 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, 4 de junho de 2019 12:04