/
Leave Pay Statement
Leave Pay Statement
- Sigrid Kviteberg
- Daria Chebotaieva
Owned by Sigrid Kviteberg, created
Summary | This view gives a Leave Pay statement, calculating the difference between Leave pay accumulated and what has been accounted for. |
Keywords | payroll, leave pay |
Description
This view gives a Leave Pay statement, calculating the difference between Leave pay accumulated and what has been accounted for.
SQL Statement
select
p01.PIN,
emp.Name as Employer,
empacc.Name as 'Employer ACC',
p0c.Periode as 'Accumulator period',
p0c.Acc10 as '10 Basis Holiday Pay',
(IB.Acc1012/100) as 'Leave Pay 12%',
CASE
WHEN (IB.Acc102.3/100)>14683.06 THEN '14683.06'
ELSE (IB.Acc102.3/100)
END as 'Leave Pay o/60_1',
CASE
WHEN (P0c.Acc102.3/100)>14683.06 THEN '14683.06'
ELSE (P0c.Acc102.3/100)
END as 'Leave Pay o/60',
(IB.Acc1012/100) + CASE
WHEN (IB.Acc102.3/100)>14683.06 THEN '14683.06'
ELSE (IB.Acc102.3/100)
END as 'Total Leave Pay Calculated',
pyh.calculatedamount3 as 'Leave Pay Accumulated',
((IB.Acc1012/100)+ CASE
WHEN (IB.Acc102.3/100)>14683.06 THEN '14683.06'
ELSE (IB.Acc10*2.3/100)
END)-pyh.calculatedamount3 as 'Difference',
p01.periode as 'Reporting period',
p01.Birthdate as 'Birthdate',
CONVERT(int,ROUND(DATEDIFF(hour,p01.Birthdate,GETDATE())/8766.0,0)) AS 'Age',
p01.client numorgid,
p01.employmentstartdate,
p01.employmentenddate
from
(select *
from (select distinct periode from pw001p0c where periode>202112 and periode<202200) p
, pw001p01
) p01
left join (select pin, employer, sum(calculatedamount3) calculatedamount3 from pw001pyh where periodeused > 202100 and periodeused < 202200 group by pin, employer) pyh on pyh.pin=p01.pin
left join pw001p0c p0c on p0c.pin=p01.pin and p0c.periode=(select max(periode) from pw001p0c p0c2 where p0c2.pin=p01.pin and p0c2.periode<=p01.periode) and p0c.employer=pyh.employer
left join pw001p0c IB on IB.pin=p01.pin and IB.periode=(select max(periode) from pw001p0c IB2 where IB2.pin=p01.pin and IB2.periode<p01.periode) and ib.employer=pyh.employer
left join pworg emp on emp.numorgid=pyh.employer
left join pworg empacc on empacc.numorgid=p0c.employerField Specification
Data Fields
10 Basis Holiday Pay
Leave Pay 12%
Leave Pay o/60
Total Leave Pay Calculated
Leave Pay Accumulated
Difference
Column Fields
Reporting Period
Row Fields
Employer
PIN
Age
