SQL Query - Need to search for value date range in SELECT or Subquery

user_14860219 0
Viewed 48

Good morning,

I'm am admittedly a bit of a novice when it comes to SQL Queries... That being said, my skills are improving!

I have a student demographics query that also needs to fill in "Learning Mode" preferences for specific date ranges.

During the 2020 pandemic, our school is using a hybrid model. Students can be in-person, hybrid, or remote. I'm joining a table that tracks that preference. The challenge is that the student's status can change. For example, a student could start out hybrid at the beginning of the year and the parent could choose to move them to remote. When that happens a new entry is put into the table. I need to be able to show the student's learning preference by specific months or date ranges.

Here is an example of query of the Student Learning preference table:

SELECT SLP.STUDENTSDCID, DECODE (SLP.LEARNINGPREFERENCE,01,'in-person',02,'Hybrid',03,'Remote') AS LEARNINGPREFERENCE, 
        TO_CHAR(SLP.EFFECTIVEDATE,'MM/DD/YYYY') AS EFFECTIVEDATE
        
    FROM S_STU_LEARNINGPREF_C SLP

ORDER BY SLP.STUDENTSDCID
STUDENT ID PREFERENCE DATE
3432 Hybrid 09/16/2020
3506 Hybrid 09/16/2020
3541 Hybrid 09/16/2020
3563 in-person 09/17/2020
3563 Hybrid 09/16/2020
3588 Hybrid 09/16/2020

Right now my query works on a basic level. I have to search multiple tables and reformat some stuff. However, unfortunately, when it comes to the learning preference I need to manually search the results for duplicate student entries, look at the effective date, and put the preference into the appropriate column.

Here is the entire query:

SELECT S.STUDENT_NUMBER, S.STATE_STUDENTNUMBER AS SASID, NVL(SCF.PSCORE_LEGAL_FIRST_NAME,S.FIRST_NAME) AS FNAME, 
    NVL(SCF.PSCORE_LEGAL_MIDDLE_NAME,S.MIDDLE_NAME) AS MNAME, NVL(SCF.PSCORE_LEGAL_LAST_NAME,S.LAST_NAME) AS LNAME,
    TO_CHAR(S.DOB,'MM/DD/YYYY') AS DOB, '' AS FREE_REDUCED_IND, 
    NVL(NVL(LTRIM(regexp_substr(S.MOTHER, '[^,]+', 1, 2)),LTRIM(regexp_substr(S.FATHER, '[^,]+', 1, 2))),SCF.GUARDIAN_FN) AS PRIMARY_CONTACT_FNAME,
    NVL(NVL(LTRIM(regexp_substr(S.MOTHER, '[^,]+', 1, 1)),LTRIM(regexp_substr(S.FATHER, '[^,]+', 1, 1))),SCF.GUARDIAN_LN) AS PRIMARY_CONTACT_LNAME,
    regexp_substr(S.MAILING_STREET, '[^,]+', 1, 1) AS STU_STREET_ADDR_1, regexp_substr(S.MAILING_STREET, '[^,]+', 1, 2) AS STU_STREET_ADDR_2, 
    S.MAILING_CITY AS STU_STREET_CITY, S.MAILING_ZIP AS STU_STREET_ZIP, '0'||S.SCHOOLID AS SCHOOL_ID,
    DECODE(S.SCHOOLID,1690005,'0169',1730005,'0173',1730010,'0173',2500005,'0250',7400405,'0740',7400505,'0740') AS DISTRICT_CODE,'' AS OCT_MODE, '' AS NOV_MODE
    
    DECODE (SLP.LEARNINGPREFERENCE,01,'in-person',02,'Hybrid',03,'Remote') AS "LEARNINGPREFERENCE", TO_CHAR(SLP.EFFECTIVEDATE,'MM/DD/YYYY') AS EFFECTIVE_DATE
    
FROM STUDENTS S
    LEFT JOIN STUDENTCOREFIELDS SCF ON S.dcid = SCF.studentsdcid
    LEFT JOIN S_STU_LEARNINGPREF_C SLP ON S.dcid = SLP.studentsdcid
    
WHERE S.ENROLL_STATUS = '0'

ORDER BY S.LAST_NAME, S.FIRST_NAME

