Multi Year Criteria Join

[sxschech]

I have a query where the Year criteria are stored in a Cutoff Table. The main query uses the years from the cutoff table to restrict which years should be returned on the output. This is working fine. However, we needed to add a new set of criteria (an additional join statement) that only applies to 2009 and forward. If I do an inner join, then naturally I end up excluding 2008. If I do a left join, then I end up getting data for 2008 and other extraneous data. Can this query be modified to be able to run this portion of the criteria but not for 2008 and earlier? To get the data out, I ended up creating a union query with the dates and joins hard coded and inner join. This of course is not very flexible and will create problems when we move into the following years. I have put the portion of the query that isn't supposed to run for 2008 and earlier in red.

The red portion was added because after 2008, the records could be changed, but keep the original application date, since we are trying to see the data at specific points of time, a person who applied in 200920 on 10-JAN-2009 decided to change to 200940 on 05-MAR-2009. If we run a list, using a cutoff date of 01-FEB-2009, we would not want to see this person. Thus, we have to check the Source_Date and have both the earliest Source_Date and Application_Date be less than/equal to the cutoff date. The Source_Date does not work properly prior to 2009, so that is why we can't use the red portion for earlier time frames. The way the query is written now, by having multiple years, it pulls out more records than it should. It could be because of students who where in 2008 and switched to 2009? When I hard code for the specific year, this doesn't happen and the counts are as we expect.

Cutoff Table
[tt]
Row# RPT_YEAR CUTOFF_DT REPORT_IND

1 200830 5/1/2008 Y
2 200840 5/1/2008 Y
3 200940 5/1/2009 Y
4 200930 5/1/2009 Y
[/tt]

select p.person_uid,            --as PIDM, 
       p.id_number               as id, 
       full_name_lfmi          as Name, 
       a.academic_period,       --as TERM,
       substr(a.academic_period, 1, 4) as year,
       substr(a.academic_period, 5, 2) as sem,
       a.application_number, --            as appl_nbr,
       trunc(a.application_date)       as application_date,
       latest_decision,
       latest_decision_date,
       program,
       a.college                       as college,
       a.STUDENT_LEVEL                 as student_level, 
       a.student_population            as stu_population,
       decode(a.student_population, 'F', 'Freshmen', 'T', 'Transfer',
              'G', 'Masters', 'D', 'Doctorate') as stu_pop,
       case 
         when a.residency in ('0','D','N','P') then 'D'        -- domestic
         when a.residency in ('A','E','I')     then 'I'        -- international
         else null
       end                             as residency,
       p.gender                        as gender, 
       p.primary_ethnicity             as ethnicity,
       p.nation_of_citizenship_desc    as citizen_nation_desc,
       1 Applied 
from cutoff2, mst_person p, 
mst_Admissions_Application a   
[red]
    LEFT JOIN (select sarchkl_pidm person_uid, id_number, 
     sarchkl_term_code_entry academic_period, 
     sarchkl_appl_no application_number, 
     min(sarchkl_source_date) Source_Date
    from mst_person p, sarchkl SAR, cutoff2
    where p.person_uid = sar.sarchkl_pidm
    and   sarchkl_term_code_entry = rpt_year --in ('200930','200940') 
    and   sarchkl_source_date <= cutoff_dt
    group by sarchkl_pidm, id_number, sarchkl_term_code_entry , sarchkl_appl_no) Source
    ON a.person_uid=source.person_uid
    and a.academic_period=source.academic_period
    and a.application_number=source.application_number
    and source.academic_period > '200840'
[/red]    
WHERE p.PERSON_UID = a.PERSON_UID
  AND a.application_date <= cutoff_dt 
  AND a.Academic_period = rpt_year --in ('200930','200940')
      AND SUBSTR(a.PROGRAM,1,2) <> 'ND' 
      AND a.PROGRAM IS NOT NULL  
      AND a.STUDENT_POPULATION <> 'S'
      AND college in ('LA', 'SC', 'BN', 'NS', 'ED', 'PS')
      AND a.Curriculum_Priority = 1
      and f_valid_id(p.id_number) = 'Y'
ORDER BY Full_Name_LFMI;

 

[Turkbear]

Hi,
Can you expand your CASE statement, or add a new one to handle data with dates >= 2009 ?

Not sure how, exactly, but someone may have an idea..





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[Dagon]

Can you explain what 200920, 200940 etc means ? It's not a year or a year-month, so what is it (20th day of the year ?)

 

[sxschech]

Turkbear:
I have thought about the case statement, but wasn't sure where/how can be implemented in the join.

Dagon:
200920, 200940, etc refer to Yearly Academic terms (Spring, Summer, Fall, etc)

 

[ChrisHunt]

I'm having difficulty following what you're trying to do, but I suspect it might put you on the right track if you used a LEFT OUTER JOIN to connect to the not-there-before-2009 table.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[sxschech]

Ideally, I would like to only run the portion in red when the year is greater than 2008 as if it were a kind of condition join. Something along the lines of Case year = 2008 then skip this join section.

Is your suggestion to do this:

LEFT JOIN (select sarchkl_pidm person_uid, id_number,
     sarchkl_term_code_entry academic_period,
     sarchkl_appl_no application_number,
     min(sarchkl_source_date) Source_Date
    from mst_person p, 
         sarchkl SAR
[b]    LEFT JOIN cutoff2
              sarchkl_term_code_entry = rpt_year --in ('200930','200940')
    and   sarchkl_source_date <= cutoff_dt
    where p.person_uid = sar.sarchkl_pidm[/b]
    group by sarchkl_pidm, id_number, sarchkl_term_code_entry , sarchkl_appl_no) Source
    ON a.person_uid=source.person_uid
    and a.academic_period=source.academic_period
    and a.application_number=source.application_number
    and source.academic_period > '200840'

 

[sxschech]

I think I came up with a solution. I added
[tt]
and case when a.academic_period > '200840' and source_date is null then 0 else 1 end = 1
[/tt]


to the where clause and it seems to be doing what I want to exclude the unqualified records.

...
...
WHERE p.PERSON_UID = a.PERSON_UID
  AND a.application_date <= cutoff_dt 
  AND a.Academic_period = rpt_year --in ('200930','200940')
      AND SUBSTR(a.PROGRAM,1,2) <> 'ND' 
      AND a.PROGRAM IS NOT NULL  
      AND a.STUDENT_POPULATION  'S'
      AND college in ('LA', 'SC', 'BN', 'NS', 'ED', 'PS')
      AND a.Curriculum_Priority = 1
      and f_valid_id(p.id_number) = 'Y'
[red]      and case when a.academic_period > '200840' and source_date is null then 0 else 1 end = 1 [/red]
ORDER BY Full_Name_LFMI;