Hi.

I am creating a demo for a company that wants to improve some of its reports and I am trying to show them Zoho as a viable opportunity.

Now, while creating the demo, I have to "infer" some information from the tables but I'm not being able to do it because I am not able to add logic code to the reports.

Here is an example.

I have three tables with simple key relation.

	Job
Personnel	JobID
PersonnelID	PersonnelID (FK)	Customer
Name	CustomerID (FK)	CustomerID
	start	Name
	end

Assumptions:

A personnel is considered 'not-available' for a given date when a job is registered in the Job table and the given date is between the start and end dates.

A personnel is considered 'available' for a given date when when either he/she is not in the Job table or when none of the Jobs he/she is registered in, have start/end date range that includes the given date

Report definition:

I need a report of the available personnel given the data in the three tables for a particular date (or range of dates)

The output ideally would be something like:

PersonnelID	Available from	Available to
1	20.02.2013
2	24.02.2013
3	20.02.2013	27.02.2013

where the job table would be something like

JobID	CustID	PerID	start	end
1101	ABC	2	18.02.2013	23.02.2013
1102	XYZ	3	28.02.2013	05.03.2013

and Personnel and Customer would be simple:

PersonnelID

1

2

3

CustomerID

ABC

XYZ

Attempted Solution:

As the Job table does not have information about all personell, I had to create a query that joined the two tables, Personel and Job

1. SELECT  [meaningful fields here...]
2. FROM  "Job" JOIN "Customer" ON "Customer"."CustID"  = "Job"."CustID"  RIGHT JOIN "Personnel" ON "Personnel"."PersID"  = "Job"."PersID"  
3. ORDER BY "Personnel"."PersID"

(it might not be beautiful but does the job :P and gives the idea for the example. If you have a better way to do this, please tell me)

With that, I have a list of the whole personnel. Some of them have the job fields, but some not. 

Now, I can calculate the Available from and Available to fields in the Job table by adding a custom formula:

Available from:

1. if("start" > currentdate( ),
2.   currentdate( ),
3.   if("end" < currentdate( ),
4.     currentdate( ),
5.     "end"
6.   )  
7. )

Available to:

1. if( "start" > currentdate( ), "start", null)

I don't care too much (for now) about the Available To, but for the sake of completeness, I added it. The results now for my report are:

PersonnelID	Available from	Available to
1
2	24.02.2013
3	20.02.2013	27.02.2013

which are close to the expected result, but not quite, because this is what I want:

With this in mind, is there any way to include the date '20.02.2013' date (current_date) in the query table if the jobID field is missing (i.e. when someone is not present in the Job table)

If I am approaching the problem from the wrong perspective, please also tell me (that's why I took my time explaining the original requirement)

Thank you very much in advance.

A.