Determining value within a pivot

Asked

Viewed 62 times

0

I have a query in which you are making a pivot and she’s bringing in the correct records per month, but I’d like to validate the amount of records per month that show only employees who have >= 16 every month!

Today the query meets like this

SELECT 
*
FROM (
SELECT * FROM (
        SELECT   
            F.CHAPAFUNC,    
            F.NOMECOMPLETOFUNC,
      F.DTADMFUNC,      
            TO_CHAR(TO_DATE(D.DTDIGIT),'MM') DIAMES, 
      COUNT(DISTINCT(D.CODOCORR)) OCORRENCIA,
      COUNT(DISTINCT(D.CODOCORR)) OCORRENCIA1   ,      
            DECODE(REPLACE(COUNT(DISTINCT(DECODE(D.CODOCORR,D.CODOCORR,0))),1,1),1,'100%') VALOR_PREMIACAO  
        FROM 
            VW_FUNCIONARIOS F,
            FRQ_DIGITACAOMOVIMENTO D   
        WHERE  
            F.CODINTFUNC NOT IN 
                (
                SELECT   
                    F.CODINTFUNC      
                FROM    
                    VW_FUNCIONARIOS F,
                    FRQ_DIGITACAOMOVIMENTO D   
                WHERE 
                    D.CODINTFUNC = F.CODINTFUNC AND
                    D.CODOCORR IN (953,954,57,560,546,515,516,518,609,44) AND 
                    D.TIPODIGIT <> 'P' AND    
                    F.CODIGOEMPRESA IN (25)  AND
                    F.CODFUNCAO IN (1700) AND
                    F.SITUACAOFUNC = 'A' AND                
                    D.DTDIGIT BETWEEN TO_DATE ('01/01/2015','DD/MM/YYYY') AND TO_DATE ('31/03/2015','DD/MM/YYYY')
            )AND
                    D.CODINTFUNC = F.CODINTFUNC AND      
                    D.CODOCORR IN(951,956,960,997) AND 
                    D.TIPODIGIT <> 'P' AND    
                    F.CODIGOEMPRESA IN (25)  AND
                    F.CODFUNCAO IN (1700) AND     
                    F.SITUACAOFUNC = 'A' AND
          D.DUPLAFUNCAO = 'S' AND     
                  D.DTDIGIT BETWEEN TO_DATE ('01/01/2015','DD/MM/YYYY') AND TO_DATE ('31/03/2015','DD/MM/YYYY') and
          F.DTADMFUNC NOT BETWEEN    TO_DATE ('01/01/2015','DD/MM/YYYY') AND TO_DATE ('31/03/2015','DD/MM/YYYY') 
                    GROUP BY F.CHAPAFUNC,F.NOMECOMPLETOFUNC,D.CODOCORR,D.DTDIGIT,F.CODINTFUNC,F.DTADMFUNC          
)              
            PIVOT
                (
                    COUNT(OCORRENCIA)
                    FOR DIAMES IN  ('01' ,'02' ,'03','04','05','06','07','08','09','10','11','12')
                )   
)

1 answer

0

This query would bring something like:

F.CHAPAFUNC | F.NOMECOMPLETOFUNC | F.DTADMFUNC | DIAMES | OCORRENCIA | OCORRENCIA1 | VALOR_PREMIACAO
            |                    |             |        |            |             |
FUNC        | JOAO               | 10/11/2014  | 10     | 25         | 25          | 100%

Correct ?

And you want to validate what ?

I didn’t understand this excerpt.

que mostre apenas funcionários que tenha >= 16 em todos os meses!

Only the lines that OCCURED >=16 ?

Or just the lines that the month appears to 16 employees or more ?

Browser other questions tagged

You are not signed in. Login or sign up in order to post.