-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathSQL024.sql
232 lines (207 loc) · 8.83 KB
/
SQL024.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
/*
* PLT045 - Script para listagem do ranking de alunos
* @param CODFILIAL Código da Filial
* @param CODPERLET Código do Periodo Letivo
* @param CODTURMA Código da Turma
*
* @author Marcelo Valvassori Bittencourt
* @mail [email protected]
* @version 1.0 bitts 02/12/2016
SQL para listagem do Ranking de alunos da Institução.
Objetivo: Formação da Média Harmônica:
Descrição da realização do calculo
É realizado o calcula da Média Final de todas as disciplinas de um aluno, conforme formula:
MEDIA PARCIAL = (NOTA 1 + NOTA 2 + NOTA 3) / 3
SE ( MEDIA PARCIAL >= 7 ) ENTÃO
MEDIA FINAL = MEDIA PARCIAL
SE ( MEDIA PARCIAL < 7 ) ENTÃO
MÉDIA FINAL = ( ( (NOTA 1 + NOTA 2 + NOTA 3) / 3 ) + NOTA DO EXAME ) / 2
Tomando por base a média final (MF) de cada disciplina, é realizado a soma de todas as médias
finais, valor esta que e então dividido pelo número total de disciplinas que o aluno frequenta
no periodo letivo, gerando assim a média aritmética das notas do aluno.
Levando-se em consideração o número de faltas do aluno e gerado um fator depreciativo para
para a média aritmética das notas, seguindo o critério:
FATOR DEPRECIATIVO = ( (SOMA TOTAL DAS FALTAS DE TODAS AS DISCIPLINAS) * 100 ) / SOMA TOTAL DE AULAS DADAS EM TODAS AS DISCIPLINAS
Para que todos os calculos sejam realizados de forma correto é necessário que todas as notas,
faltas e total de aulas de cada disciplina tenham sido lançadas no sistema.
De posse destes valores é gerado o calculo da média harmônica do aluno, conforme a formula:
MEDIA HARMONICA = ( MEDIA ARITMETICA - (MEDIA ARITMETICA * FATOR DEPRECIATIVO) )
O critério de desempate é definido pela ordem alfabetica crescente do nome do aluno.
OBS.: Alunos que foram transferidos e por consequência não possuem, uma ou mais, de suas notas
lançadas no sistema, possuem o mesmo calculo da média final e indice de depreciativo dos demais
alunos de forma a privilegiar alunos que iniciaram o ano letivo na instituição em detrimento
dos que não o fizeram.
*/
DECLARE
@CDM INT = 3, -- Precisão do calculo (em casas decimais)
@CODFILIAL INT = 6,
@CODPERLET VARCHAR(15) = '2016';
WITH HARMONICA AS (
SELECT
MA.RA,
ROUND(
(SUM(
CASE WHEN
N1.NOTA IS NOT NULL AND N2.NOTA IS NOT NULL AND N3.NOTA IS NOT NULL AND ((N1.NOTA + N2.NOTA + N3.NOTA)/3) >= 7
THEN
ROUND( ((N1.NOTA + N2.NOTA + N3.NOTA)/3), @CDM)
WHEN
N1.NOTA IS NOT NULL AND N2.NOTA IS NOT NULL AND N3.NOTA IS NOT NULL AND ((N1.NOTA + N2.NOTA + N3.NOTA)/3) < 7 AND EX.NOTA IS NOT NULL
THEN
ROUND( ( (((N1.NOTA + N2.NOTA + N3.NOTA)/3) + EX.NOTA)/2 ), @CDM)
ELSE NULL END
) / COUNT(N1.RA)), @CDM) AS [MEDIA NOTAS],
ROUND(
CASE WHEN ( (SUM(ISNULL(F1.FALTAS,0) + ISNULL(F2.FALTAS,0) + ISNULL(F3.FALTAS,0))) > 0) THEN
(((SUM(ISNULL(F1.FALTAS,0) + ISNULL(F2.FALTAS,0) + ISNULL(F3.FALTAS,0))) * 100) / SUM(AULAS.TOTAL))
ELSE 0 END
, @CDM)
AS [PERCENTAGEM FALTAS],
SUM(AULAS.TOTAL) AS [TOTAL DE ALTAS],
(SUM(ISNULL(F1.FALTAS,0) + ISNULL(F2.FALTAS,0) + ISNULL(F3.FALTAS,0))) AS [TOTAL DE FALTAS],
ROUND(
(SUM(
CASE WHEN
N1.NOTA IS NOT NULL AND N2.NOTA IS NOT NULL AND N3.NOTA IS NOT NULL AND ((N1.NOTA + N2.NOTA + N3.NOTA)/3) >= 7
THEN
ROUND( ((N1.NOTA + N2.NOTA + N3.NOTA)/3), @CDM)
WHEN
N1.NOTA IS NOT NULL AND N2.NOTA IS NOT NULL AND N3.NOTA IS NOT NULL AND ((N1.NOTA + N2.NOTA + N3.NOTA)/3) < 7 AND EX.NOTA IS NOT NULL
THEN
ROUND( ( (((N1.NOTA + N2.NOTA + N3.NOTA)/3) + EX.NOTA)/2 ), @CDM)
ELSE NULL END
) / COUNT(N1.RA)) -
((SUM(
CASE WHEN
N1.NOTA IS NOT NULL AND N2.NOTA IS NOT NULL AND N3.NOTA IS NOT NULL AND ((N1.NOTA + N2.NOTA + N3.NOTA)/3) >= 7
THEN
ROUND( ((N1.NOTA + N2.NOTA + N3.NOTA)/3), @CDM)
WHEN
N1.NOTA IS NOT NULL AND N2.NOTA IS NOT NULL AND N3.NOTA IS NOT NULL AND ((N1.NOTA + N2.NOTA + N3.NOTA)/3) < 7 AND EX.NOTA IS NOT NULL
THEN
ROUND( ( (((N1.NOTA + N2.NOTA + N3.NOTA)/3) + EX.NOTA)/2 ), @CDM)
ELSE NULL END
) / COUNT(N1.RA)) *
(ROUND(
CASE WHEN ( (SUM(ISNULL(F1.FALTAS,0) + ISNULL(F2.FALTAS,0) + ISNULL(F3.FALTAS,0))) > 0) THEN
(((SUM(ISNULL(F1.FALTAS,0) + ISNULL(F2.FALTAS,0) + ISNULL(F3.FALTAS,0))) * 100)/SUM(AULAS.TOTAL)) /100
ELSE 0 END
, @CDM)
))
, @CDM) AS [MEDIA HARMONICA]
FROM
SMATRICULA AS MA (NOLOCK)
LEFT JOIN (
SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) N1 (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
N1.CODCOLIGADA = MA.CODCOLIGADA AND
N1.IDTURMADISC = MA.IDTURMADISC AND
N1.RA = MA.RA AND
N1.CODETAPA = '1' AND
N1.TIPOETAPA = 'N'
LEFT JOIN (
SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) F1 (RA, FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
F1.CODCOLIGADA = MA.CODCOLIGADA AND
F1.IDTURMADISC = MA.IDTURMADISC AND
F1.RA = MA.RA AND
F1.CODETAPA = '2' AND
F1.TIPOETAPA = 'N'
LEFT JOIN (
SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) N2 (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
N2.CODCOLIGADA = MA.CODCOLIGADA AND
N2.IDTURMADISC = MA.IDTURMADISC AND
N2.RA = MA.RA AND
N2.CODETAPA = '3' AND
N2.TIPOETAPA = 'N'
LEFT JOIN (
SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) F2 (RA, FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
F2.CODCOLIGADA = MA.CODCOLIGADA AND
F2.IDTURMADISC = MA.IDTURMADISC AND
F2.RA = MA.RA AND
F2.CODETAPA = '4' AND
F2.TIPOETAPA = 'F'
LEFT JOIN (
SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) N3 (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
N3.CODCOLIGADA = MA.CODCOLIGADA AND
N3.IDTURMADISC = MA.IDTURMADISC AND
N3.RA = MA.RA AND
N3.CODETAPA = '5' AND
N3.TIPOETAPA = 'N'
LEFT JOIN (
SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) F3 (RA, FALTAS, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
F3.CODCOLIGADA = MA.CODCOLIGADA AND
F3.IDTURMADISC = MA.IDTURMADISC AND
F3.RA = MA.RA AND
F3.CODETAPA = '6' AND
F3.TIPOETAPA = 'F'
LEFT JOIN (
SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) MD (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
MD.CODCOLIGADA = MA.CODCOLIGADA AND
MD.IDTURMADISC = MA.IDTURMADISC AND
MD.RA = MA.RA AND
MD.CODETAPA = '7' AND
MD.TIPOETAPA = 'N'
LEFT JOIN (
SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) EX (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
EX.CODCOLIGADA = MA.CODCOLIGADA AND
EX.IDTURMADISC = MA.IDTURMADISC AND
EX.RA = MA.RA AND
EX.CODETAPA = '8' AND
EX.TIPOETAPA = 'N'
LEFT JOIN (
SELECT RA, CONVERT(FLOAT, NOTAFALTA) AS NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) MF (RA, NOTA, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
MF.CODCOLIGADA = MA.CODCOLIGADA AND
MF.IDTURMADISC = MA.IDTURMADISC AND
MF.RA = MA.RA AND
MF.CODETAPA = '10' AND
MF.TIPOETAPA = 'N'
LEFT JOIN (
SELECT RA, CONVERT(INT, AULASDADAS) AS TOTAL, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA
FROM SNOTAETAPA (NOLOCK)
) AULAS (RA, TOTAL, CODCOLIGADA, IDTURMADISC, CODETAPA, TIPOETAPA) ON
AULAS.CODCOLIGADA = MA.CODCOLIGADA AND
AULAS.IDTURMADISC = MA.IDTURMADISC AND
AULAS.RA = MA.RA AND
AULAS.CODETAPA = '10' AND
AULAS.TIPOETAPA = 'F'
WHERE
MA.CODSTATUS IN ('17','43','54','64','67','47','23','53','39','50','60','2','44','65','55','19','18','20','34','28','51','40','61')
AND MA.RA=N1.RA AND MA.RA=N2.RA AND MA.RA=N3.RA
GROUP BY MA.RA
)
SELECT
ROW_NUMBER() OVER(ORDER BY AR.[MEDIA HARMONICA] DESC) AS [COLOCAÇÃO GERAL],
ROW_NUMBER() OVER(PARTITION BY MT.CODTURMA ORDER BY AR.[MEDIA HARMONICA] DESC) AS [COLOCAÇÃO TURMA],
SP.CODPERLET, AR.RA, UPPER(PP.NOME) AS NOME, MT.CODTURMA, AR.[MEDIA HARMONICA]
FROM
HARMONICA AS AR (NOLOCK)
LEFT JOIN SALUNO AS AL (NOLOCK) ON
AR.RA=AL.RA
LEFT JOIN PPESSOA AS PP (NOLOCK) ON
AL.CODPESSOA=PP.CODIGO
LEFT JOIN SMATRICPL AS MT (NOLOCK) ON
MT.RA=AR.RA
LEFT JOIN SPLETIVO AS SP (NOLOCK) ON
MT.IDPERLET = SP.IDPERLET
WHERE
SP.CODFILIAL = @CODFILIAL AND
SP.CODPERLET LIKE '%'+ @CODPERLET +'%'
ORDER BY
AR.[MEDIA HARMONICA] DESC