add values and houses after the comma

Question

add values and houses after the comma

Navigation

#1 by (1 votes)

0

Good morning, I have a problem here and I would like a help from you, I have a select here that brings the load of one student per subject, for each student he repeats the 2x disciplines and the different time load so I can not group, how do I add them per student and tb remove the number of houses after the comma, is bringing 6 houses and I need to bring only 2 houses. follows the sql code:

SELECT C.EDEMPRESA_ID
    ,C.GMUNICIPIO_ID
    ,C.SECRETARIA_ID
    ,C.MATRICULA_ID
    ,F.FICHAS_ID
    ,F.FICHA
    ,E.ESCOLA_ID
    ,E.ESCOLA
    ,S.SERIE_ID
    ,S.SERIE
    ,TN.TURNO_ID
    ,TN.TURNO
    ,TM.TURMA_ID
    ,TM.TURMA
    ,M.MATERIA_ID
    ,M.MATERIA
    ,MAX(FC.FUNCIONA) AS FUNCIONA
    ,ISNULL(L.GLEG_CFH_ID, 0) AS GLEG_CFH_ID
    ,L.SIGLA
    ,L.LEG_CFH
    ,'Anual' AS BIMESTRE
    ,C.ANO
    ,MAX(E.ENDERECO) AS ENDERECO
    ,MAX(FL.PAI) AS PAI
    ,MAX(FL.MAE) AS MAE
    ,MAX(CONVERT(VARCHAR(10), F.NASCTO, 103)) AS DATANASCIMENTO
    ,MAX(GM.MUNICIPIO) AS GMUNICIPIO_ID_NASC
    ,MAX(E.FONE) AS FONE
    ,MAX(E.CEP) AS CEP
    ,C.AVALIACAO
    ,CASE 
        WHEN I.SITU_FINAL IS NULL
            THEN 'Cursando'
        WHEN I.SITU_FINAL = '0'
            THEN 'Cursando'
        WHEN I.SITU_FINAL = '1'
            THEN 'Tr. Turma'
        WHEN I.SITU_FINAL = '2'
            THEN 'Transferido'
        WHEN I.SITU_FINAL = '3'
            THEN 'Transferido'
        WHEN I.SITU_FINAL = '4'
            THEN 'Desistência'
        WHEN I.SITU_FINAL = '5'
            THEN 'Matrícula Sem Efeito'
        WHEN I.SITU_FINAL = '6'
            THEN 'Reclassificação'
        WHEN I.SITU_FINAL = '7'
            THEN 'Cassificação'
        WHEN I.SITU_FINAL = '8'
            THEN 'Falecido'
        WHEN I.SITU_FINAL = 'E'
            THEN 'Em Construção'
        WHEN I.SITU_FINAL = 'S'
            THEN 'Aprovado'
        WHEN I.SITU_FINAL = 'N'
            THEN 'Reprovado'
        WHEN I.SITU_FINAL = 'D'
            THEN 'Desistente'
        END SITUACAO,
                    (
                SELECT COUNT(*) AS DIAS_LETIVO
                FROM MONTA_DIA_LETIVO_RA(1, 5103254, 10, '2014', '03/20/2014', CONVERT(VARCHAR(10), GETDATE(), 101))
                ) AS DIAS_LETIVOS_TOTAIS
    ,(
            (
                SELECT COUNT(*) AS DIAS_LETIVO
                FROM MONTA_DIA_LETIVO_RA(1, 5103254, 10, '2014', '03/20/2014', CONVERT(VARCHAR(10), GETDATE(), 101))
                ) * (
                SELECT COALESCE(CAST(gm1.CARGA_HORA AS INTEGER), 0)
                FROM ED_MATRICUL M
                WHERE M.EDEMPRESA_ID = C.EDEMPRESA_ID
                    AND M.GMUNICIPIO_ID = C.GMUNICIPIO_ID
                    AND M.SECRETARIA_ID = C.SECRETARIA_ID
                    AND M.ANO = C.ANO
                    AND M.MATRICULA_ID = C.MATRICULA_ID
                ) * 100
            ) / (
            COALESCE(CAST((
                        SELECT (COALESCE(SUM(GM1.CARGA_HORA), 0))
                        FROM ED_GRA_MATE GM1
                        WHERE GM1.EDEMPRESA_ID = TM.EDEMPRESA_ID
                            AND GM1.GMUNICIPIO_ID = TM.GMUNICIPIO_ID
                            AND GM1.SECRETARIA_ID = TM.SECRETARIA_ID
                            AND GM1.GRADE_ID = TM.GRADE_ID
                        ) AS INTEGER), 0) / 100
            ) * COALESCE((
                SELECT (COALESCE(SUM(GM2.SEMI_HORA), 0) / 5)
                FROM ED_GRA_MATE GM2
                WHERE GM2.EDEMPRESA_ID = TM.EDEMPRESA_ID
                    AND GM2.GMUNICIPIO_ID = TM.GMUNICIPIO_ID
                    AND GM2.SECRETARIA_ID = TM.SECRETARIA_ID
                    AND GM2.GRADE_ID = TM.GRADE_ID
                ), 0)  AS CARGA_HORARIA
