September 10, 2004 at 11:40 am
I've been task with creating a Crystal Report based upon field parameters from the Pathlore LMS application. A supervisor would like the ability to determine when an employee has not completed a course within a date range. Here is the stored procedure I have created as follows:
CREATE PROCEDURE dbo.sp_UOF_FAILQUAL
@ReportBeginDate datetime,
@ReportEndDate datetime,
@employee_s varchar(1)
AS
SET CONCAT_NULL_YIELDS_NULL OFF
SELECT
north_data.person.xlastname + ', ' + north_data.person.xfirstname AS EmpName,
north_data.person.agency,
north_data.person.rank,
north_data.person.employee_n
FROM north_data.person
LEFT JOIN north_data.event
ON north_data.person.xuniquekey = north_data.event.yuniquekey
ORDER BY north_data.person.xlastname + ', ' + north_data.person.xfirstname
There are three tables I am bases this stored procedure on: 1) Class table; 2) Event table; and 3) Person table. Both the Class and Event table are linked by a field called xlocator, which is a varchar field of 20 characters. The xlocator number identifies the type of course being offered, of which, each class is given a unique number. However, the class may be the same name with variant differences, such as: UOF-16 (1st Tri), UOF-27 (1st Tri) or UOF-08 (2nd Tri).
Question. How can I associate the xlocator of the Class and Event Table to show that an individual has not taken this course using the stored procedure above?
September 10, 2004 at 12:41 pm
Change your left join to a full outer join. This will ensure that you get a record returned for each class that exists, and also that you will get the users record back if no classes have been taken yet (ie. if there are no events for the selected user).
The dataset would look something like this.
Name, dateattended, event
John doe, null, UOF-16
John doe, 1/21/04, UOF-17
Then you can do whatever you like with the nulls.
In crystal, you will create a group on the userid, and then list all of the class records in the group.
September 10, 2004 at 1:56 pm
Thank you, David. I will try that.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply