Friends . .In the golden olden days - use pl/sql block looping tables variables counters etc etc . .Stumbled upon a method whereby a combination of lead lag case and .inline views can produce a decent report ad hoc . .I was amazed at the results. Cant explain the theory behind this though. (coz I dont understand the relational theory myself) . .Will leave that to Janet and John . .-- Query to output overlapping absences .select ppf.employee_number ppf.last_name a.* .from .( .select date_start date_end last_enddate person_id .case when last_enddate is null then null . when last_enddate < date_start-1 then 'Gap' . when last_enddate > date_start then 'Overlap' .-- when date_end is not null and next_startdate is null then 'Missing next' . else null .end another_flag .from ( . select paa.* . lag(date_end) over (partition by person_id order by date_start) . last_enddate . lead(date_start) over (partition by person_id order by date_start) . next_startdate . from per_absence_attendances paa . order by person_id date_start . ) .) a .per_all_people_f ppf .where another_flag = 'Overlap' .and a.person_id = ppf.person_id .and a.date_start between ppf.effective_start_date and ppf.effective_end_date . . .-- Please point out any errors and constructive criticism welcome .cheers .Vin

Bolt on question . .Why does teh absence form just produce a warning and not an error when overlapping absences. Is this standard behaviour? . .How do other sites cope with this behaviour? .Custom library job??? or .Has the functionality been enhanced by a new patch?

Vin I seem to recall that if the absences are 'Sickness' then it's an error. . .Intrigued by the query I shall have a look at that next week! Nice one...

Seen some similar code from our Datawarehouse boys here at XYZ. Some of the functions these guys use are mind-blowing. . .Regarding the overlapping issue i'd tend to agree with CT. Unless the absence is SICKNESS you can quite happily have overlapping absences. . .I would normally reserve final judgement on this topic to Mr Grewal the disputed king of absence however since here rarely frequents this establishment we may just have to leave the question-mark hanging...

[quote:b4277b3dfd]Mr Grewal the disputed king of absence[/quote:b4277b3dfd] .No truer word spoken on any HRMS-related bulletin board... where is the old fraud anyway?

I am afraid Absences can overlap.

Hurrah he lives! (Note to self - sell all remaining Oracle shares ASAP)

In fact he needed a follow up e-mail to log on ... . .and all he came up with was 6 words and a full stop.

Grewal you can't leave us hanging like that....please explain.....

Actually i've just had a chat with the Mr Absence and he explains is 6 words (and a full-stop) thus... .Overlapping absences are allowed because it needs to cater for multiple assignments. Until such time as the Absence model changes to Assignment-level (rather than its currently antiquated person-level) we're always going to get kicked by this. . .WHen the model does switch he assures me that overlapping SICKNESS absences for the same assignment will not be allowed. . .I'll give him a nudge and see if he wishes to add any further enlightenment.