Calcula valor do salário escalonado, conforme circular do grupo de empresas da contrução civil pesada.
select
b.chapa,
b.nome,
b.codsituacao,
b.codsecao,
c.descricao,
B.JORNADAMENSAL /60 JORNADA,
f.nome funcao,
g.nome as SINDICATO,
b.codrecebimento,
b.salario,
case
when b.salario * 1.1050 < 1069.20 then 1069.20 / (b.jornadamensal/60)
when (b.salario <=8000 and b.salario * 1.1050 > 1069.20) and b.codrecebimento='M' then (b.salario * 1.1050) / (b.jornadamensal/60)
when b.salario <=8000 and b.codrecebimento='H' then ((b.salario/(b.jornadamensal/60)) * 1.1050)
when b.salario <=10000 and b.codrecebimento='M' then ((840 + (b.salario - 8000)* 0.08) + b.salario) / (b.jornadamensal/60)
when b.salario >10000 and b.codrecebimento='M' then ((1000 + (b.salario - 10000)* 0.051042) + b.salario) / (b.jornadamensal/60)
else 0 end as HORA,
case
when b.salario*1.1050 < 1069.20 then 1069.20
when (b.salario <=8000 and b.salario * 1.1050 > 1069.20) and b.codrecebimento='M' then b.salario * 1.1050
when b.salario <=8000 and b.codrecebimento='H' then ((b.salario/(b.jornadamensal/60)) * 1.1050) * (b.jornadamensal/60)
when b.salario <=10000 and b.codrecebimento='M' then ((840 + (b.salario - 8000)* 0.08) + b.salario)
when b.salario >10000 and b.codrecebimento='M' then (1000 + (b.salario - 10000)* 0.051042) + b.salario
else 0 end as AUMENTO
from
pfrateiofixo a left join pfunc b on
a.codcoligada = b.codcoligada and
a.chapa = b.chapa
left join psecao c on
b.codcoligada = c.codcoligada and
b.codsecao = c.codigo
left join pfuncao f on
b. codcoligada = f.codcoligada and
b.codfuncao = f.codigo
left join psindic g on
b. codcoligada = g.codcoligada and
b.codsindicato = g.codigo
where
b.codsituacao <> 'D' and
b.codsindicato not in ('011')
ORDER BY 5