Select
p01.Pin,
p01.Name,
z.Company,
z.Employer,
z.Vessel as 'Vessel name',
z.EC as 'EC Number and Text',
z.[Period] as 'Period',
z.Basis,
z.[Basis from A-melding],
z.Basis-z.[Basis from A-melding] as 'A-melding diff',
z.[Basis from A-melding included],
P01.Client Numorgid,
P01.Employmentstartdate,
P01.Employmentenddate
From
(SELECT
pyh.Pin ,
c.Name as 'Company',
e.Name as 'Employer',
v.Name as 'Vessel',
pyh.Periodeused as 'Period',
convert(varchar,pyh.Entrycode) + ' ' + txt.text as 'EC',
case when pyh.calculatedamount6<>0 and pyh.entrycode not in (163,168) THEN (pyh.calculatedamount2*100/14.1)/1.033
when pyh.calculatedamount6<>0 and pyh.entrycode in (163,168) THEN (pyh.calculatedamount2*100/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')
then pyh.CALCULATEDAMOUNT1
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L66' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT6
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L67' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT7
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L68' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT8
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L69' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT9
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L70' and AGA = 'Y')
then pyh.CALCULATEDAMOUNT10
else 0.00
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L71' and AGA = 'Y')
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 pyh.CALCULATEDAMOUNT1 != 0.00
then 'L61 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L66' and AGA = 'Y') 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 pyh.CALCULATEDAMOUNT7 != 0.00
then 'L67 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L68' and AGA = 'Y') 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 pyh.CALCULATEDAMOUNT9 != 0.00
then 'L69 '
else ''
end +
case when pyh.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L70' and AGA = 'Y') 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 pyh.CALCULATEDAMOUNT11 != 0.00
then 'L71'
else ''
end
as 'Basis from A-melding included'
from pw001pyh pyh
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
--left join pw001pyh aga on aga.pin = pyh.pin and aga.periodeused = pyh.PERIODEUSED and aga.entrycode in (select ENTRYCODE from PWEDAGDEF where PFIELDNO='L61' and AGA = 'Y')
where pyh.entrycode not in (146) and pyh.calculatedamount2<>0
) Z
left join pw001p01 p01 on p01.pin= z.pin