I have a query where you are doing a pivot and it is bringing the correct records per month, however I would like to validate the amount of records per month that shows only employees who have >= 16
in every month!
Today the query is 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')
)
)