Overlapping Activities
1 Description | 2 SQL Statement
Summary | The view shows persons with overlapping activities. |
|---|---|
Keywords | Activties, Overlap |
Description
While usually overlapping activities aren’t allowed, it’s essential to check for activity overlaps from time to time. The view shows activity overlaps and allows to distinguish between vessel transfer and unwanted overlap.
SQL Statement
Select
p.PIN,
p.[NAME] as 'Name',
p.CLIENT NUMORGID,
p.EMPLOYMENTSTARTDATE,
p.EMPLOYMENTENDDATE,
a2.DATEFROM as 'OverlapFrom',
Isnull(Isnull(a1.DATETO,a1.TODATEESTIMATED),Convert(DATE,GetDate())) as 'OverlapTo',
DateDiff(DAY,a2.DATEFROM,Isnull(Isnull(a1.DATETO,a1.TODATEESTIMATED),Convert(DATE,GetDate())))+1 as 'OverlapDays',
Case
When c1.OPTIONS like '%S%'
and c2.OPTIONS like '%S%'
and DateDiff(DAY,a2.DATEFROM,Isnull(Isnull(a1.DATETO,a1.TODATEESTIMATED),Convert(DATE,GetDate()))) = 0
and dbo.ad_scanorgtree(a1.NUMORGID,3) != dbo.ad_scanorgtree(a2.NUMORGID,3)
Then NCHAR(10004)
Else ''
End as 'VesselTransfer',
a1.CODE+Space(1)+c1.[TEXT] as 'Activity',
a2.CODE+Space(1)+c2.[TEXT] as 'OverlapActivity'
From PW001P01 p
Join PW001P03 a1 on p.PIN = a1.PIN
Join Pw001C12 c1 on a1.CODE = c1.CODE
Join PW001P03 a2 on p.PIN = a2.PIN
and a2.SEQUENCENO != a1.SEQUENCENO
and a2.DATEFROM between a1.DATETO and Isnull(Isnull(a1.DATETO,a1.TODATEESTIMATED),Convert(DATE,GetDate()))
Join Pw001C12 c2 on a2.CODE = c2.CODE