/
Activity gaps/ Available Crew
Activity gaps/ Available Crew
- Artem Finkelshtein
- Anna Zelenskaya
- Daria Chebotaieva
- Helen Ustych
Owned by Artem Finkelshtein
1 Description | 2 Selection | 3 SQL Statement
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,
(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)