Calcular Efetivo Mes Anterior

[Henrique oliveira]

Prezados bom dia,

Preciso melhorar a SQL de modo que traga o total de admitidos no mes anterior para calcular o Turn over.Segue a feita.

SELECT COUNT(PFUNC.NOME) QNT_ADM

FROM PFUNC

WHERE PFUNC.CODFILIAL =:ESPELHO#1

AND PFUNC.CODSITUACAO IN ('A','F')

GROUP BY PFUNC.CODFILIAL

Obrigado.

[Henrique oliveira]

Bom dia,

ALguém pode ajudar ??? É muito URGENTE...Obrigado.

[João Rodrigo]

Bom dia,

ALguém pode ajudar ??? É muito URGENTE...Obrigado.

veja se assim te ajuda:

SELECT COUNT(PFUNC.NOME) QNT_ADM
FROM PFUNC
WHERE PFUNC.CODFILIAL =:ESPELHO#1
AND PFUNC.CODSITUACAO IN ('A','F')
And
RIGHT(CONVERT(VARCHAR(10), DATAADMISSAO, 103), 7) = RIGHT(CONVERT(VARCHAR(10), dateadd(MM,(-1),getdate()), 103), 7)
GROUP BY PFUNC.CODFILIAL

[Henrique oliveira]

veja se assim te ajuda:

SELECT COUNT(PFUNC.NOME) QNT_ADM
FROM PFUNC
WHERE PFUNC.CODFILIAL =:ESPELHO#1
AND PFUNC.CODSITUACAO IN ('A','F')
And
RIGHT(CONVERT(VARCHAR(10), DATAADMISSAO, 103), 7) = RIGHT(CONVERT(VARCHAR(10), dateadd(MM,(-1),getdate()), 103), 7)
GROUP BY PFUNC.CODFILIAL

Bom dia,

A SQL retorna este erro abaixo:O Banco é Oracle.Obrigado.

ORA-00936: missing expression

Código da Informação: [98f4e2a0e6d84ed8fc0c1238f25a6d5f]

[João Rodrigo]

Bom dia,

tente assim:

SELECT COUNT(PFUNC.NOME) QNT_ADM
FROM PFUNC
WHERE PFUNC.CODFILIAL =:ESPELHO#1
AND PFUNC.CODSITUACAO IN ('A','F')
And


EXTRACT(YEAR, DATAADMISSAO) = EXTRACT(YEAR, add_months(CURRENT_DATE,(-1)))
AND
EXTRACT(MONTH, DATAADMISSAO) = EXTRACT(MONTH, add_months(CURRENT_DATE,(-1)))
GROUP BY PFUNC.CODFILIAL

ou

SELECT COUNT(PFUNC.NOME) QNT_ADM
FROM PFUNC
WHERE PFUNC.CODFILIAL =:ESPELHO#1
AND PFUNC.CODSITUACAO IN ('A','F')
And
TO_CHAR(DATAADMISSAO, 'MM-YYYY') = TO_CHAR(add_months(CURRENT_DATE,(-1)), 'MM-YYYY')
GROUP BY PFUNC.CODFILIAL

[VINICIUS REGO]

Eu uso esse

Ai você pode acrescentar o filtro que quiser!

SELECT gcoligada.codcoligada as 'empresa',

gcoligada.nome, 'QUADRO DE PESSOAL INICIO MES' AS 'POSIÇÃO',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-01-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-01-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-01-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-01-01'))))) AND (DTTRANSFERENCIA > '2012-01-01'))) as 'JAN',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-02-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-02-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-02-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-02-01'))))) AND (DTTRANSFERENCIA > '2012-02-01'))) as 'FEV',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-03-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-03-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-03-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-03-01'))))) AND (DTTRANSFERENCIA > '2012-03-01'))) as 'MAR',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-04-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-04-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-04-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-04-01'))))) AND (DTTRANSFERENCIA > '2012-04-01'))) as 'ABR',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-05-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-05-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-05-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-05-01'))))) AND (DTTRANSFERENCIA > '2012-05-01'))) AS 'MAI',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-06-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-06-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-06-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-06-01'))))) AND (DTTRANSFERENCIA > '2012-06-01'))) AS 'JUN',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-07-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-07-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-07-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-07-01'))))) AND (DTTRANSFERENCIA > '2012-07-01'))) AS 'JUL',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-08-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-08-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-08-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-08-01'))))) AND (DTTRANSFERENCIA > '2012-08-01'))) AS 'AGO',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-09-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-09-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-09-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-09-01'))))) AND (DTTRANSFERENCIA > '2012-09-01'))) AS 'SET',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-10-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-10-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-10-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-10-01'))))) AND (DTTRANSFERENCIA > '2012-10-01'))) AS 'OUT',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-11-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-11-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-11-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-11-01'))))) AND (DTTRANSFERENCIA > '2012-11-01'))) AS 'NOV',

