Stuck on getting only first valid record from associated table

[ghbeers]

I am absolutely stuck on finding ONLY 1 record that exists in an associated table that meets my minimum requirement. I have a table STUDENT_TERMS that is for a student and specific term. In the table are keys to all of the course info the student has signed up for the semester. When I go to the course info records (STUDENT_ACAD_CRED), I want to stop after I find the first record where the grade is not NULL. Everything I've tried is not working or it takes way too long to execute. Here is my script so far. The last CTE is the ome I just can't get right, really missing something. Any help will be greatly appreciated.

-- Student with 0 GPA or Completed credits for Term
WITH USER_TERM AS
(
SELECT DISTINCT
T.TERMS_ID AS 'USR_TERM',
TL.TERM_CENSUS_DATES AS 'CUR_CENSUS_DATE',
T.TERMS_ID + '*UG' AS 'STTR_TERM',
T.TERM_END_DATE AS 'TERM_END'
FROM TERMS T
LEFT JOIN TERMS_LS TL ON T.TERMS_ID = TL.TERMS_ID
WHERE TL.TERM_CENSUS_DATES IS NOT NULL
AND TL.POS = 1
AND TL.TERMS_ID = '2017/FA'
-- AND TL.TERMS_ID = UPPER( <<Enter Term YYYY/SS [Text]>> )
)
--SELECT * FROM USER_TERM
,RPT_STTR_STUS AS
(
SELECT DISTINCT TOP 60
SUBSTRING(STTR.STUDENT_TERMS_ID, 1,7) 'STUDENT_ID',
STTR.STUDENT_TERMS_ID 'STTR_KEY',
STTRS.STTR_STATUS 'STTR_STATUS'

FROM STUDENT_TERMS STTR
JOIN USER_TERM UT ON STTR.STUDENT_TERMS_ID LIKE '%' + UT.STTR_TERM
JOIN STTR_STATUSES STTRS ON STTR.STUDENT_TERMS_ID = STTRS.STUDENT_TERMS_ID
AND STTRS.POS = 1
AND STTRS.STTR_STATUS IN ('R','P','T')
)
--SELECT * FROM RPT_STTR_STUS
,HAVE_GRADE_STUS AS
(
SELECT DISTINCT
RSS.STUDENT_ID 'STUDENT_ID'
FROM RPT_STTR_STUS RSS
WHERE RSS.STUDENT_ID =
(
SELECT TOP 1 STAC1.STC_PERSON_ID
FROM STUDENT_ACAD_CRED STAC1
JOIN STUDENT_TERMS_LS STTRL ON STAC1.STUDENT_ACAD_CRED_ID = STTRL.STTR_STUDENT_ACAD_CRED
AND STAC1.STC_VERIFIED_GRADE IS NOT NULL
)
)

SELECT * FROM HAVE_GRADE_STUS

 

[fredericofonseca]

not clear what you are after. Could you please give us a sample of inputs for each table and desired output.

As for being slow "JOIN USER_TERM UT ON STTR.STUDENT_TERMS_ID LIKE '%' + UT.STTR_TERM" will most likely be a killer

Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

FAQ219-2884
FAQ181-2886

 

[ghbeers]

I figured this out about 2 seconds before I saw you response come in. Here's what I ended up doing with the last CTE that is working correctly. Thank you for getting back to me.

HAVE_GRADE_STUS AS
(
SELECT distinct
RPT.STTR_KEY
FROM RPT_STTR_STUS RPT
JOIN STUDENT_TERMS_LS STTR ON RPT.STTR_KEY = STTR.STUDENT_TERMS_ID
LEFT JOIN (SELECT STAC.STUDENT_ACAD_CRED_ID AS STAC_KEY,
Row_Number() Over (Partition by STAC.STUDENT_ACAD_CRED_ID
Order by STAC.STC_START_DATE) As RowId
FROM STUDENT_ACAD_CRED STAC
WHERE STAC.STC_VERIFIED_GRADE IS NOT NULL) SEL1
ON SEL1.STAC_KEY = STTR.STTR_STUDENT_ACAD_CRED
AND RowId = 1
)