Skip to end of banner
Go to start of banner

Activity gaps/ Available Crew

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 7 Next »

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

CREATE VIEW dbo.PW001SRV21 as
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
			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)

  • No labels