(SELECT COUNT(*) FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-12-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-12-01'))) AND CHAPA NOT IN(SELECT CHAPA FROM PFUNC WHERE CHAPA IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATAADMISSAO < '2012-12-01') AND CHAPA NOT IN((SELECT CHAPA FROM PFUNC WHERE PFUNC.codcoligada=gcoligada.CODcoligada AND (DATADEMISSAO < '2012-12-01'))))) AND (DTTRANSFERENCIA > '2012-12-01'))) AS 'DEZ'

FROM gcoligada

WHERE CODCOLIGADA >= '1'

GROUP BY codcoligada, gcoligada.nome

Editado 15 de Abril de 2013 por VINICIUS REGO

[João Rodrigo]

Vinicius,

desculpe "meter o dedo" no seu sql, mas alterei o seu código utilizando uma técnica de pivô, apenas para SQLServer :

SELECT CODCOLIGADA,NOME,
JAN = sum(CASE WHEN mes=1 THEN ADMITIDOS ELSE 0 END),
FEV = sum(CASE WHEN mes=2 THEN ADMITIDOS ELSE 0 END),
MAR = sum(CASE WHEN mes=3 THEN ADMITIDOS ELSE 0 END),
ABR = sum(CASE WHEN mes=4 THEN ADMITIDOS ELSE 0 END),
MAI = sum(CASE WHEN mes=5 THEN ADMITIDOS ELSE 0 END),
JUN = sum(CASE WHEN mes=6 THEN ADMITIDOS ELSE 0 END),
JUL = sum(CASE WHEN mes=7 THEN ADMITIDOS ELSE 0 END),
AGO = sum(CASE WHEN mes=8 THEN ADMITIDOS ELSE 0 END),
[sET] = sum(CASE WHEN mes=9 THEN ADMITIDOS ELSE 0 END),
[OUT] = sum(CASE WHEN mes=10 THEN ADMITIDOS ELSE 0 END),
NOV = sum(CASE WHEN mes=11 THEN ADMITIDOS ELSE 0 END),
DEZ = sum(CASE WHEN mes=12 THEN ADMITIDOS ELSE 0 END)
FROM(
SELECT GCOLIGADA.CODCOLIGADA,GCOLIGADA.NOME, datepart(Month, DATAADMISSAO) Mes, COUNT(*) ADMITIDOS
FROM GCOLIGADA INNER JOIN PFUNC ON PFUNC.CODCOLIGADA = GCOLIGADA.CODCOLIGADA
WHERE year(DATAADMISSAO)=2000
GROUP BY GCOLIGADA.CODCOLIGADA,GCOLIGADA.NOME, datepart(Month, DATAADMISSAO)
)A
GROUP BY CODCOLIGADA,NOME

[VINICIUS REGO]

show... ficou melhor ainda!

[Henrique oliveira]

Bom dia,

tente assim:

SELECT COUNT(PFUNC.NOME) QNT_ADM
FROM PFUNC
WHERE PFUNC.CODFILIAL =:ESPELHO#1
AND PFUNC.CODSITUACAO IN ('A','F')
And


EXTRACT(YEAR, DATAADMISSAO) = EXTRACT(YEAR, add_months(CURRENT_DATE,(-1)))
AND
EXTRACT(MONTH, DATAADMISSAO) = EXTRACT(MONTH, add_months(CURRENT_DATE,(-1)))
GROUP BY PFUNC.CODFILIAL

ou

SELECT COUNT(PFUNC.NOME) QNT_ADM
FROM PFUNC
WHERE PFUNC.CODFILIAL =:ESPELHO#1
AND PFUNC.CODSITUACAO IN ('A','F')
And
TO_CHAR(DATAADMISSAO, 'MM-YYYY') = TO_CHAR(add_months(CURRENT_DATE,(-1)), 'MM-YYYY')
GROUP BY PFUNC.CODFILIAL

Boa tarde João Rodrigo,

A SQL ZERA OS VALORES...O que pode ser ?

Obrigado.

[João Rodrigo]

Pode mandar um print?