SELECT
P01.Pin ,
p01.Name,
c.Name as 'Company',
e.Name as 'Employer',
v.Name as 'Vessel Name',
pyh.Periodeused as Period,
convert(varchar,pyh.Entrycode) + ' ' + txt.text as 'EC Number and Text',
case when pyh.entrycode>349 and pyh.entrycode<400 THEN 0.00
when pyh.calculatedamount6<>0 and pyh.entrycode not in (163,168) THEN (pyh.calculatedamount2100/14.1)/1.033
when pyh.calculatedamount6<>0 and pyh.entrycode in (163,168) THEN (pyh.calculatedamount2100/14.1)
when pyh.calculatedamount6=0 then (pyh.calculatedamount2*100/14.1) end as Basis,
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L61' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT1
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L66' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT6
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L67' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT7
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L68' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT8
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L69' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT9
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L70' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT10
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L71' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard')
then pyh.CALCULATEDAMOUNT11
else 0.00
end
as 'Basis from A-melding',
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L61' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard') and pyh.CALCULATEDAMOUNT1 != 0.00
then 'L61 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L66' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard') and pyh.CALCULATEDAMOUNT6 != 0.00
then 'L66 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L67' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard') and pyh.CALCULATEDAMOUNT7 != 0.00
then 'L67 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L68' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard') and pyh.CALCULATEDAMOUNT8 != 0.00
then 'L68 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L69' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard') and pyh.CALCULATEDAMOUNT9 != 0.00
then 'L69 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L70' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard') and pyh.CALCULATEDAMOUNT10 != 0.00
then 'L70 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L71' and AGA = 'Y' and skatteogavgiftsregel<>'svalbard') and pyh.CALCULATEDAMOUNT11 != 0.00
then 'L71'
else ''
end
as 'Basis from A-melding included',
P01.Client Numorgid,
P01.Employmentstartdate,
P01.Employmentenddate
from pw001pyh pyh
left join pw001p01 p01 on p01.pin= pyh.pin
left join pyety001 txt on pyh.entrycode=txt.entrycode
left join pworg c on pyh.orgnumid=c.numorgid
left join pworg e on pyh.employer=e.numorgid
left join pworg v on pyh.accountdim2=v.numorgid
left join pw001c02 c02 on pyh.rank=c02.code
where pyh.entrycode not in (146) and pyh.calculatedamount2<>0