May 14, 2018 at 1:08 pm
I am working with a system in which I can query prerequisites for course enrollment in a query like this:SELECT
.[PEOPLE_CODE_ID]
,.[EVENT_ID]
,[sp].[OPEN_PARENS]
,[spc].[PREREQ_EVENT_ID]
,[sp].[CLOSE_PARENS]
,[sp].[LOGICAL_OPERATOR]
,[td2].[FINAL_GRADE]
FROM [TRANSCRIPTDETAIL]
JOIN [SECTIONS]
ON
.[EVENT_ID] = .[EVENT_ID]
JOIN [SECTIONPREREQUISITE] [sp]
ON .[EVENT_ID] = [sp].[EVENT_ID]
JOIN [SECTIONPREREQCOURSE] [spc]
ON [sp].[PREREQ_ID] = [spc].[PREREQ_ID]
LEFT JOIN [TRANSCRIPTDETAIL] [td2]
ON
.[PEOPLE_CODE_ID] = [td2].[PEOPLE_CODE_ID]
AND [spc].[PREREQ_EVENT_ID] = [td2].[EVENT_ID]
This yields:
| PEOPLE_CODE_ID | EVENT_ID | OPEN_PARENS | PREREQ_EVENT_ID | CLOSE_PARENS | LOGICAL_OPERATOR | FINAL_GRADE |
| P000011746 | EG372 | (( | MS110 | O | A | |
| P000011746 | EG372 | MS150 | ) | A | NULL | |
| P000011746 | EG372 | ( | ET371 | O | A | |
| P000011746 | EG372 | ES371 | ) | A | NULL | |
| P000011746 | EG372 | ( | PS102 | O | TR | |
| P000011746 | EG372 | PS162 | ) | A | NULL | |
| P000011746 | EG372 | ( | CO200 | O | NULL | |
| P000011746 | EG372 | CE203 | O | |||
| P000011746 | EG372 | CO201 | )) | NULL | ||
| P000011746 | EG481 | ET211 | O | B | ||
| P000011746 | EG481 | ES201 | O | NULL | ||
| P000011746 | EG481 | ES251 | NULL | |||
| P000011746 | ET432 | ( | ET371 | O | A | |
| P000011746 | ET432 | ES371 | ) | NULL | ||
| P000011746 | ET401 | ((( | EG372 | A | ||
| P000011746 | ET401 | ( | CE203 | O | ||
| P000011746 | ET401 | CO200 | O | NULL | ||
| P000011746 | ET401 | CO201 | O | NULL | ||
| P000011746 | ET401 | CO203 | )) | O | NULL |
Meaning that in order for P000011746 to take EG372, they must have passed (MS110 OR MS150) AND (ET371 OR ES371) AND (PS102 OR PS162) AND (CO200 OR CE203 OR CO201). I am trying to find a way to do this in SQL. I've included a second join to [TRANSCRIPTDETAIL] in the query above in order to see what the student has for a final grade, but I will most likely remove that and go the route of writing a bunch of dynamic SQL checks instead. Can anyone think of a better approach? I know it may be difficult without knowing more, and I can provide more information if needed.
May 14, 2018 at 2:02 pm
Without a much more detailed understanding of your tables, their structure, and what a record in each of the tables is intended to represent, this is largely impractical for someone to assist you. We need a lot more detail, which will mean CREATE TABLE and INSERT / VALUES statements for each of the tables, and a good explanation of what a record in each of those tables represents. Then explain your overall objective in terms of why your query makes the joins that it does, and we then would have a much better basis for providing useful assistance. As all you've provided so far is the resulting data from your query, we're entirely in the dark as to how that result applies to the actual problem at hand.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 15, 2018 at 10:05 am
sgmunson - Monday, May 14, 2018 2:02 PM... which will mean CREATE TABLE and INSERT / VALUES statements for each of the tables, and a good explanation of what a record in each of those tables represents.
Here is some SQL to create two temporary tables and populate them with sample data. I excluded a lot of extraneous information to try and focus on the issue at hand.
sgmunson - Monday, May 14, 2018 2:02 PM... a good explanation of what a record in each of those tables represents.
##TRANSCRIPT is a history of courses a student has taken, with which section (there can be multiple sections of a course offered, A, B, C, etc.), in what term, and their final grade (if they have one).
##PREREQUISITES are the prerequisite courses associated with each course. The records are evaluated per course/section, based on the order of [SEQUENCE_NUMBER], defining parenthesized ([OPEN_PARENS] ,[CLOSE_PARENS]), logical and/or ([LOGICAL_OPERATOR]) groupings of required prerequisites that the student has to have acquired a particular grade in (let's say any grade other than 'F'). I think I explained how to interpret these values well enough in my original post for EG372, but let me know if further clarification is needed.
sgmunson - Monday, May 14, 2018 2:02 PMThen explain your overall objective in terms of why your query makes the joins that it does
Here is a simplified query based on the test data I posted:SELECT [t].[PersonId]
,[t].[ACADEMIC_YEAR]
,[t].[ACADEMIC_TERM]
,[t].[EVENT_ID]
,[t].[SECTION]
,[p].[SEQUENCE_NUMBER]
,[p].[OPEN_PARENS]
,[p].[PREREQ_EVENT_ID]
,[p].[CLOSE_PARENS]
,[p].[LOGICAL_OPERATOR]
FROM ##TRANSCRIPT [t]
JOIN ##PREREQUISITES [p]
ON [t].[ACADEMIC_YEAR] = [p].[ACADEMIC_YEAR]
AND [t].[ACADEMIC_TERM] = [p].[ACADEMIC_TERM]
AND [t].[EVENT_ID] = [p].[EVENT_ID]
AND [t].[SECTION] = [p].[SECTION]
WHERE [t].[ACADEMIC_YEAR] = '2018'
AND [t].[ACADEMIC_TERM] = 'FALL'
ORDER BY [t].[PersonId]
,[t].[EVENT_ID]
,[p].[SEQUENCE_NUMBER]
It is selecting all course registrations for 2018/Fall, and then joining on ##PREREQUISITES to find the prerequisites that are configured for that course/section of that year/term. Their transcript then needs to be referenced again to see if they have taken, and if they have a passing grade for the courses ([PREREQ_EVENT_ID]). For example, if we were to join on ##TRANSCRIPT again based on [PersonId] and [PREREQ_EVENT_ID], we could see that 66134 has not taken or received a passing grade in ET211 or ES251 or ES201, which disqualifies him from taking EG321:
| 66134 | 2018 | FALL | EG321 | B | 10 | ET211 | O | F | ||
| 66134 | 2018 | FALL | EG321 | B | 20 | ES251 | O | NULL | ||
| 66134 | 2018 | FALL | EG321 | B | 30 | ES201 | NULL |
They can get arbitrarily more or less complicated than this, with any combination of parenthesis and and/or relationships. I'm looking for ideas on how I can verify that students have fulfilled their prerequisites.
May 15, 2018 at 11:54 am
cranberry - Tuesday, May 15, 2018 10:05 AMUnderstood - here is some more information that may help you help me.sgmunson - Monday, May 14, 2018 2:02 PM... which will mean CREATE TABLE and INSERT / VALUES statements for each of the tables, and a good explanation of what a record in each of those tables represents.Here is some SQL to create two temporary tables and populate them with sample data. I excluded a lot of extraneous information to try and focus on the issue at hand.
sgmunson - Monday, May 14, 2018 2:02 PM... a good explanation of what a record in each of those tables represents.##TRANSCRIPT is a history of courses a student has taken, with which section (there can be multiple sections of a course offered, A, B, C, etc.), in what term, and their final grade (if they have one).
##PREREQUISITES are the prerequisite courses associated with each course. The records are evaluated per course/section, based on the order of [SEQUENCE_NUMBER], defining parenthesized ([OPEN_PARENS] ,[CLOSE_PARENS]), logical and/or ([LOGICAL_OPERATOR]) groupings of required prerequisites that the student has to have acquired a particular grade in (let's say any grade other than 'F'). I think I explained how to interpret these values well enough in my original post for EG372, but let me know if further clarification is needed.sgmunson - Monday, May 14, 2018 2:02 PMThen explain your overall objective in terms of why your query makes the joins that it doesHere is a simplified query based on the test data I posted:
SELECT [t].[PersonId]
,[t].[ACADEMIC_YEAR]
,[t].[ACADEMIC_TERM]
,[t].[EVENT_ID]
,[t].[SECTION]
,[p].[SEQUENCE_NUMBER]
,[p].[OPEN_PARENS]
,[p].[PREREQ_EVENT_ID]
,[p].[CLOSE_PARENS]
,[p].[LOGICAL_OPERATOR]
FROM ##TRANSCRIPT [t]
JOIN ##PREREQUISITES [p]
ON [t].[ACADEMIC_YEAR] = [p].[ACADEMIC_YEAR]
AND [t].[ACADEMIC_TERM] = [p].[ACADEMIC_TERM]
AND [t].[EVENT_ID] = [p].[EVENT_ID]
AND [t].[SECTION] = [p].[SECTION]
WHERE [t].[ACADEMIC_YEAR] = '2018'
AND [t].[ACADEMIC_TERM] = 'FALL'
ORDER BY [t].[PersonId]
,[t].[EVENT_ID]
,[p].[SEQUENCE_NUMBER]It is selecting all course registrations for 2018/Fall, and then joining on ##PREREQUISITES to find the prerequisites that are configured for that course/section of that year/term. Their transcript then needs to be referenced again to see if they have taken, and if they have a passing grade for the courses ([PREREQ_EVENT_ID]). For example, if we were to join on ##TRANSCRIPT again based on [PersonId] and [PREREQ_EVENT_ID], we could see that 66134 has not taken or received a passing grade in ET211 or ES251 or ES201, which disqualifies him from taking EG321:
66134 2018 FALL EG321 B 10 ET211 O F 66134 2018 FALL EG321 B 20 ES251 O NULL 66134 2018 FALL EG321 B 30 ES201 NULL They can get arbitrarily more or less complicated than this, with any combination of parenthesis and and/or relationships. I'm looking for ideas on how I can verify that students have fulfilled their prerequisites.
Anyone tackling this would still need scripted table creates and inserts to have much of a chance to help, but you did do a reasonable job of explaining what you need. I personally would never allow such an unnecessarily complex structure to exist for the prerequisites, and would favor a design where additional rows in a prereq table represent AND conditions, and delimited strings in the course identifier and grade requirement portion represent OR conditions. This would allow some good use of Jeff Moden's string splitting function. Having to get to parentheses makes for more challenge than I have time to help out with. For your data, I suspect your approach to use dynamic SQL might well be the only approach that works. It's probably going to take a fair amount of testing,
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 15, 2018 at 12:01 pm
May 15, 2018 at 12:17 pm
cranberry - Tuesday, May 15, 2018 12:01 PMDid you miss the script I posted, or am I misunderstanding?
Here it is.
Yep, missed it. However, I don't go to "unknown to me" websites, and many that post here also hesitate to do so, as they are usually posting from work computers subject to internet policies and are just being safe rather than sorry. As you may have missed, I don't have the time to get involved in the dynamic SQL associated with making your situation work, and I'm not a fan of the table design. Too complex for my taste. Getting a useful query is going to be a LOT of work, and who has to support it if you get hit by a bus? Or retire? Will they understand it? Just documenting such a query could be as much work as the query itself.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
May 15, 2018 at 1:06 pm
One thing I will say - your code is likely to be unpredictable since you don't have any ordering on the query builder. Since table have no natural / physical ordering guarantee, the first 100 time will likely work the way you expect, but 101 will compose this differently.
As to the architecture/design, I would just use a prerequisite table instead of a query builder type table. I'd suspect that will yield better results.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 15, 2018 at 1:27 pm
Matt Miller (4) - Tuesday, May 15, 2018 1:06 PMOne thing I will say - your code is likely to be unpredictable since you don't have any ordering on the query builder. Since table have no natural / physical ordering guarantee, the first 100 time will likely work the way you expect, but 101 will compose this differently.
[SEQUENCE_NUMBER] indicates the order in which they should be evaluated / the query should be built.
Matt Miller (4) - Tuesday, May 15, 2018 1:06 PMAs to the architecture/design, I would just use a prerequisite table instead of a query builder type table. I'd suspect that will yield better results.
Unfortunately I don't have any control over the design of the database. The prerequisite checking functionality the software offers is inadequate, and I was only wondering how I might provide an alternative.
May 16, 2018 at 2:31 pm
I really shouldn't take the bait...
If you create the prerequisites table, this isn't too hard. I still am not totally clear on how you group ORs. How would you say "Course 10" requires Course 9 AND either (course 7 OR course 8)? Maybe I'm wrong, but I would think all the ANDs can be done with Prereq(@courseID) LEFT JOIN Transcript(@StudentID) and if there exist any records where the grade is null, the student doesn't meet all the prerequisites. The only way I can think of doing OR without hard coding it is to have a column in the Prereqs like (NextCourse, PrereqCourse, AndOr, GroupingID) so the case of "student must pass one of the following PrereqCourse courses before taking NextCourse"
you could use EXISTS for something like that, but if you can't write any T-SQL against it, I'm not sure how you're supposed to do it.
May 17, 2018 at 9:51 am
pietlinden - Wednesday, May 16, 2018 2:31 PMI really shouldn't take the bait...If you create the prerequisites table, this isn't too hard. I still am not totally clear on how you group ORs. How would you say "Course 10" requires Course 9 AND either (course 7 OR course 8)? Maybe I'm wrong, but I would think all the ANDs can be done with Prereq(@courseID) LEFT JOIN Transcript(@StudentID) and if there exist any records where the grade is null, the student doesn't meet all the prerequisites. The only way I can think of doing OR without hard coding it is to have a column in the Prereqs like (NextCourse, PrereqCourse, AndOr, GroupingID) so the case of "student must pass one of the following PrereqCourse courses before taking NextCourse"
you could use EXISTS for something like that, but if you can't write any T-SQL against it, I'm not sure how you're supposed to do it.
If your class table mentioned how many distinct prerequisites, your prerequisite detail table can tran how each prerequisite can be met.
Example: If your graduate basket-weaving class (BW-502) required an English class (any one of ENG101, 102 or 103) AND a chemistry class (chem50 or 51) AND an B in Basketweaving 400, your detail might look like
Class PreReq# class to meet grade required
BW501 1 ENG101 PASS
BW502 1 ENG102 PASS
BW502 1 ENG103 PASS
BW502 2 CHEM50 PASS
BW502 2 CHEM51 PASS
BW502 3 BW400 B
So your AND's are done by different prereq#'s, your OR's are done by adding a multiple options within the same prereq# . In this case, grouping by prereq# and checking that prereq matching >=1 in each grouping.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
May 17, 2018 at 10:48 am
That is an interesting idea. I could possibly use a window function to establish the PreReq#:
SELECT [t].[PERSONID]
,[t].[EVENT_ID]
,[p].[SEQUENCE_NUMBER]
,[p].[OPEN_PARENS]
,[p].[PREREQ_EVENT_ID]
,[p].[CLOSE_PARENS]
,[p].[LOGICAL_OPERATOR]
,Rank()
OVER (
PARTITION BY [PERSONID], CASE [LOGICAL_OPERATOR] WHEN '' THEN 'O' ELSE [LOGICAL_OPERATOR] END
ORDER BY [SEQUENCE_NUMBER]) AS [PreReq#]
FROM ##TRANSCRIPT [t]
JOIN ##PREREQUISITES [p]
ON [t].[ACADEMIC_YEAR] = [p].[ACADEMIC_YEAR]
AND [t].[ACADEMIC_TERM] = [p].[ACADEMIC_TERM]
AND [t].[EVENT_ID] = [p].[EVENT_ID]
AND [t].[SECTION] = [p].[SECTION]
OUTER APPLY (SELECT TOP 1 [FINAL_GRADE]
FROM ##TRANSCRIPT [t2]
WHERE [t].[PERSONID] = [t2].[PERSONID]
AND [p].[PREREQ_EVENT_ID] = [t2].[EVENT_ID]) [t2]
WHERE [t].[ACADEMIC_YEAR] = '2018'
AND [t].[ACADEMIC_TERM] = 'FALL'
AND [t].[EVENT_ID] = 'EG372'
ORDER BY [t].[PERSONID]
,[t].[EVENT_ID]
,[p].[SEQUENCE_NUMBER]
Creates:
| PERSONID | EVENT_ID | SEQUENCE_NUMBER | OPEN_PARENS | PREREQ_EVENT_ID | CLOSE_PARENS | LOGICAL_OPERATOR | PreReq# |
| 8299 | EG372 | 10 | (( | MS110 | O | 1 | |
| 8299 | EG372 | 20 | MS150 | ) | A | 1 | |
| 8299 | EG372 | 30 | ( | ET371 | O | 2 | |
| 8299 | EG372 | 40 | ES371 | ) | A | 2 | |
| 8299 | EG372 | 50 | ( | PS102 | O | 3 | |
| 8299 | EG372 | 60 | PS162 | ) | A | 3 | |
| 8299 | EG372 | 70 | ( | CO200 | O | 4 | |
| 8299 | EG372 | 80 | CE203 | O | 5 | ||
| 8299 | EG372 | 90 | CO201 | )) | 6 |
(The last record's LOGICAL_OPERATOR being blank is sort of a pain - would have to figure something else out about how to partition the window function)
As long as this student has a passing grade in each distinct PreReq# grouping, they meet the prerequisites.
I've also been thinking about joining (or applying the TOP 1, in case they've taken the class multiple times) the transcript again, deriving a "FULFILLED" bit based on whether they've taken and passed the classSELECT [t].[PERSONID]
,[t].[EVENT_ID]
,[p].[SEQUENCE_NUMBER]
,[p].[OPEN_PARENS]
,[p].[PREREQ_EVENT_ID]
,[p].[CLOSE_PARENS]
,[p].[LOGICAL_OPERATOR]
,CASE Isnull([t2].[FINAL_GRADE], 'F')
WHEN 'F' THEN '0'
ELSE '1'
END AS [FULFILLED]
FROM ##TRANSCRIPT [t]
JOIN ##PREREQUISITES [p]
ON [t].[ACADEMIC_YEAR] = [p].[ACADEMIC_YEAR]
AND [t].[ACADEMIC_TERM] = [p].[ACADEMIC_TERM]
AND [t].[EVENT_ID] = [p].[EVENT_ID]
AND [t].[SECTION] = [p].[SECTION]
OUTER APPLY (SELECT TOP 1 [FINAL_GRADE]
FROM ##TRANSCRIPT [t2]
WHERE [t].[PERSONID] = [t2].[PERSONID]
AND [p].[PREREQ_EVENT_ID] = [t2].[EVENT_ID]) [t2]
WHERE [t].[ACADEMIC_YEAR] = '2018'
AND [t].[ACADEMIC_TERM] = 'FALL'
ORDER BY [t].[PERSONID]
,[t].[EVENT_ID]
,[p].[SEQUENCE_NUMBER]
| PERSONID | EVENT_ID | SEQUENCE_NUMBER | OPEN_PARENS | PREREQ_EVENT_ID | CLOSE_PARENS | LOGICAL_OPERATOR | FULFILLED |
| 8299 | EG372 | 10 | (( | MS110 | O | 1 | |
| 8299 | EG372 | 20 | MS150 | ) | A | 0 | |
| 8299 | EG372 | 30 | ( | ET371 | O | 1 | |
| 8299 | EG372 | 40 | ES371 | ) | A | 0 | |
| 8299 | EG372 | 50 | ( | PS102 | O | 1 | |
| 8299 | EG372 | 60 | PS162 | ) | A | 0 | |
| 8299 | EG372 | 70 | ( | CO200 | O | 0 | |
| 8299 | EG372 | 80 | CE203 | O | 1 | ||
| 8299 | EG372 | 90 | CO201 | )) | 0 |
Then using a cursor to construct a string for each class, (( {MS110 FULFILLED} = 1 OR {MS150 FULFILLED} = 1 ) AND {ET371 FULFILLED} = 1... etc. I would then put that in a case statementSELECT CASE
WHEN ( ( 1 = 1
OR 0 = 1 )
AND 1 = 1 ) THEN 1
ELSE 0
END
and use dynamic SQL to evaluate it to a bit value as to whether they fulfilled the prerequisites or not...
It's ridiculous, I know. I see it as an exercise in SQL more than anything.
January 23, 2019 at 8:55 am
This is the solution I came up with. Obviously not ideal SQL, but it works within the limitations of the table structure, runs much faster and is (surprisingly) less onerous than the software's own means. Looks like some of the comments (ASCII table) will not maintain their format.
SET NOCOUNT ON
IF Object_id('tempdb..#TRANSCRIPT') IS NOT NULL
DROP TABLE #TRANSCRIPT
IF Object_id('tempdb..#PREREQS') IS NOT NULL
DROP TABLE #PREREQS
DECLARE @ACADEMIC_YEAR VARCHAR(4) = '2019'
DECLARE @ACADEMIC_TERM VARCHAR(10) = 'SUMM'
-- Populate #TRANSCRIPTS with course enrollments for the specified year/term
-- a cursor will later loop through this table, evaluating prerequisites
SELECT [PEOPLE_CODE_ID]
,[PEOPLE_ID]
,
.[ACADEMIC_YEAR]
,
.[ACADEMIC_TERM]
,
.[EVENT_ID]
,
.[EVENT_SUB_TYPE]
,
.[SECTION]
,CONVERT(BIT, 0) AS [FULFILLED_PREREQS]
INTO #TRANSCRIPT
FROM [TRANSCRIPTDETAIL]
-- cross apply to make sure the course at least has prerequisites
CROSS APPLY (SELECT TOP 1 1 AS [x]
FROM [SECTIONPREREQUISITE]
WHERE
.[ACADEMIC_YEAR] = [ACADEMIC_YEAR]
AND
.[ACADEMIC_TERM] = [ACADEMIC_TERM]
AND
.[ACADEMIC_SESSION] = [ACADEMIC_SESSION]
AND
.[EVENT_ID] = [EVENT_ID]
AND
.[EVENT_SUB_TYPE] = [EVENT_SUB_TYPE]
AND
.[SECTION] = [SECTION]) [sp]
WHERE
.[ACADEMIC_YEAR] = @ACADEMIC_YEAR
AND
.[ACADEMIC_TERM] = @ACADEMIC_TERM
AND
.[EVENT_SUB_TYPE] = 'LEC'
AND
.[ADD_DROP_WAIT] = 'A'
CREATE CLUSTERED INDEX cx_transcript
ON #TRANSCRIPT ([PEOPLE_CODE_ID], [EVENT_ID], [SECTION]);
-- Populate #PREREQS with the least amount of information necessary to
-- evaluate prerequisites
SELECT [t].[PEOPLE_CODE_ID]
,[t].[EVENT_ID]
,[sp].[SECTION]
,[sp].[SEQUENCE_NUMBER]
,[sp].[OPEN_PARENS]
,CASE [PREREQ_TYPE]
WHEN 'C' THEN [spc].[PREREQ_EVENT_ID]
WHEN 'P' THEN 'Permission by: '
+ [dbo].[Fnnamefrompcid]('P' + [spp].[PERMISSION_ID])
END AS [PREREQ]
,[sp].[CLOSE_PARENS]
,[sp].[LOGICAL_OPERATOR]
,[most_rec].[ACADEMIC_YEAR]
,[most_rec].[ACADEMIC_TERM]
,CASE [PREREQ_TYPE]
WHEN 'C' THEN [most_rec].[FINAL_GRADE]
WHEN 'P' THEN NULLIF([rp].[STATUS], 'WAITING')
END AS [OUTCOME]
INTO #PREREQS
FROM #TRANSCRIPT [t]
JOIN [SECTIONPREREQUISITE] [sp]
ON [t].[ACADEMIC_YEAR] = [sp].[ACADEMIC_YEAR]
AND [t].[ACADEMIC_TERM] = [sp].[ACADEMIC_TERM]
AND [t].[EVENT_ID] = [sp].[EVENT_ID]
AND [t].[EVENT_SUB_TYPE] = [sp].[EVENT_SUB_TYPE]
AND [t].[SECTION] = [sp].[SECTION]
LEFT JOIN [SECTIONPREREQCOURSE] [spc]
ON [sp].[PREREQ_ID] = [spc].[PREREQ_ID]
LEFT JOIN [SECTIONPREREQPERMISSION] [spp]
ON [sp].[PREREQ_ID] = [spp].[PREREQ_ID]
LEFT JOIN [REGISTRATIONPERMISSION] [rp]
ON [t].[ACADEMIC_YEAR] = [rp].[ACADEMIC_YEAR]
AND [t].[ACADEMIC_TERM] = [rp].[ACADEMIC_TERM]
AND [t].[EVENT_ID] = [rp].[EVENT_ID]
AND [t].[EVENT_SUB_TYPE] = [rp].[EVENT_SUB_TYPE]
AND [t].[SECTION] = [rp].[SECTION]
AND [t].[PEOPLE_ID] = [rp].[STUDENT_ID]
OUTER APPLY (SELECT TOP 1 [ACADEMIC_YEAR]
,[ACADEMIC_TERM]
,[FINAL_GRADE]
FROM [TRANSCRIPTDETAIL]
WHERE
.[PEOPLE_CODE_ID] = [t].[PEOPLE_CODE_ID]
AND
.[EVENT_ID] = [spc].[PREREQ_EVENT_ID]
AND
.[EVENT_SUB_TYPE] = [spc].[PREREQ_SUB_TYPE]
AND
.[ADD_DROP_WAIT] = 'A'
ORDER BY
.[END_DATE] DESC) [most_rec]
CREATE CLUSTERED INDEX cx_prereqs
ON #PREREQS ([PEOPLE_CODE_ID], [EVENT_ID], [SECTION]);
DECLARE @PEOPLE_CODE_ID VARCHAR(10)
DECLARE @EVENT_ID VARCHAR(10)
DECLARE @SECTION VARCHAR(3)
DECLARE @PREREQ_CASE NVARCHAR(256)
DECLARE @IS_FULFILLED BIT
DECLARE db_cursor CURSOR FAST_FORWARD FOR
SELECT [PEOPLE_CODE_ID]
,[EVENT_ID]
,[SECTION]
FROM #TRANSCRIPT
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @PEOPLE_CODE_ID, @EVENT_ID, @SECTION
-- For every PEOPLE_CODE_ID, EVENT_ID, SECTION we populated #TRANSCRIPT with...
WHILE @@FETCH_STATUS = 0
BEGIN
SET @PREREQ_CASE = ''
SET @IS_FULFILLED = 0
--
-- We want to build an expression that SQL can evaluate to tell us whether each student has fulfilled the prerequisites for courses
-- they are enrolled in.
--
-- e.g., Our cursor tells us P000023688 is enrolled in EG372-B
-- with that, we query #PREREQS for this information:
--
-- +----------------+----------+---------+-----------------+-------------+--------+--------------+------------------+---------+
-- | PEOPLE_CODE_ID | EVENT_ID | SECTION | SEQUENCE_NUMBER | OPEN_PARENS | PREREQ | CLOSE_PARENS | LOGICAL_OPERATOR | OUTCOME |
-- +----------------+----------+---------+-----------------+-------------+--------+--------------+------------------+---------+
-- | P000023688 | EG372 | B | 10 | (( | MS110 | | O | B- |
-- | P000023688 | EG372 | B | 20 | | MS150 | ) | A | NULL |
-- | P000023688 | EG372 | B | 30 | ( | ET371 | | O | A |
-- | P000023688 | EG372 | B | 40 | | ES371 | ) | A | NULL |
-- | P000023688 | EG372 | B | 50 | ( | PS102 | | O | TR |
-- | P000023688 | EG372 | B | 60 | | PS162 | ) | A | NULL |
-- | P000023688 | EG372 | B | 70 | ( | CO200 | | O | NULL |
-- | P000023688 | EG372 | B | 80 | | CE203 | | O | NULL |
-- | P000023688 | EG372 | B | 90 | | CO201 | )) | | NULL |
-- +----------------+----------+---------+-----------------+-------------+--------+--------------+------------------+---------+
--
-- (for us, for prerequisites to be considered "fulfilled", "OUTCOME" has to be non-NULL, anything other than an F grade)
-- So, with that, we want to build a string that essentially means:
-- (( 'B-' <> 'F' OR ISNULL(NULL,'F') <> 'F' ) AND ( 'A' <> 'F' OR ISNULL(NULL,'F') <> 'F' ) AND ( 'TR' <> 'F' OR ISNULL(NULL,'F') <> 'F' ) AND ( ISNULL(NULL,'F') <> 'F' OR ISNULL(NULL,'F') <> 'F' OR ISNULL(NULL,'F') <> 'F' ))
--
-- The query below does that, but reduces it further into simple boolean logic:
-- (( 1=1 OR 0=1 ) AND ( 1=1 OR 0=1 ) AND ( 1=1 OR 0=1 ) AND ( 0=1 OR 0=1 OR 0=1 ))
--
--
SELECT @PREREQ_CASE = @PREREQ_CASE + [OPEN_PARENS]
+ CASE Isnull([p].[OUTCOME], 'F')
WHEN 'F' THEN '0'
ELSE '1'
END
+ '=1' + [CLOSE_PARENS]
+ CASE [LOGICAL_OPERATOR]
WHEN 'O' THEN ' OR '
WHEN 'A' THEN ' AND '
ELSE ''
END
FROM #PREREQS [p]
WHERE [p].[PEOPLE_CODE_ID] = @PEOPLE_CODE_ID
AND [p].[EVENT_ID] = @EVENT_ID
AND [p].[SECTION] = @SECTION
ORDER BY [p].[PEOPLE_CODE_ID]
,[p].[EVENT_ID]
,[p].[SEQUENCE_NUMBER]
-- We then wrap a CASE statement around that logic:
-- SELECT @IS_FULFILLED = CASE WHEN (( 1=1 OR 0=1 ) AND ( 1=1 OR 0=1 ) AND ( 1=1 OR 0=1 ) AND ( 0=1 OR 0=1 OR 0=1 )) THEN 1 ELSE 0 END
SET @PREREQ_CASE = 'SELECT @IS_FULFILLED = CASE WHEN '
+ Isnull(NULLIF(@PREREQ_CASE, ''), '0=1')
+ ' THEN 1 ELSE 0 END'
-- Execute that SQL, storing the bit output in @IS_FULFILLED
BEGIN TRY
EXEC Sp_executesql
@PREREQ_CASE,
N'@IS_FULFILLED BIT OUTPUT',
@IS_FULFILLED OUTPUT
END TRY
BEGIN CATCH
DECLARE @ERROR_MSG VARCHAR(128) = 'Could not evaluate: ' + @prereq_case
+ Char(13) + Char(10) + 'For: ' + @ACADEMIC_YEAR + '/'
+ @ACADEMIC_TERM + ' ' + @EVENT_ID + '-' + @SECTION
RAISERROR (@ERROR_MSG,16,1);
END CATCH
-- UPDATE #TRANSCRIPT.FULFILLED_PREREQS for P000023688 EG372-B
UPDATE [t]
SET [FULFILLED_PREREQS] = @IS_FULFILLED
FROM #TRANSCRIPT [t]
WHERE [t].[PEOPLE_CODE_ID] = @PEOPLE_CODE_ID
AND [t].[EVENT_ID] = @EVENT_ID
AND [t].[SECTION] = @SECTION
FETCH NEXT FROM db_cursor INTO @PEOPLE_CODE_ID, @EVENT_ID, @SECTION
END
CLOSE db_cursor
DEALLOCATE db_cursor
SELECT [p].[PEOPLE_CODE_ID]
,[p].[LAST_NAME]
,[p].[FIRST_NAME]
,[t].[ACADEMIC_YEAR]
,[t].[ACADEMIC_TERM]
,[t].[EVENT_ID]
,[pr].[SECTION]
,[pr].[SEQUENCE_NUMBER]
,[pr].[OPEN_PARENS]
,[pr].[PREREQ]
,[pr].[CLOSE_PARENS]
,[pr].[LOGICAL_OPERATOR]
,Isnull([pr].[ACADEMIC_YEAR], '') AS [ACADEMIC_YEAR]
,Isnull([pr].[ACADEMIC_TERM], '') AS [ACADEMIC_TERM]
,Isnull([pr].[OUTCOME], '') AS [OUTCOME]
,[t].[FULFILLED_PREREQS]
FROM [PEOPLE] [p]
JOIN #TRANSCRIPT [t]
ON [p].[PEOPLE_CODE_ID] = [t].[PEOPLE_CODE_ID]
JOIN #PREREQS [pr]
ON [t].[PEOPLE_CODE_ID] = [pr].[PEOPLE_CODE_ID]
AND [t].[EVENT_ID] = [pr].[EVENT_ID]
AND [t].[SECTION] = [pr].[SECTION]
ORDER BY [FULFILLED_PREREQS]
,[t].[PEOPLE_CODE_ID]
,[t].[EVENT_ID]
,[pr].[SEQUENCE_NUMBER]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply