Hi

I'm new to WebFocus,

i'm not getting how to go forword, I'm giving the complete scenario, plz help me,

There are Three Tables,

1. Department
Fields � DeptID (PK)
DeptName

2. Employee
Fields - EmpID (PK)
EmpName
JoinDate

3. EmployeeDetails
Fields � EmpID (FK)
DeptID (FK)
DeptJoinDate
TerminationDate
TerminationReason

I've to create a single report, which has below sections �

1. All the Employees
I've to display all the employees for each Department if TerminationDate and TerminationReason is Null.

2. New Employees since a MM/DD/YYYY.
I�ve to display employees for each Department where DeptJoinDate >= MM/DD/YYYY and TerminationDate and TerminationReason is Null.

3. Members Transferred to Department since a date.

An Employee �Transferred� means EmployeeDetails Table will have atleast two records � one with TerminationDate field has a value and another record with a different DeptID and TerminationDate is Null.

Here I�ve to select all the Employees associated with a Department (there exists a record with TerminationDate < MM/DD/YYYY and Another record with DeptJoinDate >= MM/DD/YYYY) And JoinDate < MM/DD/YYYY

4. Terminated Members to Department since MM/DD/YYYY

An Employee �Terminated� means EmployeeDetails Table should have only one record with TerminationDate field has a value.

Here I�ve to select all the Employees with that Department where JoinDate <= MM/DD/YYYY And TerminationDate <= MM/DD/YYYY and there does not exist another record where DeptJoinDate >= MM/DD/YYYY

5. Members transferred from a Department since MM/DD/YYYY

Select all Employees associated with that Department where JoinDate <= MM/DD/YYYY And DeptJoinDate <= MM/DD/YYYY And TerminationDate < MM/DD/YYYY

I've created a view, which returns all the record irrespective of any above condition. I've to give above condition in the single where Condition in the WebFocus tool.

I've one more doubt,

How to display date in DD/MM/YYYY HH:MM AM/PM format?

Thanx in advance

Regards

Vijay

Hi Vijay-
I think what you are looking for is a JOIN. Take a look in your documentation for a good discussion of this topic.
You also should be able to find information in the docuemnation to help you display the date and time.
Welcome to FOCUS.