Hi all, first every post!
I am superneto w SQL but already love it although at the current time I'm working with very simple queries. I have a much more difficult query I need help finishing as my ability level is unfortunately way below this currently.
I have a dataset from a school for student assessments and am pulling into from the databasebuti am getting many records I don't want.
I am hoping to get this data to cooperate by running some extra code which will seach one field in my database for a numbethatsinr cluded in the title of another one. I have no idea how to do this or if the data types causes issues when doing this but this is what I am trying to achiieve:
Using the field Grade.short_name look in the field Classes.section_name where this can be found, if theres a match then I want it displayed. Issue is Classes.section_name contains more than just the number found from the Grade.short_name. That field could look like this 2a.ROOM If a student is in grade 2 I want it to be listed as a match. Is this possible?
Here is my code so far:
SELECT DISTINCT
Students.state_student_id,
Students.student_id,
Students.local_student_id,
Students.first_name,
Students.last_name,
Grade.short_name,
Schools.site_name,
Classes.section_name,
Teachers.teachers,
Assmnts.title,
Assmnts.description,
Assmnts.academic_year,
Assmnts.administered_at,
Assmnts.code_subject_area_id,
MathScores.percent_correct
FROM
dna_assessments.agg_student_responses MathScores
left JOIN dna_assessments.assessments Assmnts ON MathScores.assessment_id = Assmnts.assessment_id
left JOIN public.students Students ON MathScores.student_id = Students.student_id
left JOIN (SELECT distinct
c.student_id,
STRING_AGG(u.last_name, ', ') AS teachers,
c.site_id,
c.grade_level_id,
c.section_id
FROM matviews.ss_cube c
left JOIN public.users AS u
ON c.user_id = u.user_id
GROUP BY c.student_id, c.site_id, c.grade_level_id, c.section_id
) AS Teachers
ON Students.student_id = Teachers.student_id
left JOIN public.sections Classes ON Classes.section_id = Teachers.section_id
left JOIN public.student_session_aff Enrollment -- Student school enrollments as on page=Student_Enrollment_Controller
ON Students.student_id = Enrollment.student_id
AND MathScores.date_taken BETWEEN Enrollment.entry_date and Enrollment.leave_date -- Enrollment range in which student's score date falls. Assumes no missing leave dates or overlapping enrollment records.
-- AND Assmnts.administered_at BETWEEN Enrollment.entry_date and Enrollment.leave_date -- Enrollment range in which assessment date falls. Assumes no missing leave dates or overlapping enrollment records.
left JOIN public.grade_levels Grade ON Enrollment.grade_level_id = Grade.grade_level_id
left JOIN public.sessions Years ON Enrollment.session_id = Years.session_id -- Sites' regular/summer sessions (connects student_session_aff to sites)
left JOIN public.sites Schools ON Years.site_id = Schools.site_id
Where (Assmnts.title like '%Math Interim Assessment%' or Assmnts.title like '%HRM%') and (Classes.section_name like '%HRM%' or Classes.section_name like '%Homeroom%');
Any help would be much appreciated and really get me out of a data hole. Hope someone can help