Show Individuals NOT in criteria

[rbh123456789]

CR 11.5

I have 2 tables:

1) out_of_office_table
2) vacations_table
3) staff_table

The out_of_office_table has a list of out of town events that are booked. the tables contains a unique ID for each entry, start date, end date, and the person who is away (1 person).

The vacations_table has a list of vacation entries. table tables the person who is on vacation, unique id per record, start and end date

staff_table has all the staff members, which all have a unique id

I need a formula to check tables 1 & 2 for entries and display the staff who are NOT in either of the tables.

This is meant to be an 'availability' report, to show who is currently in the office.

Any help would be appreciated.

Thanks.

 

[MCuthill]

rbh,

What is the structure of your report? Do you have any record selection criteria? Will the report be ran for a given day, or range of days?

I am thinking you will somehow need to have every possible date show, then trigger 1's and 0's if a person is on vacation or out of office for a given date (current day analyzed >= start of vacation and current day analyzed <= end of vacation).

The persons available would then have a SUM of 0 for these formulas.

Hope this helps point you in the right direction.

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[rbh123456789]

Thanks MCuthill,

i forgot to mention that. yes, the only parameter when generating the report is a date range. the range (lets say 5 days) would isolate those days and check the tables to see whos away.

i guess i am just having a difficult time envisioning how to show the staff who do not have entries...

 

[MCuthill]

rbh,

Perhaps I was over thinking it...

If you use left-outer joins, perhaps a criteria like follows will work:

(IsNull({VacationTable.EmpID}) AND IsNull({OutOfOfficeTable.EmpID}))

This record selection would show only those persons without a field returned from the outer tables.

Definitely worth a shot, wouldn't be tough to test.

Cheers! Hope this helps!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[MCuthill]

rbh,

Are you able to get the report to show those persons who are away; or to what point is the report developed? (does it list all staff at present?)

If you could provide an example of your data and any report structure, it would assist in helping you with a solution. I assume, at minimum, you have a group on EmpID for this report. =)

Cheers!

Mike
---------------------------------------------------------------
"To be alive is to revel in the moments, in the sunrise and the sunset, in the sudden and brief episodes of love and adventure,
in the hours of companionship. It is, most of all, to never be paralyzed by your fears of a future that no one can foretell."

 

[lbass]

You could do this by using left joins FROM the employee table TO each of the other two tables. Do not add selection criteria (which would undo the joins). Instead create two formula like this:

//{@vacat}:
if (
isnull({vacation.emplID}) or
{vacation.startdate} > {?EndDate} or
{vacation.enddate} < {?Start}
) then
0 else
1

//{@out}:
if (
isnull({outofoffice.emplID}) or
{outofoffice.startdate} > {?EndDate} or
{outofoffice.enddate} < {?Start}
) then
0 else
1

Then go to report->selection formula->GROUP and enter:

sum({@vacat},{employee.ID}) = 0 and
sum({@out},{employee.ID}) = 0

-LB

 

[rbh123456789]

Thanks all for the help, i have been away from the office.


MCuthill - at this point, the report is totally blank, no groups or anything. just the tables are present

lbass - since my last post i have actually unioned the 2 tables (outofoffice and vacation), which are now being displayed with 1 sql view (just to make things easier).

so i created your @out formula (which uses my sql view of the combined tables) and added the group formula.

however, i am assuming that i want to created a group first, based on the employee.id, correct?

i also add the employees table and left joined it to the 'out of office' sql view.