FROM ED_CAN_CFH AS C
LEFT JOIN ED_MATERIAS AS M ON C.EDEMPRESA_ID = M.EDEMPRESA_ID
    AND C.GMUNICIPIO_ID = M.GMUNICIPIO_ID
    AND C.SECRETARIA_ID = M.SECRETARIA_ID
    AND C.MATERIA_ID = M.MATERIA_ID
LEFT JOIN GLEG_CFH AS L ON C.GLEG_CFH_ID = L.GLEG_CFH_ID
LEFT JOIN ED_MATRICUL AS MT ON C.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND C.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND C.SECRETARIA_ID = MT.SECRETARIA_ID
    AND C.ANO = MT.ANO
    AND C.MATRICULA_ID = MT.MATRICULA_ID
INNER JOIN ED_FICHA AS F ON C.EDEMPRESA_ID = F.EDEMPRESA_ID
    AND C.GMUNICIPIO_ID = F.GMUNICIPIO_ID
    AND C.SECRETARIA_ID = F.SECRETARIA_ID
    AND F.FICHAS_ID = MT.FICHAS_ID
LEFT JOIN ED_SERIES AS S ON S.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND S.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND S.SECRETARIA_ID = MT.SECRETARIA_ID
    AND S.SERIE_ID = MT.SERIE_ID
LEFT JOIN ED_TURNO AS TN ON TN.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND TN.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND TN.SECRETARIA_ID = MT.SECRETARIA_ID
    AND TN.TURNO_ID = MT.TURNO_ID
LEFT JOIN ED_TURMAS AS TM ON TM.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND TM.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND TM.SECRETARIA_ID = MT.SECRETARIA_ID
    AND TM.TURMA_ID = MT.TURMA_ID
    AND TM.ANO = MT.ANO
    AND TM.TURNO_ID = MT.TURNO_ID
    AND TM.SERIE_ID = MT.SERIE_ID
    AND TM.ESCOLA_ID = MT.ESCOLA_ID
LEFT JOIN ED_ESCOLAS AS E ON E.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND E.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND E.SECRETARIA_ID = MT.SECRETARIA_ID
    AND E.ESCOLA_ID = MT.ESCOLA_ID
INNER JOIN ED_FILIACAO AS FL ON FL.EDEMPRESA_ID = F.EDEMPRESA_ID
    AND FL.GMUNICIPIO_ID = F.GMUNICIPIO_ID
    AND FL.SECRETARIA_ID = F.SECRETARIA_ID
    AND FL.FILIACAO_ID = F.FILIACAO_ID
LEFT JOIN GMUNICIPIO AS GM ON GM.GMUNICIPIO_ID = F.GMUNICIPIO_ID_NASC
LEFT JOIN ED_LOTACAO AS LT ON LT.EDEMPRESA_ID = MT.EDEMPRESA_ID
    AND LT.GMUNICIPIO_ID = MT.GMUNICIPIO_ID
    AND LT.SECRETARIA_ID = MT.SECRETARIA_ID
    AND LT.ESCOLA_ID = MT.ESCOLA_ID
    AND LT.ANO = MT.ANO
    AND LT.TURMA_ID = MT.TURMA_ID
    AND LT.TURNO_ID = MT.TURNO_ID
    AND LT.SERIE_ID = MT.SERIE_ID
LEFT JOIN ED_PESSOAL AS P ON P.EDEMPRESA_ID = LT.EDEMPRESA_ID
    AND P.GMUNICIPIO_ID = LT.GMUNICIPIO_ID
    AND P.SECRETARIA_ID = LT.SECRETARIA_ID
    AND P.ANO_LETIVO = LT.ANO
