none
Consulta com subConsulta RRS feed

  • Pergunta

  • Boa tarde pessoal!

    Segue a dúvida:

    Tenho essas duas tabelas:

    CREATE TABLE [dbo].[tblExameDirecao](
    	[IDExameDir] [int] IDENTITY(1,1) NOT NULL,
    	[DataExameDir] [date] NOT NULL,
    	[Placa] [varchar](7) NOT NULL,
    	[IDInstrutor] [int] NOT NULL,
    	[Horario] [time](7) NULL,
     CONSTRAINT [PK_tblExameDirecao1] PRIMARY KEY CLUSTERED 
    (
    	[IDExameDir] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]
    
    
    CREATE TABLE [dbo].[tblItensExameDir](
    	[IDExameDir] [int] NOT NULL,
    	[IDAluno] [int] NOT NULL,
    	[Examinador1] [varchar](15) NULL,
    	[Examinador2] [varchar](15) NULL,
    	[Resultado] [varchar](15) NULL,
     CONSTRAINT [PK_tbleItensExameDir] PRIMARY KEY CLUSTERED 
    (
    	[IDExameDir] ASC,
    	[IDAluno] ASC
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
    ) ON [PRIMARY]

    Tenho a Procedure no código abaixo para me apresentar:

    DataExameDir = A Data do Exame de Direção
    Placa        = A Placa do veículo que realizou o exame
    EXAMES       = O Número de exames realizados no veículo
    APROVADOS    = O Número de candidatos aprovados
    REPROVADOS   = O Número de candidatos reprovados
    INDICE       = O indice de aprovação

    DECLARE @Aprovados DECIMAL(3,0),@Reprovados DECIMAL(3,0),@Exames DECIMAL(3,0),@Indice FLOAT;
    
    	SET @Exames=(SELECT COUNT(tblItensExameDir.IDAluno) FROM tblItensExameDir)
    	SET @Aprovados=(SELECT COUNT(tblItensExameDir.IDAluno) FROM tblItensExameDir WHERE tblItensExameDir.Resultado='APROVADO');
    	SET @Reprovados=(SELECT COUNT(tblItensExameDir.IDAluno) FROM tblItensExameDir WHERE tblItensExameDir.Resultado='REPROVADO')
    	SET @Indice=(@Aprovados/@Exames)*100
    	
    	--SELECT @Exames AS EXAMES,@Aprovados AS APROVADOS,@Reprovados AS REPROVADOS
    
    	SELECT DISTINCT dbo.tblExameDirecao.DataExameDir, tblExameDirecao.Placa, @Exames AS EXAMES, @Aprovados AS APROVADOS, @Reprovados AS REPROVADOS,
    					@Indice AS INDICE
    	            
    	FROM  dbo.tblExameDirecao INNER JOIN dbo.tblItensExameDir ON dbo.tblExameDirecao.IDExameDir = dbo.tblItensExameDir.IDExameDir INNER JOIN
    		  dbo.tblVeiculo ON dbo.tblExameDirecao.Placa = dbo.tblVeiculo.Placa
    		  
    	GROUP BY dbo.tblExameDirecao.Placa, tblExameDirecao.DataExameDir, dbo.tblItensExameDir.Resultado,dbo.tblVeiculo.Categoria
    	HAVING ((tblExameDirecao.DataExameDir)BETWEEN @DataInicio AND @DataFinal)AND tblVeiculo.Categoria=@Categoria
    Bom, percebam que na procedure eu configurei as variáveis @Exames, @Aprovados e @Reprovados como DECIMAL e não como INT e não apresentando as casas decimais.
    Isso porque quando usando o valor INT o resultado apresentado nos calculos da variável @Indice não eram satisfatórios. Sendo apresentado sempre '0.00' independente do resultado. E isso resolveu o problema.

    Agora o problema é quando ocorre de termos dois veículos realizando exames na mesma data.
    Não estou conseguindo separar os resultados por veículo. Ou seja. Os valores se somam.

    Exemplo:

    Se tenho 3 exames no carro '001' e 2 exames no carro '002', me é apresentado:

    DataExameDir-+-Placa-----+-EXAMES-+-APROVADOS-+-REPROVADOS-+-INDICE
    XX/XX/XXXX----+-ABC0001+----5-------+-------2--------+---------3---------+---40,00
    XX/XX/XXXX----+-ABC0002+----5-------+-------2--------+---------3---------+---40,00

    Preciso conseguir com que os resultados fiquem com os seus respectivos veículos para apresentar o resultado em um gráfico.

    Quem puder ajudar...

    Agradeço. 




    sexta-feira, 14 de abril de 2017 15:31

Respostas

  • Boa noite,

    Experimente fazer uns tests dessa forma:

    SELECT 
        dbo.tblExameDirecao.DataExameDir, 
        dbo.tblExameDirecao.Placa, 
        COUNT(tblItensExameDir.IDAluno) AS EXAMES,
        COUNT(case when tblItensExameDir.Resultado = 'APROVADO' then tblItensExameDir.IDAluno end) AS APROVADOS, 
        COUNT(case when tblItensExameDir.Resultado = 'REPROVADO' then tblItensExameDir.IDAluno end) AS REPROVADOS, 
        100.0 * 
        COUNT(case when tblItensExameDir.Resultado = 'APROVADO' then tblItensExameDir.IDAluno end) /
        COUNT(tblItensExameDir.IDAluno) AS INDICE            
    FROM dbo.tblExameDirecao 
    INNER JOIN dbo.tblItensExameDir ON dbo.tblExameDirecao.IDExameDir = dbo.tblItensExameDir.IDExameDir 
    INNER JOIN dbo.tblVeiculo ON dbo.tblExameDirecao.Placa = dbo.tblVeiculo.Placa
    WHERE 
        tblExameDirecao.DataExameDir BETWEEN @DataInicio AND @DataFinal and
        tblVeiculo.Categoria = @Categoria
    GROUP BY 
        dbo.tblExameDirecao.DataExameDir, 
        dbo.tblExameDirecao.Placa

    Espero que ajude


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

    • Sugerido como Resposta Antero Marques domingo, 16 de abril de 2017 11:50
    sexta-feira, 14 de abril de 2017 21:29

Todas as Respostas

  • Boa noite,

    Experimente fazer uns tests dessa forma:

    SELECT 
        dbo.tblExameDirecao.DataExameDir, 
        dbo.tblExameDirecao.Placa, 
        COUNT(tblItensExameDir.IDAluno) AS EXAMES,
        COUNT(case when tblItensExameDir.Resultado = 'APROVADO' then tblItensExameDir.IDAluno end) AS APROVADOS, 
        COUNT(case when tblItensExameDir.Resultado = 'REPROVADO' then tblItensExameDir.IDAluno end) AS REPROVADOS, 
        100.0 * 
        COUNT(case when tblItensExameDir.Resultado = 'APROVADO' then tblItensExameDir.IDAluno end) /
        COUNT(tblItensExameDir.IDAluno) AS INDICE            
    FROM dbo.tblExameDirecao 
    INNER JOIN dbo.tblItensExameDir ON dbo.tblExameDirecao.IDExameDir = dbo.tblItensExameDir.IDExameDir 
    INNER JOIN dbo.tblVeiculo ON dbo.tblExameDirecao.Placa = dbo.tblVeiculo.Placa
    WHERE 
        tblExameDirecao.DataExameDir BETWEEN @DataInicio AND @DataFinal and
        tblVeiculo.Categoria = @Categoria
    GROUP BY 
        dbo.tblExameDirecao.DataExameDir, 
        dbo.tblExameDirecao.Placa

    Espero que ajude


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

    • Sugerido como Resposta Antero Marques domingo, 16 de abril de 2017 11:50
    sexta-feira, 14 de abril de 2017 21:29
  • P E R F E I T O  gapimex!

    Certinho, vlws mesmo. Só tirei o '.0' do texto (100.0*)

    COUNT(case when tblItensExameDir.Resultado = 'REPROVADO' then tblItensExameDir.IDAluno end) AS REPROVADOS, 
        100.0 *
    Marcello M. Mariano
    domingo, 16 de abril de 2017 00:24
  • Marcello,

    Se a resposta do gapimex foi perfeita para resolver teu problema, maque-a como resposta. Assim você ajuda outras pessoas que estão com o mesmo problema.


    Antero Marques
    _______________________________________________________________________________ 

    Se a resposta for útil, marque como útil, se respondeu totalmente sua dúvida, marque como resposta. O Fórum MSDN é utilizado também como base de conhecimento, então é responsabilidade de todos mantê-lo organizado e funcional.


    domingo, 16 de abril de 2017 11:51