Versions Compared

Version Old Version 4 New Version Current
Changes made by

Helen Ustych

Anna Zelenskaya

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

Page Properties

Summary

The view shows persons with gaps in activities.

Keywords

Activties, Gap

Description

For permanent contracts, users need the ability to view gaps in activities. The view provides this information.

Selection

The view shows persons with gaps in activities, a separate row per every gap.

SQL Statement

Code Block
languagesql
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)