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
| With dmGesco2010 do
begin
qryLesNotes.SQL.Clear;
qryLesNotes.SQL.Add ('SELECT N.ScoNum, N.EtuNum,E.EtuNom, E.EtuPrenoms, S.SexNom,V.VilNom, Na.NatNom, I.ClaNum, M.MatNom, TJ.Coeff, AVG(N.NotDevoir) AS Devoir, AVG(N.NotCompo) AS Compo, C.CycNom ');
qryLesNotes.SQL.Add (' '+', F.FilNom,O.OptNom, (AVG(N.NotDevoir) + AVG(N.NotCompo)) / 2 AS Moy, (AVG(N.NotDevoir) + AVG(N.NotCompo))/ 2 * TJ.Coeff AS MoyGle ');
qryLesNotes.SQL.Add ('FROM tblNotes N');
qryLesNotes.SQL.Add ('INNER JOIN TJ_Programmer TJ ON N.ScoNum = TJ.ScoNum');
qryLesNotes.SQL.Add ('INNER JOIN tblEtudiant E ON N.EtuNum = E.EtuNum');
qryLesNotes.SQL.Add ('INNER JOIN tblInscription I ON E.EtuNum = I.EtuNum');
qryLesNotes.SQL.Add ('INNER JOIN tblClasse ON TJ.ClaNum = tblClasse.ClaNum AND I.ClaNum = tblClasse.ClaNum');
qryLesNotes.SQL.Add ('INNER JOIN tblMatiere M ON N.MatCode = M.MatCode AND TJ.MatCode = M.MatCode');
qryLesNotes.SQL.Add ('INNER JOIN tblSexe S ON E.SexNum = S.SexNum');
qryLesNotes.SQL.Add ('INNER JOIN tblVille V ON E.VilNum = V.VilNum');
qryLesNotes.SQL.Add ('INNER JOIN tblCycle C ON tblClasse.CycNum = C.CycNum');
qryLesNotes.SQL.Add ('INNER JOIN tblNation Na ON E.NatNum = Na.NatNum');
qryLesNotes.SQL.Add ('INNER JOIN tblFiliere F ON tblClasse.FilNum = F.FilNum');
qryLesNotes.SQL.Add ('INNER JOIN tblOption O ON tblClasse.OptNum = O.OptNum');
qryLesNotes.SQL.Add ('WHERE (N.EtuNum = : ParamEtuNum)');
qryLesNotes.SQL.Add ('AND (N.NotDate Between : ParamDateDebut And : ParamDateFin)');
qryLesNotes.SQL.Add ('GROUP BY N.ScoNum, N.EtuNum, E.EtuNom, E.EtuPrenoms, S.SexNom, V.VilNom,Na.NatNom, I.ClaNum, M.MatNom, TJ.Coeff, C.CycNom, F.FilNom,O.OptNom');
qryLesNotes.SQL.Add ('HAVING ((AVG(N.NotDevoir) + AVG(N.NotCompo)) / 2 IS NOT NULL)');
qryLesNotes.SQL.Add ('ORDER BY E.EtuNom, M.MatNom');
qryLesNotes.Parameters.ParamByName('ParamEtuNum').Value := dlcEtuNum.Text;
qryLesNotes.Parameters.ParamByName ('ParamDateDebut').Value := dtpDateDebut.Date;
qryLesNotes.Parameters.ParamByName ('ParamDateFin').Value := dtpDateFin.Date;
qryLesNotes.Open;
end; |
Partager