SELECT
p01.PIN,
p01.CLIENT NUMORGID,
p01.EMPLOYMENTSTARTDATE,
p01.EMPLOYMENTENDDATE,
P01.NAME,
1 AS [COUNT],
C02.NAME AS 'RANK',
PYH.ACCOUNTDIM3 AS 'DEPARTMENT NAME',
VES.NAME AS 'VESSEL NAME',
pyh.ENTRYFIELD4 AS 'CATEGORY',
pyh.PAYSCALETABLE,
pyh.PAYSCALECODE,
case when entrycode in (887, 886) and (ROUND (CALCULATEDAMOUNT1, 2) + ROUND (CALCULATEDAMOUNT3, 2) <> 0) then 1 else 0 end AS perscount,
case when entrycode in (887, 886) then CONVERT(FLOAT, CASE WHEN entryfield5 = '' THEN '0' ELSE REPLACE(entryfield5, ',', '.') END ) else 0 end AS 'NIs',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT1, 2) else 0 end AS 'EE Amount EUR',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT3, 2) else 0 end AS 'ER Amount EUR',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT1, 2) + ROUND (CALCULATEDAMOUNT3, 2) else 0 end AS 'TOTAL in EUR',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT1 * currencyrateforpayment, 2) else 0 end AS 'EE Amount USD',
case when entrycode in (887, 886) then ROUND (CALCULATEDAMOUNT3 * currencyrateforpayment, 2) else 0 end AS 'ER Amount USD',
case when entrycode in (887, 886) then (ROUND (CALCULATEDAMOUNT1, 2) + ROUND (CALCULATEDAMOUNT3, 2)) * currencyrateforpayment else 0 end AS 'TOTAL in USD',
case when entrycode in (1) then ROUND (Calculatedamount1, 2) else 0 end GrossEUR,
case when entrycode in (1) then ROUND (Calculatedamount1*currencyrateforpayment, 2) else 0 end GrossUSD,
Case When pyh.entryfield6 = '' or ENTRYFIELD6 like '%[a-z]%' then null else round(cast(replace(pyh.entryfield6,',','.') as float),2) end as 'Weekly Rate EUR',
Case When pyh.entryfield6 = '' or ENTRYFIELD6 like '%[a-z]%' then null else round(cast(replace(pyh.entryfield6,',','.') as float),2) end * pyh.currencyrateforpayment as 'Weekly Rate USD'
FROM pw001p01 p01
JOIN pw001pay pyh ON p01.pin = pyh.pin
LEFT JOIN pw001c02 c02 ON c02.code = pyh.rank
LEFT JOIN PWORG VES ON VES.NUMORGID=PYH.ACCOUNTDIM2
LEFT JOIN PWPSC000 PYS ON PYS.REGULATIVECODE = PYH.PAYSCALETABLE
LEFT JOIN PW001P1R P1R ON P1R.PIN=PYH.PIN AND P1R.RATENO=1
WHERE pyh.entrycode in (1, 887 ,886)
and exists(select pin from PW001PAY h2 where h2.pin=pyh.pin and h2.periodeused=pyh.periodeused and h2.entrycode in (887)) |
