Hi,
I am having a problem in creating a report meeting my criteria:
I am using crystal reports 8.5
The tables I am using for the report are:
1. Employee
2. Assignments
The primary field in the employee table is ID
The primary field in the Assignments table is ID. both are linked.
The Data in the tables are as follows:
Employee table:
ID Name
1 xyz
2 abc
3 aaa
4 bbb
5 ccc
6 ddd
Assignments table data: (It consists of multiple records for each ID and also for each unitid)
Recnum ID unitid closeddate
1 1 100 12/2/2003
2 1 100 1/1/1753 (default date in sql server)
3 1 125 12/10/2003
4 1 126 1/1//1753 (it means no date, i.,e Open Unit)
5 2 100 12/05/2003
6 2 100 1/1/1753
7 2 125 12/4/2003
8 2 126 12/7/2003
The criteria is: The ID should have Unitid 100 (which is a MUST) and any (one or more)of the other units i.e.,125 or 126 and the closed date for the latest record(we can find the latest record by the Record number which is generated by the system)should be 1/1/1753 which is the default date which means the unit is OPEN.
Each ID and unit might end up with multiple records in the Assignments table. But we need to consider the latest record for each unit which is OPEN that means with a closed Date 1/1/1753.
As per the criteria, the data with ID 1 from the above Assignments table should be on the report because it has unit id 100 (with the latest record (recnum:2) open) and also it has unit id 126 with the latest record open.
The data with ID 2 from the above Assignments table should not come on the report because it has unit id 100 with the latest record open but with it does not have any open record with other units i.e., 125 or 126.
I need to get the latest record in each group which has a closed date of 1/1/1753.
Name or ID(employee table) unitid (Assignments table)
Thanks
Pandy
I am having a problem in creating a report meeting my criteria:
I am using crystal reports 8.5
The tables I am using for the report are:
1. Employee
2. Assignments
The primary field in the employee table is ID
The primary field in the Assignments table is ID. both are linked.
The Data in the tables are as follows:
Employee table:
ID Name
1 xyz
2 abc
3 aaa
4 bbb
5 ccc
6 ddd
Assignments table data: (It consists of multiple records for each ID and also for each unitid)
Recnum ID unitid closeddate
1 1 100 12/2/2003
2 1 100 1/1/1753 (default date in sql server)
3 1 125 12/10/2003
4 1 126 1/1//1753 (it means no date, i.,e Open Unit)
5 2 100 12/05/2003
6 2 100 1/1/1753
7 2 125 12/4/2003
8 2 126 12/7/2003
The criteria is: The ID should have Unitid 100 (which is a MUST) and any (one or more)of the other units i.e.,125 or 126 and the closed date for the latest record(we can find the latest record by the Record number which is generated by the system)should be 1/1/1753 which is the default date which means the unit is OPEN.
Each ID and unit might end up with multiple records in the Assignments table. But we need to consider the latest record for each unit which is OPEN that means with a closed Date 1/1/1753.
As per the criteria, the data with ID 1 from the above Assignments table should be on the report because it has unit id 100 (with the latest record (recnum:2) open) and also it has unit id 126 with the latest record open.
The data with ID 2 from the above Assignments table should not come on the report because it has unit id 100 with the latest record open but with it does not have any open record with other units i.e., 125 or 126.
I need to get the latest record in each group which has a closed date of 1/1/1753.
Name or ID(employee table) unitid (Assignments table)
Thanks
Pandy