Ideally I would be able to add some kind of subqueries in my select that would search the learning preference table and put the resulting entry into the appropriate column(s).

Any help would be appreciated.

Thank you.

oracle-sqldeveloper oracle sql
2 Answers

The most obvious solution is to create a table which contains preferences, e.g.

create table preference as
  select '01' pref_id, 'in-person' name from dual union all
  select '02'        , 'hybrid'         from dual union all
  select '03'        , 'remote'         from dual;

and join it with the rest of your query:

select 
  s.studentsdcid,
  p.name learningpreference,
  to_char(s.effectivedate, 'mm/dd/yyyy') effectivedate
from s_stu_learningpref_c s join preference p on p.id = s.learningpreference
order by s.studentsdcid;

Otherwise, if you can't create a table (though, I don't see a reason why wouldn't you), you could use a subquery or the CTE (common table expression, also know as the WITH factoring clause), e.g.

with preference (pref_id, name) as
  (select '01' pref_id, 'in-person' name from dual union all
   select '02'        , 'hybrid'         from dual union all
   select '03'        , 'remote'         from dual
  )
select 
  s.studentsdcid,
  p.name learningpreference,
  to_char(s.effectivedate, 'mm/dd/yyyy') effectivedate
from s_stu_learningpref_c s join preference p on p.id = s.learningpreference
order by s.studentsdcid;
user_90979060

Will there always be a preference for each student? If yes, you can use a join to a CTE or subquery to get the max preference date.

e.g.

SELECT S.STUDENT_NUMBER, S.STATE_STUDENTNUMBER AS SASID, NVL(SCF.PSCORE_LEGAL_FIRST_NAME,S.FIRST_NAME) AS FNAME, 
    NVL(SCF.PSCORE_LEGAL_MIDDLE_NAME,S.MIDDLE_NAME) AS MNAME, NVL(SCF.PSCORE_LEGAL_LAST_NAME,S.LAST_NAME) AS LNAME,
    TO_CHAR(S.DOB,'MM/DD/YYYY') AS DOB, '' AS FREE_REDUCED_IND, 
    NVL(NVL(LTRIM(regexp_substr(S.MOTHER, '[^,]+', 1, 2)),LTRIM(regexp_substr(S.FATHER, '[^,]+', 1, 2))),SCF.GUARDIAN_FN) AS PRIMARY_CONTACT_FNAME,
    NVL(NVL(LTRIM(regexp_substr(S.MOTHER, '[^,]+', 1, 1)),LTRIM(regexp_substr(S.FATHER, '[^,]+', 1, 1))),SCF.GUARDIAN_LN) AS PRIMARY_CONTACT_LNAME,
    regexp_substr(S.MAILING_STREET, '[^,]+', 1, 1) AS STU_STREET_ADDR_1, regexp_substr(S.MAILING_STREET, '[^,]+', 1, 2) AS STU_STREET_ADDR_2, 
    S.MAILING_CITY AS STU_STREET_CITY, S.MAILING_ZIP AS STU_STREET_ZIP, '0'||S.SCHOOLID AS SCHOOL_ID,
    DECODE(S.SCHOOLID,1690005,'0169',1730005,'0173',1730010,'0173',2500005,'0250',7400405,'0740',7400505,'0740') AS DISTRICT_CODE,'' AS OCT_MODE, '' AS NOV_MODE
    
    DECODE (SLP.LEARNINGPREFERENCE,01,'in-person',02,'Hybrid',03,'Remote') AS "LEARNINGPREFERENCE", TO_CHAR(SLP.EFFECTIVEDATE,'MM/DD/YYYY') AS EFFECTIVE_DATE
    
FROM STUDENTS S
    LEFT JOIN STUDENTCOREFIELDS SCF ON S.dcid = SCF.studentsdcid
    LEFT JOIN S_STU_LEARNINGPREF_C SLP ON S.dcid = SLP.studentsdcid
    JOIN (Select studentsdcid, max(EFFECTIVEDATE) lastDate from S_STU_LEARNINGPREF_C group by studentsdcid) lastPref ON SLP.studentsdcid = lastPref.studentsdcid and SLP.EFFECTIVEDATE = lastPref.lastDate
    
WHERE S.ENROLL_STATUS = '0'

ORDER BY S.LAST_NAME, S.FIRST_NAME
user_79891350
Related