SELECT P.PON_FUN, FUN_NOME,
        CASE EXTRACT(WEEKDAY FROM CAST(PON_ENTRADA AS DATE))
          WHEN 0 THEN LPAD(EXTRACT(DAY FROM PON_ENTRADA), 2, '0') || '/' || EXTRACT(MONTH FROM PON_ENTRADA) || ' - Dom'
          WHEN 1 THEN LPAD(EXTRACT(DAY FROM PON_ENTRADA), 2, '0') || '/' || EXTRACT(MONTH FROM PON_ENTRADA) || ' - Seg'
          WHEN 2 THEN LPAD(EXTRACT(DAY FROM PON_ENTRADA), 2, '0') || '/' || EXTRACT(MONTH FROM PON_ENTRADA) || ' - Ter'
          WHEN 3 THEN LPAD(EXTRACT(DAY FROM PON_ENTRADA), 2, '0') || '/' || EXTRACT(MONTH FROM PON_ENTRADA) || ' - Qua'
          WHEN 4 THEN LPAD(EXTRACT(DAY FROM PON_ENTRADA), 2, '0') || '/' || EXTRACT(MONTH FROM PON_ENTRADA) || ' - Qui'
          WHEN 5 THEN LPAD(EXTRACT(DAY FROM PON_ENTRADA), 2, '0') || '/' || EXTRACT(MONTH FROM PON_ENTRADA) || ' - Sex'
          WHEN 6 THEN LPAD(EXTRACT(DAY FROM PON_ENTRADA), 2, '0') || '/' || EXTRACT(MONTH FROM PON_ENTRADA) || ' - Sáb'
        END DATA,
        MARCACOES,
        DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00') TOTAL_HORAS,
        CAST(EXP_SEG_SEX AS TIME) EXPEDIENTE,
        IIF(DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00') > CAST(EXP_SEG_SEX AS TIME),
            DATEADD(SECOND, DATEDIFF(SECOND FROM CAST(EXP_SEG_SEX AS TIME) TO DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00')), TIME '00:00:00'),
            CAST('00:00:00' AS TIME)) SALDO_POSITIVO,
        IIF(DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00') > CAST(EXP_SEG_SEX AS TIME),
            CAST('00:00:00' AS TIME),
            DATEADD(SECOND, DATEDIFF(SECOND FROM DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00') TO CAST(EXP_SEG_SEX AS TIME)),TIME '00:00:00')) SALDO_NEGATIVO,
        IIF(DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00') > CAST(EXP_SEG_SEX AS TIME),
            '+ ' || SUBSTRING(DATEADD(SECOND, DATEDIFF(SECOND FROM CAST(EXP_SEG_SEX AS TIME) TO DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00')), TIME '00:00:00') FROM 1 FOR 8),
            '- ' || SUBSTRING(DATEADD(SECOND, DATEDIFF(SECOND FROM DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00') TO CAST(EXP_SEG_SEX AS TIME)),TIME '00:00:00') FROM 1 FOR 8)) SALDO_IMPRESSAO,
        IIF(DATEDIFF(SECOND FROM CAST(EXP_SEG_SEX AS TIME) TO DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00')) > 0,
            DATEDIFF(SECOND FROM CAST(EXP_SEG_SEX AS TIME) TO DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00')),
            0) TOTAL_HORA_EXTRA,
        IIF(DATEDIFF(SECOND FROM CAST(EXP_SEG_SEX AS TIME) TO DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00')) < 0,
            DATEDIFF(SECOND FROM DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00') TO CAST(EXP_SEG_SEX AS TIME)),
            0) TOTAL_ATRASO,
        CAST(LIST(PON_OBS) AS VARCHAR(100)) OBS,
        BANCO_HORAS_UTILIZADO,
        EXTRACT(YEAR FROM PON_ENTRADA) ANO, EXTRACT(MONTH FROM PON_ENTRADA) MES
 FROM (
   SELECT P.*, FUN_NOME,
           COALESCE(CASE EXTRACT(WEEKDAY FROM CAST(PON_ENTRADA AS DATE))
             WHEN 0 THEN DOM.EXP_TOTAL_HORAS
             WHEN 1 THEN SEG.EXP_TOTAL_HORAS
             WHEN 2 THEN TER.EXP_TOTAL_HORAS
             WHEN 3 THEN QUA.EXP_TOTAL_HORAS
             WHEN 4 THEN QUI.EXP_TOTAL_HORAS
             WHEN 5 THEN SEX.EXP_TOTAL_HORAS
             WHEN 6 THEN SAB.EXP_TOTAL_HORAS
           END, '00:00:00') EXP_SEG_SEX
   FROM PONTO_FUNCIONARIO P
   INNER JOIN FUNCIONARIOS ON ID_FUNCIONARIO = PON_FUN
   LEFT JOIN EXPEDIENTES_PONTO SEG ON SEG.ID_EXPEDIENTE_PONTO = FUN_EXP_SEG
   LEFT JOIN EXPEDIENTES_PONTO TER ON TER.ID_EXPEDIENTE_PONTO = FUN_EXP_TER
   LEFT JOIN EXPEDIENTES_PONTO QUA ON QUA.ID_EXPEDIENTE_PONTO = FUN_EXP_QUA
   LEFT JOIN EXPEDIENTES_PONTO QUI ON QUI.ID_EXPEDIENTE_PONTO = FUN_EXP_QUI
   LEFT JOIN EXPEDIENTES_PONTO SEX ON SEX.ID_EXPEDIENTE_PONTO = FUN_EXP_SEX
   LEFT JOIN EXPEDIENTES_PONTO SAB ON SAB.ID_EXPEDIENTE_PONTO = FUN_EXP_SAB
   LEFT JOIN EXPEDIENTES_PONTO DOM ON DOM.ID_EXPEDIENTE_PONTO = FUN_EXP_DOM
   WHERE CAST(PON_ENTRADA AS DATE) >= '01.10.2022' AND CAST(PON_SAIDA AS DATE) <= '31.10.2022'
     AND PON_BANCO_HORAS IS NULL
     AND PON_FUN = 584
   ORDER BY PON_ENTRADA
   ) P
 INNER JOIN (
   SELECT
     DISTINCT PON_FUN,
     CAST(PON_ENTRADA AS DATE) DATA,
     (SELECT
        RESULTADO
      FROM FBLIST('SELECT COALESCE(SUBSTRING(CAST(PON_ENTRADA AS TIME) FROM 1 FOR 8), ''FALTA'') || '' - '' ||
                          COALESCE(SUBSTRING(CAST(PON_SAIDA AS TIME) FROM 1 FOR 8), ''FALTA'')
                   FROM PONTO_FUNCIONARIO
                   WHERE PON_FUN = ' || F.PON_FUN || '
                   AND CAST(PON_ENTRADA AS DATE) = ''' || CAST(F.PON_ENTRADA AS DATE) || '''
                   ORDER BY PON_ENTRADA',
                  ' - ')) MARCACOES
   FROM PONTO_FUNCIONARIO F
   WHERE CAST(PON_ENTRADA AS DATE) >= '01.10.2022' AND CAST(PON_SAIDA AS DATE) <= '31.10.2022'
     AND PON_BANCO_HORAS IS NULL AND PON_FUN = 584
 ) X ON X.PON_FUN = P.PON_FUN AND X.DATA = CAST(P.PON_ENTRADA AS DATE)
 LEFT JOIN (
   SELECT PON_FUN FUNCIONARIO, DATEADD(SECOND, SUM(DATEDIFF(SECOND, TIME '00:00:00', PON_TOTAL)), TIME '00:00:00') BANCO_HORAS_UTILIZADO
   FROM PONTO_FUNCIONARIO
   WHERE CAST(PON_ENTRADA AS DATE) >= '01.10.2022' AND CAST(PON_SAIDA AS DATE) <= '31.10.2022'
     AND PON_BANCO_HORAS = 'S'
     AND PON_FUN = 584
     GROUP BY PON_FUN) ON FUNCIONARIO = P.PON_FUN
 GROUP BY PON_FUN, FUN_NOME, DATA, MARCACOES, EXP_SEG_SEX,
          BANCO_HORAS_UTILIZADO, ANO, MES
 ORDER BY PON_FUN, ANO, MES, DATA