stumped by query: retrieving trainees that have passed all courses

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

stumped by query: retrieving trainees that have passed all courses

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-02-07 : 08:26:24

Nick writes "Hi, this one has me floored.

I've got 4 tables: Course, Trainee, Test and TestSession.
Each Course has multiple Tests. Each Test has multiple TestSessions. A Trainee attends multiple TestSessions, but usually one TestSession per Test, and there is a point when they have only completed TestSessions for some of the Tests.

So, I need to retrieve a list of all Trainees that have passed all Tests for a course (a field in the TestSession table records that they've passed that specific TestSession, and hence that Test - ts_qa_assured).

Hope you're still with me... :)

CREATE TABLE [Test] (
[test_id] [int] IDENTITY (1, 1) NOT NULL ,
[test_course] [int] NULL ,
[test_maxscore] [int] NULL ,
[test_passmark] [int] NULL ,
[test_name] [nvarchar] (50) NULL,
CONSTRAINT [PK_tTest] PRIMARY KEY CLUSTERED
(
[test_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_tTest_tCourse] FOREIGN KEY
(
[test_course]
) REFERENCES [tCourse] (
[course_id]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO

CREATE TABLE [TestSession] (
[ts_id] [int] IDENTITY (1, 1) NOT NULL ,
[ts_date] [datetime] NULL ,
[ts_test] [int] NULL ,
[ts_trainee] [int] NULL ,
[ts_score] [int] NULL ,
[ts_qa_assured] [bit] NULL ,
CONSTRAINT [PK_tTestSession] PRIMARY KEY CLUSTERED
(
[ts_id]
) ON [PRIMARY] ,
CONSTRAINT [FK_tTestSession_tPerson] FOREIGN KEY
(
[ts_trainee]
) REFERENCES [tPerson] (
[p_id]
) NOT FOR REPLICATION ,
CONSTRAINT [FK_tTestSession_tTest] FOREIGN KEY
(
[ts_test]
) REFERENCES [tTest] (
[test_id]
) NOT FOR REPLICATION
) ON [PRIMARY]
GO

CREATE TABLE [Course] (
[course_id] [int] IDENTITY (1, 1) NOT NULL ,
[course_title] [nvarchar] (50) NULL ,
[course_active] [bit] NULL CONSTRAINT [DF_tCourse_course_active] DEFAULT (1),
CONSTRAINT [PK_tCourse] PRIMARY KEY CLUSTERED
(
[course_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [Trainee] (
[p_id] [int] IDENTITY (1, 1) NOT NULL ,
[p_firstname] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[p_surname] [nvarchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
CONSTRAINT [PK_tTrainee] PRIMARY KEY CLUSTERED
(
[p_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO

If you can help on this one, I'd REALLY appreciate it!!

Cheers
Nick"

tkeith
Starting Member

9 Posts

Posted - 2005-02-11 : 21:34:19

Here's a couple queries that might help you out. It looks to me as though you need to use a CROSS join between your Trainee and Test tables. This will allow you to look at each test per trainee (even those not taken and thus not in the TestSession table).

DECLARE @courseid int

/* ID for the course you are limiting the results by */
SET @courseid = 1

/* Query top Trainee scores for all tests in course */
SELECT p.[p_surname] + ', ' + p.[p_firstname] AS [Trainee]
,	c.[course_title] AS [Course]
,	t.[test_name] AS [Test Name]
,	ISNULL(ts.[ts_score],0) AS [Score]
,	t.[test_maxscore] AS [Max Score]
,	LTRIM(STR(ROUND((CONVERT(DECIMAL,ISNULL(ts.[ts_score],0))/t.[test_maxscore])*100,1),10)) + '%' AS [Percent]
FROM	(SELECT test.[test_id]
	,	trainee.[p_id]
	FROM test CROSS JOIN trainee
	WHERE test.[test_course] = @courseid) TraineeAllTest 
	INNER JOIN Test t ON t.[test_id] = TraineeAllTest.[test_id]
	INNER JOIN Trainee p ON p.[p_id] = TraineeAllTest.[p_id]
	INNER JOIN Course c ON c.[course_id] = t.[test_course]
	LEFT OUTER JOIN (
		SELECT	[ts_trainee]
		,	[ts_test]
		,	MAX([ts_score]) AS [ts_score]
		FROM	TestSession
		WHERE [ts_test] IN (SELECT [test_id] FROM Test WHERE [test_course] = @courseid)
			AND [ts_qa_assured] = 1
		GROUP BY [ts_trainee], [ts_test]
			) ts ON ts.[ts_test] = TraineeAllTest.[test_id] 
			AND ts.[ts_trainee] = TraineeAllTest.[p_id]
ORDER BY p.[p_surname] + ', ' + p.[p_firstname], t.[test_id]


/* Query Trainees that have passed all tests in course */

/* First find all trainees that have not passed or taken a test in the course */
DECLARE @TestsNotPassed TABLE(test_id int, p_id int)

INSERT INTO @TestsNotPassed

SELECT t.[test_id]
,	p.[p_id]
FROM test t CROSS JOIN trainee p
	LEFT OUTER JOIN (
		SELECT	[ts_trainee]
		,	[ts_test]
		,	MAX([ts_score]) AS [ts_score]
		FROM	TestSession
		WHERE [ts_test] IN (SELECT [test_id] FROM Test WHERE [test_course] = @courseid)
			AND [ts_qa_assured] = 1
		GROUP BY [ts_trainee], [ts_test]
		) ts ON ts.[ts_test] = t.[test_id] AND ts.[ts_trainee] = p.[p_id]
WHERE t.[test_course] = @courseid
	AND (	ts.[ts_trainee] IS NULL 
		OR ts.[ts_test] IS NULL
		OR ts.[ts_score] < t.[test_passmark] )
ORDER BY [p_id], [test_id]

/* Select all trainees not in @TestsNotPassed */
SELECT	p.[p_surname] + ', ' + p.[p_firstname] AS [Trainees Passed] 
FROM Trainee p
WHERE p.[p_id] NOT IN (SELECT [p_id] FROM @TestsNotPassed)

nr
SQLTeam MVY

12543 Posts

Posted - 2005-02-12 : 00:18:53

This uses a having clause to find those trainees that have passsed the same number of tests that there are in the course - i.e. all.
Untested of course


select c.course_id, tr.p_id
from Course c
cross join Trainee tr
	join test t
		on t.test_course = c.course_id
	left join (select distinct ts_test, ts_trainee from TestSession where ts_qa_assured = 1) ts
		on ts.ts_test = t.test_id
		and ts.ts_trainee = tr.p_id
group by c.course_id, tr.p_id
having count(t.test_id) = sum(case when ts.ts_test is null then 0 else 1 end)

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources