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