Description
The view will select and sum the salary days from the hardcoded list of Entry Codes defined by the customer.
Selection
CREATE VIEW dbo.PW001SRV534 AS
SELECT
X.PIN,
X.NAME,
X.DAYS,
X.[Payscale Table],
X.[Payscale Code],
X.Numorgid,
X.EMPLOYMENTSTARTDATE,
X.EMPLOYMENTENDDATE,
PW.NAME AS 'VESSEL'
FROM
( SELECT
P01.PIN ,
P01.NAME,
--sum(dbo.ad_StrToFloat(entryfield2)) as Days,
SUM(SUM(dbo.ad_StrToFloat(entryfield2))) OVER(PARTITION BY p01.PIN) AS 'DAYS',
--ORG.NAME AS Vessel,
p0p.PAYSCALETABLE as 'Payscale Table',
p0p.PAYSCALECODE as 'Payscale Code',
P01.CLIENT Numorgid,
P01.EMPLOYMENTSTARTDATE ,
P01.EMPLOYMENTENDDATE
FROM PW001PAY PAY
LEFT JOIN PW001P01 P01 ON P01.PIN=PAY.PIN
LEFT JOIN PWORG ORG ON PAY.ACCOUNTDIM2=ORG.NUMORGID
LEFT JOIN PW001P0P P0P ON (P01.PIN=P0P.PIN AND (P0P.PNUMBER='A'))
where pay.entrycode in (100, 102, 103, 104, 105, 107, 108, 109, 110)
group by P01.PIN ,
P01.NAME,
P01.CLIENT,
p0p.PAYSCALETABLE,
p0p.PAYSCALECODE,
P01.CLIENT,
--ORG.NAME,
P01.EMPLOYMENTSTARTDATE ,
P01.EMPLOYMENTENDDATE
) X
JOIN PW001PAY PAYx
ON PAYX.PIN=X.PIN
JOIN PWORG PW
ON PW.NUMORGID=PAYX.ACCOUNTDIM2
AND NOT EXISTS
(SELECT PAYT.FromDate FROM PW001PAY PAYT
WHERE PAYT.PIN=PAYX.PIN
--AND PAYT.P03_CODE IN ('ON', 'ILL ON')
AND PAYT.ENTRYCODE IN (100, 102, 103, 104, 105, 107, 108, 109, 110)
AND PAYT.P03_DATEFROM > PAYX.P03_DATEFROM)
where payX.entrycode in (100, 102, 103, 104, 105, 107, 108, 109, 110)
GROUP BY
X.PIN,
X.NAME,
X.DAYS,
X.[Payscale Table],
X.[Payscale Code],
X.Numorgid,
EMPLOYMENTSTARTDATE,
X.EMPLOYMENTENDDATE,
PW.NAME