Versions Compared

Version Old Version 2 New Version 3
Changes made by

Daria Chebotaieva

Daria Chebotaieva

Saved on

Key

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

...

Description

...

Table of Contents
minLevel1
maxLevel1
typeflat
separatorpipe
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
Select
	p.PIN,
	p.[NAME] as 'Name',
	p.CLIENT NUMORGID,
	p.EMPLOYMENTSTARTDATE,
	p.EMPLOYMENTENDDATE,
	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

...