Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Select Record for current term 1

Status
Not open for further replies.

sxschech

Technical User
Jul 11, 2002
1,033
US
I "cheated" and got my results using MS-Access, but was wondering if I could get the results directly in oracle (assuming that performance would be quicker). Would like to select a person's record for the most current of the two terms of the criteria. If person A has a record with 200830 and a record for 200840, only want the 200840, if they only have 200830 or only have 200840, then would take that record. I saw a few examples, but seemed like they were keyed off date, since I need to key off both ID and Date, ended up concatenating ID and Date with the Access query further below. I imagine there is a more proper way of doing this query?

This is the oracle query:
Code:
select distinct aa.academic_period, aa.id, aa.name, aa.student_level, 
ac.student_level acStudent_Level, 
aa.student_population,  
aa.student_population_desc, 
ac.student_population acStudent_Population, 
ac.student_classification acStudent_Classification, 
aa.College, 
ss.CollegeCampus, 
aa.program,   
aa.program_desc, 
aa.major_desc, 
sc.cohort scCohort, ac.block_schedule, 
CASE WHEN ac.college in ('ED', 'PS') AND (sc.cohort is not null AND substr(sc.cohort,5,2) <> 'SF') THEN ac.college || '-OFF' 
ELSE ac.college
END AS acCollege, ac.program acprogram, ac.major_desc acMajor_Desc, ss.cohort, ss.cohort_desc,  
ss.applied, ss.accepted, ss.deposit, ss.enrolled, 
en.CURRENT_TIME_STATUS, en.CURRENT_TIME_STATUS_DESC, en.TOTAL_CREDITS,
ac.Enrolled_Ind, ac.Registered_Ind,  
CASE WHEN ac.student_classification in
          ( '1D' , '2D' , '3D' , '1P' , '2P' , '3P' , '4P' , '4D' ) then 'PT' 
     WHEN ac.student_classification in
          ( '1F' , '2F' , '3F' , '4F' )                             then 'FT' 
     WHEN ac.college = 'PS' and ac.block_schedule is not null       then 'FT' 
     WHEN ac.student_level = 'GR' and en.total_non_ceu_credits >= 6 then 'FT' 
     WHEN ac.student_level = 'DR' and en.total_non_ceu_credits >= 6 then 'FT' 
     WHEN ac.student_level = 'UG' and en.total_non_ceu_credits >=12 then 'FT'
ELSE 'PT' 
END AS TimeStatus
from admissions_application aa, UN_AS_STATUS_SLOT ss, 
     academic_study ac, MST_Enrollment en, 
     student_cohort sc
where aa.person_uid=ss.pidm 
  and aa.person_uid=ac.person_uid 
  and aa.person_uid=en.person_uid 
  and aa.person_uid=sc.person_uid(+) 
  and aa.academic_period=ss.term 
  and aa.academic_period=ac.academic_period 
  and aa.academic_period=en.academic_period 
  and aa.academic_period=sc.academic_period(+) 
  and aa.program=ss.program 
  and aa.academic_period between '200830' and '200840'
  and ss.enrolled=1
  order by 2

Here is the query I did in Access.
Code:
SELECT * 
FROM sqlCENS_200840
WHERE ID&Academic_Period IN (
                        Select sqlCENS_200840.ID&Max([ACADEMIC_PERIOD]) 
                        FROM sqlCENS_200840
                        GROUP BY sqlCENS_200840.ID
                                                     );
 
Code:
select max(aa.academic_period), aa.id, aa.name, aa.student_level from admissions_application aa group by
aa.id, aa.name, aa.student_level

-----------------------------------------
I cannot be bought. Find leasing information at
 
Jaxtell,

That's not really the same thing, since you would only be able to select columns that appear in the group by. Sxschech wants a whole record, but the record with the highest of two possible term dates.

Sxschech,

When you say you want to use both ID and date, do you mean you want the highest date for that particular ID. If so, you would use a query such as:

Code:
select distinct aa.academic_period, aa.id, aa.name, aa.student_level, 
ac.student_level acStudent_Level, 
aa.student_population,  
aa.student_population_desc, 
ac.student_population acStudent_Population, 
ac.student_classification acStudent_Classification, 
aa.College, 
ss.CollegeCampus, 
aa.program,   
aa.program_desc, 
aa.major_desc, 
sc.cohort scCohort, ac.block_schedule, 
CASE WHEN ac.college in ('ED', 'PS') AND (sc.cohort is not null AND substr(sc.cohort,5,2) <> 'SF') THEN ac.college || '-OFF' 
ELSE ac.college
END AS acCollege, ac.program acprogram, ac.major_desc acMajor_Desc, ss.cohort, ss.cohort_desc,  
ss.applied, ss.accepted, ss.deposit, ss.enrolled, 
en.CURRENT_TIME_STATUS, en.CURRENT_TIME_STATUS_DESC, en.TOTAL_CREDITS,
ac.Enrolled_Ind, ac.Registered_Ind,  
CASE WHEN ac.student_classification in
          ( '1D' , '2D' , '3D' , '1P' , '2P' , '3P' , '4P' , '4D' ) then 'PT' 
     WHEN ac.student_classification in
          ( '1F' , '2F' , '3F' , '4F' )                             then 'FT' 
     WHEN ac.college = 'PS' and ac.block_schedule is not null       then 'FT' 
     WHEN ac.student_level = 'GR' and en.total_non_ceu_credits >= 6 then 'FT' 
     WHEN ac.student_level = 'DR' and en.total_non_ceu_credits >= 6 then 'FT' 
     WHEN ac.student_level = 'UG' and en.total_non_ceu_credits >=12 then 'FT'
ELSE 'PT' 
END AS TimeStatus
from admissions_application aa, UN_AS_STATUS_SLOT ss, 
     academic_study ac, MST_Enrollment en, 
     student_cohort sc
where aa.person_uid=ss.pidm 
  and aa.person_uid=ac.person_uid 
  and aa.person_uid=en.person_uid 
  and aa.person_uid=sc.person_uid(+) 
  and aa.academic_period=ss.term 
  and aa.academic_period=ac.academic_period 
  and aa.academic_period=en.academic_period 
  and aa.academic_period=sc.academic_period(+) 
  and aa.program=ss.program 
  and aa.academic_period between '200830' and '200840'
  and ss.enrolled=1
  and aa.academic_period = 
  (select max(b.academic_period)
     from admissions_application b
    where b.person_uid = aa.person_id)
  order by 2

 
Thank you so much Dagon. That provided what I was looking for. Glad you understood what I was after and it is good to know there was a simple solution for multiple criteria situations.
 
Sxschech,

As always, Dagon proposes an excellent solution. My concern with that type of solution was, Will the code:
Code:
WHERE...aa.academic_period = 
  (select max(b.academic_period)
     from admissions_application b
    where b.person_uid = aa.person_id
    [b]-- without the other 12 qualifying WHERE filters[/b]
  )...
...return a value that is not represented by the diminished result set that the 12 other qualifying WHERE filters produce?

If that doesn't worry you, then it doesn't worry me. <grin>

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
SantaMufasa,

Do you have an alternate criteria code that would preclude unintended results?
 
Frankly, that issue is precisely why I did not post a solution originally...Because it would take potentially a full copy of your orginal code beginning with FROM... to appear as the sub-query, just to guarantee a proper result set:
Code:
select distinct aa.academic_period, aa.id, aa.name, aa.student_level, 
ac.student_level acStudent_Level, 
aa.student_population,  
aa.student_population_desc, 
ac.student_population acStudent_Population, 
ac.student_classification acStudent_Classification, 
aa.College, 
ss.CollegeCampus, 
aa.program,   
aa.program_desc, 
aa.major_desc, 
sc.cohort scCohort, ac.block_schedule, 
CASE WHEN ac.college in ('ED', 'PS') AND (sc.cohort is not null AND substr(sc.cohort,5,2) <> 'SF') THEN ac.college || '-OFF' 
ELSE ac.college
END AS acCollege, ac.program acprogram, ac.major_desc acMajor_Desc, ss.cohort, ss.cohort_desc,  
ss.applied, ss.accepted, ss.deposit, ss.enrolled, 
en.CURRENT_TIME_STATUS, en.CURRENT_TIME_STATUS_DESC, en.TOTAL_CREDITS,
ac.Enrolled_Ind, ac.Registered_Ind,  
CASE WHEN ac.student_classification in
          ( '1D' , '2D' , '3D' , '1P' , '2P' , '3P' , '4P' , '4D' ) then 'PT' 
     WHEN ac.student_classification in
          ( '1F' , '2F' , '3F' , '4F' )                             then 'FT' 
     WHEN ac.college = 'PS' and ac.block_schedule is not null       then 'FT' 
     WHEN ac.student_level = 'GR' and en.total_non_ceu_credits >= 6 then 'FT' 
     WHEN ac.student_level = 'DR' and en.total_non_ceu_credits >= 6 then 'FT' 
     WHEN ac.student_level = 'UG' and en.total_non_ceu_credits >=12 then 'FT'
ELSE 'PT' 
END AS TimeStatus
from admissions_application aa, UN_AS_STATUS_SLOT ss, 
     academic_study ac, MST_Enrollment en, 
     student_cohort sc
where aa.person_uid=ss.pidm 
  and aa.person_uid=ac.person_uid 
  and aa.person_uid=en.person_uid 
  and aa.person_uid=sc.person_uid(+) 
  and aa.academic_period=ss.term 
  and aa.academic_period=ac.academic_period 
  and aa.academic_period=en.academic_period 
  and aa.academic_period=sc.academic_period(+) 
  and aa.program=ss.program 
  and aa.academic_period between '200830' and '200840'
  and ss.enrolled=1
  and aa.academic_period = 
  (select max(b.academic_period)
     from [B][I]admissions_application b, UN_AS_STATUS_SLOT ss, 
          academic_study ac, MST_Enrollment en, 
          student_cohort sc
    where b.person_uid = aa.person_id
      and b.person_uid=ss.pidm 
      and b.person_uid=ac.person_uid 
      and b.person_uid=en.person_uid 
      and b.person_uid=sc.person_uid(+) 
      and b.academic_period=ss.term 
      and b.academic_period=ac.academic_period 
      and b.academic_period=en.academic_period 
      and b.academic_period=sc.academic_period(+) 
      and b.program=ss.program 
      and b.academic_period between '200830' and '200840'
      and ss.enrolled=1[/I][/B])
  order by 2
Anyone, please post if my assertion is faulty.

[santa]Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm. The cost will be your freedoms and your liberty.”
 
I think that might be overkill. I suspect that the only real condition you might need would be the one on "academic_period between '200830' and '200840'" (although, if these are the most recent periods, then even that might not be necessary).

I suspect you could probably get away with:

Code:
select distinct aa.academic_period, aa.id, aa.name, aa.student_level, 
ac.student_level acStudent_Level, 
aa.student_population,  
aa.student_population_desc, 
ac.student_population acStudent_Population, 
ac.student_classification acStudent_Classification, 
aa.College, 
ss.CollegeCampus, 
aa.program,   
aa.program_desc, 
aa.major_desc, 
sc.cohort scCohort, ac.block_schedule, 
CASE WHEN ac.college in ('ED', 'PS') AND (sc.cohort is not null AND substr(sc.cohort,5,2) <> 'SF') THEN ac.college || '-OFF' 
ELSE ac.college
END AS acCollege, ac.program acprogram, ac.major_desc acMajor_Desc, ss.cohort, ss.cohort_desc,  
ss.applied, ss.accepted, ss.deposit, ss.enrolled, 
en.CURRENT_TIME_STATUS, en.CURRENT_TIME_STATUS_DESC, en.TOTAL_CREDITS,
ac.Enrolled_Ind, ac.Registered_Ind,  
CASE WHEN ac.student_classification in
          ( '1D' , '2D' , '3D' , '1P' , '2P' , '3P' , '4P' , '4D' ) then 'PT' 
     WHEN ac.student_classification in
          ( '1F' , '2F' , '3F' , '4F' )                             then 'FT' 
     WHEN ac.college = 'PS' and ac.block_schedule is not null       then 'FT' 
     WHEN ac.student_level = 'GR' and en.total_non_ceu_credits >= 6 then 'FT' 
     WHEN ac.student_level = 'DR' and en.total_non_ceu_credits >= 6 then 'FT' 
     WHEN ac.student_level = 'UG' and en.total_non_ceu_credits >=12 then 'FT'
ELSE 'PT' 
END AS TimeStatus
from (select * from admissions_application aa
      where aa.academic_period = 
     (select max(b.academic_period)
        from admissions_application b
       where b.person_uid = aa.person_id
         and b.academic_period between '200830'  
      and '200840') 
      and aa.academic_period between '200830' and '200840') aa, 
    UN_AS_STATUS_SLOT ss, 
    academic_study ac, 
    MST_Enrollment en, 
    student_cohort sc
where aa.person_uid=ss.pidm 
  and aa.person_uid=ac.person_uid 
  and aa.person_uid=en.person_uid 
  and aa.person_uid=sc.person_uid(+) 
  and aa.academic_period=ss.term 
  and aa.academic_period=ac.academic_period 
  and aa.academic_period=en.academic_period 
  and aa.academic_period=sc.academic_period(+) 
  and aa.program=ss.program 
  and aa.academic_period between '200830' and '200840'
  and ss.enrolled=1
order by 2
 
Thanks Dagon, I'll run the query tomorrow and let you know...encountered a difference in number of records, so need to check if problem is a result of the query or the data as the record in the database is a bit ambiguous.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top