Consulta com subConsulta

Question

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. 

Answer 1

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

Answer 2

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

Answer 3

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.