Usamos cookies para medir audiência e melhorar sua experiência. Você pode aceitar ou recusar a qualquer momento. Veja sobre o iMasters.
Bom dia pessoal,
estou com uma grande dúvida em fazer um processo para cálculo de datas.
Tenho a seguinte experiência de um funcionário:
http://social.msdn.microsoft.com/Forums/getfile/283095
Gostaria de calcular o tempo de experiência do funcionário em meses considerando as intercessões.
Conforme calculado abaixo:
http://social.msdn.microsoft.com/Forums/getfile/283097
Como o intervalo das linhas 2 e 3 estão contidos no intervalo da linha 1 devemos considerar somente que o funcionário tem 26 meses de experiência.
Como fazer esse cálculo no SQL, uma vez que tenho que verificar estas intercessões?
Pode ser que resolve, mas primeiro faz o seguinte: ((Ano fim - ano inicio) * 12) + (mês fim - mês Inicio) isso vai te dar em mês .
e como faço a comparação para ver se um intervalo está contido em outro?
quanto ao cálculo do tempo em meses tranquilo, o que pega mesmo é a questão de ver qual intervalo está contido em outro.
O intervalo da linha 1 contém os intervalos das linhas 2 e 3. Como faço para saber isso via código?
Use sua query como tabela virtual ou view e faça a comparação com ela mesma
Motta, vc pode me dar algum exemplo?
Publique sua query
SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
fa.ano_fim,
fa.mes_fim,
fa.ind_atividade_atualFROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401' )
ORDER BY rh.nom_rh
tenta algo assim :
select *
from
( SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
(fa.ano_inicio * 100 + ano_inicio) perinicio,
fa.ano_fim,
fa.mes_fim,
(fa.ano_fim * 100 + ano_fim) perfim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401' ) ) T1,
( SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
(fa.ano_inicio * 100 + ano_inicio) perinicio,
fa.ano_fim,
fa.mes_fim,
(fa.ano_fim * 100 + ano_fim) perfim,
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401' ) ) T2
where ( (t1.perinicio between t2.perinico and t2.perfim))
or
(t1.perfim between t2.perinico and t2.perfim) )Motta obrigado pela ajuda... estou quase chegando ao resultado, agora só falta somar as experiências
veja o SQL:
SELECT DISTINCT t1.nom_rh,
t1.ano_inicio AS t1_ano_inicio,
t1.ano_fim AS t1_ano_fim,
T1.ano_fim - T1.ano_inicio AS DIFERENCAFROM (SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
CASE
WHEN fa.ano_fim = '0' THEN Year(Getdate())
ELSE fa.ano_fim
END AS ano_fim,
CASE
WHEN fa.mes_fim = '0' THEN Month(Getdate())
ELSE fa.mes_fim
END AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401' )) T1,
(SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
CASE
WHEN fa.ano_fim = '0' THEN Year(Getdate())
ELSE fa.ano_fim
END AS ano_fim,
CASE
WHEN fa.mes_fim = '0' THEN Month(Getdate())
ELSE fa.mes_fim
END AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401' )) T2SELECT DISTINCT t1.nom_rh,
t1.ano_inicio AS t1_ano_inicio,
t1.ano_fim AS t1_ano_fim,
T1.ano_fim - T1.ano_inicioFROM (SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
CASE
WHEN fa.ano_fim = '0' THEN Year(Getdate())
ELSE fa.ano_fim
END AS ano_fim,
CASE
WHEN fa.mes_fim = '0' THEN Month(Getdate())
ELSE fa.mes_fim
END AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401' )) T1,
(SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
CASE
WHEN fa.ano_fim = '0' THEN Year(Getdate())
ELSE fa.ano_fim
END AS ano_fim,
CASE
WHEN fa.mes_fim = '0' THEN Month(Getdate())
ELSE fa.mes_fim
END AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401' )) T2agora só falta somar as experiências
Um sum na query que vc montou não vai ?
Veja como fiz:
SELECT t3.seq_rh,
t3.cod_pessoa,
t3.nom_rh,
Sum(dif) Total_em_Anos_Exp_Graduacao
FROM (SELECT DISTINCT t1.seq_rh,
t1.cod_pessoa,
t1.nom_rh,
--t1.ano_inicio AS t1_ano_inicio,
--t1.ano_fim AS t1_ano_fim,
T1.ano_fim - T1.ano_inicio AS DIF
/*t2.ano_inicio as t2_ano_inicio,
t2.ano_fim as t2_ano_fim*/
FROM (SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
CASE
WHEN Isnull(fa.ano_fim, '') <> ''
AND fa.ano_inicio <= fa.ano_fim
AND fa.ano_fim <> '0'
AND fa.ano_fim <> 0
AND fa.ano_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.ano_fim
ELSE Year(Getdate())
END AS ano_fim,
CASE
WHEN Isnull(fa.mes_fim, '') <> ''
AND fa.mes_inicio <= fa.mes_fim
AND fa.mes_fim <> '0'
AND fa.mes_fim <> 0
AND fa.mes_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
Month(Getdate())
ELSE fa.mes_fim
END AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401'
)) T1,
(SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
CASE
WHEN Isnull(fa.ano_fim, '') <> ''
AND fa.ano_inicio <= fa.ano_fim
AND fa.ano_fim <> '0'
AND fa.ano_fim <> 0
AND fa.ano_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.ano_fim
ELSE Year(Getdate())
END AS ano_fim,
CASE
WHEN Isnull(fa.mes_fim, '') <> ''
AND fa.mes_inicio <= fa.mes_fim
AND fa.mes_fim <> '0'
AND fa.mes_fim <> 0
AND fa.mes_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
Month(Getdate())
ELSE fa.mes_fim
END AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401'
)) T2
WHERE /*t1.nom_rh='Adriana Gomes Dickman' and t2.nom_rh='Adriana Gomes Dickman'
and */
( ( t1.ano_inicio NOT BETWEEN t2.ano_inicio AND t2.ano_fim )
AND ( t1.ano_fim NOT BETWEEN t2.ano_inicio AND t2.ano_fim ) )
--AND ( T2.ano_inicio= t1.ano_inicio AND t2.ano_fim= t1.ano_fim )
AND t1.cod_pessoa = T2.cod_pessoa
--GROUP BY T1.nom_rh
--ORDER BY t1.nom_rh
EXCEPT
SELECT DISTINCT t1.seq_rh,
t1.cod_pessoa,
t1.nom_rh,
--t1.ano_inicio AS t1_ano_inicio,
--t1.ano_fim AS t1_ano_fim,
T1.ano_fim - T1.ano_inicio AS DIF
/*, t2.ano_inicio as t2_ano_inicio,
t2.ano_fim as t2_ano_fim*/
FROM (SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
CASE
WHEN Isnull(fa.ano_fim, '') <> ''
AND fa.ano_inicio <= fa.ano_fim
AND fa.ano_fim <> '0'
AND fa.ano_fim <> 0
AND fa.ano_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.ano_fim
ELSE Year(Getdate())
END AS ano_fim,
CASE
WHEN Isnull(fa.mes_fim, '') <> ''
AND fa.mes_inicio <= fa.mes_fim
AND fa.mes_fim <> '0'
AND fa.mes_fim <> 0
AND fa.mes_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
Month(Getdate())
ELSE fa.mes_fim
END AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401'
)) T1,
(SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
fa.ano_inicio,
fa.mes_inicio,
CASE
WHEN Isnull(fa.ano_fim, '') <> ''
AND fa.ano_inicio <= fa.ano_fim
AND fa.ano_fim <> '0'
AND fa.ano_fim <> 0
AND fa.ano_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.ano_fim
ELSE Year(Getdate())
END AS ano_fim,
CASE
WHEN Isnull(fa.mes_fim, '') <> ''
AND fa.mes_inicio <= fa.mes_fim
AND fa.mes_fim <> '0'
AND fa.mes_fim <> 0
AND fa.mes_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
Month(Getdate())
ELSE fa.mes_fim
END AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401'
)) T2
WHERE /*t1.nom_rh='Adriana Gomes Dickman' and t2.nom_rh='Adriana Gomes Dickman'
and */
( ( t1.ano_inicio BETWEEN t2.ano_inicio AND t2.ano_fim )
AND ( t1.ano_fim BETWEEN t2.ano_inicio AND t2.ano_fim ) )
AND ( ( T2.ano_inicio <> t1.ano_inicio )
OR ( t2.ano_fim <> t1.ano_fim ) )
AND t1.cod_pessoa = T2.cod_pessoa
--GROUP BY T1.nom_rh, T1.ano_fim, T1.ano_inicio
)T3Ainda falta considerar os meses, faço isso logo e posto na sequencia.
Fechado... agora sim...
--CONSIDERDAR NO CÁLCULO OS MESES SOMANDO TUDO E DIVIDINDO POR 12.
SELECT t3.seq_rh,
t3.cod_pessoa,
t3.nom_rh,
--((SUM(DIF_ANO)* 12)+ SUM(DIF_MES)/12 Total_em_Anos_Exp_Graduacao
SUM(DIF) AS Total_em_Anos_Exp_Graduacao
FROM (SELECT DISTINCT t1.seq_rh,
t1.cod_pessoa,
t1.nom_rh,
--t1.ano_inicio AS t1_ano_inicio,
--t1.ano_fim AS t1_ano_fim,
(((T1.ano_fim - T1.ano_inicio)*12)+ (T1.mes_fim - T1.mes_inicio))/12 AS DIF
/*t2.ano_inicio as t2_ano_inicio,
t2.ano_fim as t2_ano_fim*/
FROM (SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
CAST(fa.ano_inicio AS float) AS ano_inicio,
CAST (fa.mes_inicio AS float) AS mes_inicio,
CAST(CASE
WHEN Isnull(fa.ano_fim, '') <> ''
AND fa.ano_inicio <= fa.ano_fim
AND fa.ano_fim <> '0'
AND fa.ano_fim <> 0
AND fa.ano_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.ano_fim
ELSE Year(Getdate())
END AS float)AS ano_fim,
CAST(CASE
WHEN Isnull(fa.mes_fim, '') <> ''
AND fa.mes_inicio <= fa.mes_fim
AND fa.mes_fim <> '0'
AND fa.mes_fim <> 0
AND fa.mes_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.mes_fim
ELSE Month(Getdate())
END AS float) AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401'
)) T1,
(SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
CAST(fa.ano_inicio AS float) AS ano_inicio,
CAST (fa.mes_inicio AS float) AS mes_inicio,
CAST(CASE
WHEN Isnull(fa.ano_fim, '') <> ''
AND fa.ano_inicio <= fa.ano_fim
AND fa.ano_fim <> '0'
AND fa.ano_fim <> 0
AND fa.ano_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.ano_fim
ELSE Year(Getdate())
END AS float) AS ano_fim,
CAST(CASE
WHEN Isnull(fa.mes_fim, '') <> ''
AND fa.mes_inicio <= fa.mes_fim
AND fa.mes_fim <> '0'
AND fa.mes_fim <> 0
AND fa.mes_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.mes_fim
ELSE Month(Getdate())
END AS float) AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401'
)) T2
WHERE /*t1.nom_rh='Adriana Gomes Dickman' and t2.nom_rh='Adriana Gomes Dickman'
and */
( ( t1.ano_inicio NOT BETWEEN t2.ano_inicio AND t2.ano_fim )
AND ( t1.ano_fim NOT BETWEEN t2.ano_inicio AND t2.ano_fim ) )
--AND ( T2.ano_inicio= t1.ano_inicio AND t2.ano_fim= t1.ano_fim )
AND t1.cod_pessoa = T2.cod_pessoa
--GROUP BY T1.nom_rh
--ORDER BY t1.nom_rh
EXCEPT
SELECT DISTINCT t1.seq_rh,
t1.cod_pessoa,
t1.nom_rh,
--t1.ano_inicio AS t1_ano_inicio,
--t1.ano_fim AS t1_ano_fim,
(((T1.ano_fim - T1.ano_inicio)*12)+ (T1.mes_fim - T1.mes_inicio))/12 AS DIF
/*, t2.ano_inicio as t2_ano_inicio,
t2.ano_fim as t2_ano_fim*/
FROM (SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
CAST(fa.ano_inicio AS float) AS ano_inicio,
CAST (fa.mes_inicio AS float) AS mes_inicio,
CAST(CASE
WHEN Isnull(fa.ano_fim, '') <> ''
AND fa.ano_inicio <= fa.ano_fim
AND fa.ano_fim <> '0'
AND fa.ano_fim <> 0
AND fa.ano_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.ano_fim
ELSE Year(Getdate())
END AS float) AS ano_fim,
CAST(CASE
WHEN Isnull(fa.mes_fim, '') <> ''
AND fa.mes_inicio <= fa.mes_fim
AND fa.mes_fim <> '0'
AND fa.mes_fim <> 0
AND fa.mes_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.mes_fim
ELSE Month(Getdate())
END AS float) AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401'
)) T1,
(SELECT rh.seq_rh,
rh.cod_pessoa,
rh.nom_rh,
CAST(fa.ano_inicio AS float) AS ano_inicio,
CAST (fa.mes_inicio AS float) AS mes_inicio,
CAST(CASE
WHEN Isnull(fa.ano_fim, '') <> ''
AND fa.ano_inicio <= fa.ano_fim
AND fa.ano_fim <> '0'
AND fa.ano_fim <> 0
AND fa.ano_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.ano_fim
ELSE Year(Getdate())
END AS FLOAT) AS ano_fim,
CAST(CASE
WHEN Isnull(fa.mes_fim, '') <> ''
AND fa.mes_inicio <= fa.mes_fim
AND fa.mes_fim <> '0'
AND fa.mes_fim <> 0
AND fa.mes_fim <> ''
AND fa.ind_atividade_atual <> 'ATUAL' THEN
fa.mes_fim
ELSE Month(Getdate())
END AS FLOAT) AS mes_fim,
fa.ind_atividade_atual
FROM cvl.dbo.recurso_humano AS rh
LEFT OUTER JOIN cvl.dbo.funcao_atividade AS fa
ON rh.seq_rh = fa.seq_rh
AND fa.cod_natureza_atividade IN ( '401'
)) T2
WHERE /*t1.nom_rh='Adriana Gomes Dickman' and t2.nom_rh='Adriana Gomes Dickman'
and */
( ( t1.ano_inicio BETWEEN t2.ano_inicio AND t2.ano_fim )
AND ( t1.ano_fim BETWEEN t2.ano_inicio AND t2.ano_fim ) )
AND ( ( T2.ano_inicio <> t1.ano_inicio )
OR ( t2.ano_fim <> t1.ano_fim ) )
AND t1.cod_pessoa = T2.cod_pessoa
--GROUP BY T1.nom_rh, T1.ano_fim, T1.ano_inicio
)T3
Use o Cast para converter o ano e mes (use dia 1) para data.
Use o DATEDIFF para calcular esta diferença.