Skip to end of banner
Go to start of banner

Activity gaps

Skip to end of metadata
Go to start of metadata

You are viewing an old version of this page. View the current version.

Compare with Current View Version History

« Previous Version 2 Next »

Description

Activity gaps

Summary

The view shows persons with gaps in activities

Keywords

Description

For permanent contracts, users need the ability to view gaps in activities.

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

Selection

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

  • No labels