Posted by rcamarda on 08/21/06 23:36

rcamarda wrote:
> I've been trying to solve this problem for better of 4 days:
<snip>

Well, I think I have found a compromise. I've argued that when a
student add/drops , add/drops, <rinse-repeat> it is really just noise.
Since most of our students either:
1. Add classes or
2. Add classes and drop
I decided to just get the first add and the last drop (if the net of
adds/drops is zero).
My solution was to check if the min(activity_dt) was equal to the date
I was processing and sum(activity_count) was > 0, then 1 else null.
(I union'd this to another, similar SQL statement, thats why I have the
AMT's = 0)
I could do this. Here is my SQL:

SELECT
a.student_id,
a.session_id,STUDENT_STATUS,
activity_dt,
b.session_day,
'HOME_CAMPUS_ID' = student_campus_id,
-- sum up the activities. If > 0 then check to see if the first (min())
registration is equal to the date from the main select. If it is, then
its 1 else 0
adds = case
when (select sum(activity_count)
from f_bi_registration_tracking
where a.student_id=student_id and
a.session_id=session_id and activity_dt<= a.activity_dt) > 0
and
(select min(activity_dt)
from f_bi_registration_tracking
where a.student_id=student_id and
a.session_id=session_id and activity_dt<= a.activity_dt and
activity_code='A') = activity_dt
then 1 else 0 end,
-- sum up the activities. If net is <= 0 then the student is a drop, so
return 1 else 0.
drops = case
when (select sum(activity_count) from
f_bi_registration_tracking where a.student_id=student_id and
a.session_id=session_id and activity_dt<= a.activity_dt)<=0
and (select MAX(activity_dt) from f_bi_registration_tracking where
a.student_id=student_id and a.session_id=session_id and activity_dt<=
a.activity_dt and activity_code='D') = activity_dt
THEN 1 ELSE 0 END,
TUITION_AMT = 0,
FEES_AMT = 0,
FINAID_AMT = 0,
TENDERED_AMT = 0,
UNCAT_AMT = 0,
UNKNOWN_AMT = 0,
OTHER_AMT =0
from
f_bi_registration_tracking a,
f_session_dates b,
d_bi_student c,
F_BI_Student_Summary_Session d
where
a.session_id=b.session_id and a.activity_dt=b.date
and a.student_skey=c.student_skey
and a.student_id=d.student_id
and a.session_id=d.session_id
group by a.student_id, a.session_id, activity_dt, b.session_day,
student_status, student_campus_id

[Back to original message]