Query creation using PIVOT in SQL

Navigation
1

I need to display the result of this query in columns, each row represents an employee point mark on the day, I'm trying to use the pivot, but I'm not getting it.

    
asked by anonymous 16.05.2018 / 01:18

1 answer

1

I was able to solve it, I created a row_number to number the beats that are 4, then I used them to create the columns and I used the pivot, it worked, thanks. 

SELECT  
CHAPA,
NOME,
SECAO,
DATA,
HORARIO_BASE,
HORAS_TRABALHADAS,
[1] ENTRADA,
[2] SAÍDA,
[3] ENTRADA,
[4] SAÍDA,      
ATRASO,
EXTRAEXECUTADO,
ABONO,
ADICIONALNOTURNO    

FROM 
(
    SELECT 
        ROW_NUMBER() OVER(PARTITION BY ABATFUN.DATA, ABATFUN.CHAPA ORDER BY 
ABATFUN.DATA) LINHA,
        PFUNC.CHAPA,
        PFUNC.NOME,
        PSECAO.DESCRICAO SECAO,
        ABATFUN.DATA,            
        dbo.UNIFAM_CONVMIN(BATIDA) BATIDA,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.BASE) HORARIO_BASE,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.HTRAB) HORAS_TRABALHADAS,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.ATRASO) ATRASO,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.EXTRAEXECUTADO) EXTRAEXECUTADO,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.ADICIONAL) ADICIONALNOTURNO,
        dbo.UNIFAM_CONVMIN(AAFHTFUN.ABONO) ABONO,
        AABONO.DESCRICAO MOTIVO_ABONO,
        CASE WHEN
            (SELECT CODAVISO FROM AAVISOCALCULADO WHERE CODCOLIGADA = 
AAFHTFUN.CODCOLIGADA AND CHAPA = AAFHTFUN.CHAPA AND DATAREFERENCIA = 
AAFHTFUN.DATA AND CODAVISO=1) = 1
            THEN 'MENOS DE 11HRS ENTRE AS JORNADAS' END INTERVALO_JORNADA,
        CASE WHEN
            (SELECT CODAVISO FROM AAVISOCALCULADO WHERE CODCOLIGADA = 
AAFHTFUN.CODCOLIGADA AND CHAPA = AAFHTFUN.CHAPA AND DATAREFERENCIA = 
AAFHTFUN.DATA AND CODAVISO=2) = 2
            THEN 'TRABALHOU MAIS DE 10HRS NO DIA' END 
TOTAL_HORAS_TRABALHADAS

    FROM 
        PFUNC   
            LEFT JOIN ABATFUN ON
            ABATFUN.CODCOLIGADA = PFUNC.CODCOLIGADA
            AND ABATFUN.CHAPA = PFUNC.CHAPA
            LEFT JOIN AAFHTFUN ON
            AAFHTFUN.CODCOLIGADA = PFUNC.CODCOLIGADA
            AND AAFHTFUN.CHAPA = PFUNC.CHAPA
            AND AAFHTFUN.DATA = ABATFUN.DATA
            INNER JOIN PSECAO ON
            PSECAO.CODCOLIGADA = PFUNC.CODCOLIGADA
            AND PSECAO.CODIGO  = PFUNC.CODSECAO
            LEFT JOIN AABONFUN ON
            AABONFUN.CODCOLIGADA = AAFHTFUN.CODCOLIGADA
            AND AABONFUN.CHAPA = AAFHTFUN.CHAPA
            AND AABONFUN.DATA = AAFHTFUN.DATA       
            LEFT JOIN AABONO ON
            AABONO.CODCOLIGADA = AABONFUN.CODCOLIGADA
            AND AABONO.CODIGO = AABONFUN.CODABONO   
    WHERE 
        PFUNC.CODCOLIGADA=1
        AND PFUNC.CODFILIAL=2
        AND PFUNC.CODTIPO<>'A'
        --AND PFUNC.CHAPA='200739' 
        AND ABATFUN.DATA BETWEEN '2018-03-21' AND '2018-04-20'

)TABELACONSULTA

PIVOT

(
    MAX(BATIDA)
    FOR [LINHA] IN ([1],[2],[3],[4])
) 
AS TABELAPIVOT


ORDER BY
DATA
,NOME
    
16.05.2018 / 15:15
Auto Cast with TypeScript? Python, increasing number of numbers