Scritpt aumento SalarialContrução Civil Pesada 05/2012

[jaime]

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