CREATE VIEW dbo.PW001SRV21 as
Select
	p.PIN,
	p.[NAME] as 'Name',
	p.CLIENT NUMORGID,
	p.EMPLOYMENTSTARTDATE,
	p.EMPLOYMENTENDDATE,
	(select t.NATIONALITY from PWCOUNTRY t where t.COUNTRYCODE = p.NATIONALITY) as 'Nationality',
	Isnull(a1.DATETO,a1.TODATEESTIMATED) as 'Gap From',
	a2.DATEFROM as 'Gap To',
	DateDiff(DAY,Isnull(a1.DATETO,a1.TODATEESTIMATED),a2.DATEFROM)-1 as 'Gap Days'
From PW001P01 p
Join PW001P03 a2 on p.PIN = a2.PIN
			and a2.DATEFROM > (Select MIN(t.DATEFROM) From PW001P03 t Where t.PIN = p.PIN)
Join Pw001p03 a1 on p.PIN = a1.PIN
			and a1.SEQUENCENO != a2.SEQUENCENO
			and Isnull(a1.DATETO,a1.TODATEESTIMATED) is not null
			and DateDiff(DAY,Isnull(a1.DATETO,a1.TODATEESTIMATED),a2.DATEFROM)>1
			and not exists (select q.SEQUENCENO from PW001P03 q
							where q.PIN = p.PIN and q.DATEFROM between a1.DATEFROM and a2.DATEFROM
								and q.SEQUENCENO != a1.SEQUENCENO and q.SEQUENCENO != a2.SEQUENCENO)