SELECT
P01.Pin ,
P01.Client Numorgid,
p01.Name,
P01.Employmentstartdate,
P01.Employmentenddate,
P01.Personalidno,
pyh.Periodeused as Period,
pyh.Entrycode as 'Entry Code',
c02.name as 'Rank',
txt.Text as 'Entry Code Name',
convert(varchar,pyh.Entrycode) + ' ' + txt.text as 'EC Number and Text',
ed.AGA as 'Employers Fee',
ed.ftrekk as 'Taxable',
pyh.Amount*Calcsignvalue as 'Amount',
pyh.Account,
da.Accounttext as 'Account Name',
pyh.Account + ' ' + da.Accounttext as 'Account Number and Name',
Calculatedamountfield as 'Count',
t.Ttext as 'EC Text',
v.Orgcode as 'Vessel Code',
v.Name as 'Vessel Name',
v.Orgcode + ' ' + v.Name as 'Vessel Code Name',
e.Name as 'Employer',
c.Name as 'OrganisationCompany'
from
(
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount1 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount1 account, 1 calculatedamountfield, ((entrycode/1000)*100) + 1 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount2 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount2 account, 2 calculatedamountfield, ((entrycode/1000)*100) + 2 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount3 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount3 account, 3 calculatedamountfield, ((entrycode/1000)*100) + 3 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount4 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount4 account, 4 calculatedamountfield, ((entrycode/1000)*100) + 4 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount5 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount5 account, 5 calculatedamountfield, ((entrycode/1000)*100) + 5 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount6 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount6 account, 6 calculatedamountfield, ((entrycode/1000)*100) + 6 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount7 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount7 account, 7 calculatedamountfield, ((entrycode/1000)*100) + 7 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount8 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount8 account, 8 calculatedamountfield, ((entrycode/1000)*100) + 8 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount9 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount9 account, 9 calculatedamountfield, ((entrycode/1000)*100) + 9 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount10 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount10 account, 10 calculatedamountfield, ((entrycode/1000)*100) + 10 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount11 amount, case when calcsign='-' then -1 else 1 end calcsignvalue, accountnoofcalcamount11 account, 11 calculatedamountfield, ((entrycode/1000)*100) + 11 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount1 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit1 account, 1 calculatedamountfield, ((entrycode/1000)*100) + 1 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount2 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit2 account, 2 calculatedamountfield, ((entrycode/1000)*100) + 2 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount3 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit3 account, 3 calculatedamountfield, ((entrycode/1000)*100) + 3 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount4 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit4 account, 4 calculatedamountfield, ((entrycode/1000)*100) + 4 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount5 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit5 account, 5 calculatedamountfield, ((entrycode/1000)*100) + 5 textfield from pw001pyh where periodeused>202100 and periodeused < 2016012 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount6 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit6 account, 6 calculatedamountfield, ((entrycode/1000)*100) + 6 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount7 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit7 account, 7 calculatedamountfield, ((entrycode/1000)*100) + 7 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer,pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount8 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit8 account, 8 calculatedamountfield, ((entrycode/1000)*100) + 8 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount9 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit9 account, 9 calculatedamountfield, ((entrycode/1000)*100) + 9 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount10 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit10 account, 10 calculatedamountfield, ((entrycode/1000)*100) + 10 textfield from pw001pyh where periodeused>202100 and periodeused < 202200 union all
select orgnumid, employer, pin, rank, periodeused, entrycode, accountdim2, accountdim3, accountdim4, accountdim5, accountdim6, calculatedamount11 amount, case when calcsign='-' then 1 else -1 end calcsignvalue, accountnoofcalcamountcredit11 account, 11 calculatedamountfield, ((entrycode/1000)*100) + 11 textfield from pw001pyh where periodeused>202100 and periodeused < 202200
) pyh
left join pw001p01 p01 on p01.pin= pyh.pin
left join pyety001txt t on t.tid=0 and t.texttype='O' and pyh.calculatedamountfield=t.tnumber
left join py001acc da on pyh.account=da.accountno
left join pyety001 txt on pyh.entrycode=txt.entrycode
left join pworg e on pyh.employer=e.numorgid
left join pworg c on pyh.orgnumid=c.numorgid
left join pworg v on pyh.accountdim2=v.numorgid
left join pw001c02 c02 on pyh.rank=c02.code
left join pwedagdef ed on pyh.entrycode=ed.entrycode and ('L'+convert(varchar(3), (pyh.calculatedamountfield +60)) = ed.pfieldno)
where periodeused > 202100 and periodeused < 202200
and ((isnumeric(pyh.account)=1 and convert(integer, pyh.account) > 2999)) |