LEFT JOIN ED_PROFESSO AS PR ON PR.EDEMPRESA_ID = LT.EDEMPRESA_ID
    AND PR.GMUNICIPIO_ID = LT.GMUNICIPIO_ID
    AND PR.SECRETARIA_ID = LT.SECRETARIA_ID
    AND PR.PROFESSOR_ID = LT.PROFESSOR_ID
LEFT JOIN ED_FUNCIONA AS FC ON FC.EDEMPRESA_ID = PR.EDEMPRESA_ID
    AND FC.GMUNICIPIO_ID = PR.GMUNICIPIO_ID
    AND FC.SECRETARIA_ID = PR.SECRETARIA_ID
    AND FC.FUNCIONA_ID = PR.FUNCIONA_ID
LEFT JOIN ED_FIC_NOT N ON N.EDEMPRESA_ID = C.EDEMPRESA_ID
    AND N.SECRETARIA_ID = C.SECRETARIA_ID
    AND N.GMUNICIPIO_ID = C.GMUNICIPIO_ID
    AND N.MATRICULA_ID = MT.MATRICULA_ID
    AND N.ANO = MT.ANO
    AND N.ESCOLA_ID = MT.ESCOLA_ID
    AND N.MATERIA_ID = C.MATERIA_ID
LEFT JOIN ED_FIC_IND I ON I.EDEMPRESA_ID = C.EDEMPRESA_ID
    AND I.SECRETARIA_ID = C.SECRETARIA_ID
    AND I.GMUNICIPIO_ID = C.GMUNICIPIO_ID
    AND I.MATRICULA_ID = MT.MATRICULA_ID
    AND I.ANO = MT.ANO
    AND I.ESCOLA_ID = MT.ESCOLA_ID
    AND I.SERIE_ID = LT.SERIE_ID
    AND I.TURNO_ID = LT.TURNO_ID
    AND I.TURMA_ID = LT.TURMA_ID
LEFT JOIN ED_GRA_MATE GM1 ON GM1.EDEMPRESA_ID = N.EDEMPRESA_ID
    AND GM1.SECRETARIA_ID = N.SECRETARIA_ID
    AND GM1.GMUNICIPIO_ID = N.GMUNICIPIO_ID
    AND GM1.MATERIA_ID = N.MATERIA_ID
WHERE C.EDEMPRESA_ID = 1
    AND C.GMUNICIPIO_ID = 5103254
    AND C.SECRETARIA_ID = 10
    AND MT.ANO = '2014'
    AND LT.ESCOLA_ID = 1
    AND LT.SERIE_ID = 6
    AND LT.TURNO_ID = 1
    AND LT.TURMA_ID = 1
    AND C.BIMESTRE IN (7)
GROUP BY C.EDEMPRESA_ID
    ,C.GMUNICIPIO_ID
    ,C.SECRETARIA_ID
    ,C.MATRICULA_ID
    ,F.FICHAS_ID
    ,F.FICHA
    ,E.ESCOLA_ID
    ,E.ESCOLA
    ,S.SERIE_ID
    ,S.SERIE
    ,TN.TURNO_ID
    ,TN.TURNO
    ,TM.TURMA_ID
    ,TM.TURMA
    ,C.FALTAS
    ,M.MATERIA_ID
    ,M.MATERIA
    ,L.GLEG_CFH_ID
    ,L.SIGLA
    ,L.LEG_CFH
    ,C.ANO
    ,C.BIMESTRE
    ,C.AVALIACAO
    ,I.SITU_FINAL
    ,I.CARGA
    ,TM.EDEMPRESA_ID
    ,TM.GMUNICIPIO_ID
    ,TM.SECRETARIA_ID
    ,TM.GRADE_ID
    ,MT.DATA
    ,N.FALTAS
    ,GM1.CARGA_HORA

Thank you in advance.

Thank you.

sql-server

asked by anonymous 19.09.2014 / 14:34

1 answer

How to insert a data table into the database? Retrieve content between custom tags using RegEx

score 1 · Answer 1

To sum the hourly loads use SUM ().

To format the decimals you can try with: CONVERT (DECIMAL (10,2), [COLUMN])

And theoretically from what I understand, take the GM1.CARGA_HORA from GROUP BY.

But as the column load_horaria is calculated the error will occur: "Can not perform an aggregate function on an expression containing an aggregate or a subquery". So you have some alternatives to do:

 1. Fazer deste SELECT um SUB-SELECT, ou seja, montar um SELECT do resultado deste (agrupado por carga_hora) e neste novo select fazer o SUM().
 2. Criar uma view / tabela temporária com este select e obter o resultado a partir desta view/tabela temporária.