Summarize Unique changes of registration

[rcamarda]

I've been trying to solve this problem for better of 4 days:

We summarize registrations of students on a daily basis, however they
are net changes.
Example:
A student registers one class for the first time for the Fall quarter
on a Monday. A report would reflect that change for Monday.
Next, the same student adds another class on Tuesday. Since the student
was already counted on Monday, I dont want the student to count on
Tuesday.
On Wednesday, the student decides to drop both classes. Since the
student no longer has any classes, I wish to decrement the student
count on Wednesday for that one student.
If the same student adds a new class on Friday, then they would count
on Friday since their previous classes net to zero.
After the end of the session, I would be able to sum up the daily
balance of adds and drops and it would net out to be equal with the
total number of unique students registered for the quarter.
- Students can add and or drop classes on the same day, or on different
days.
- I need to know when the net effect when a student is changed and
reflect that quantity on the date for the quarter (SESSION).
We have reports on our legacy system written in IBM's Universe (its a
business basic). Its pretty straight forward as we would traverse the
data using a basic program. However, trying to something in batch in
SQL has eluded me.

What I am looking to select:
What I am looking to select:
SESSION DATE STUDENT_ADDS STUDENT_DROPS
200602 2005-07-18 1 0
200602 2005-08-23 1 0
2006002 2005-09-30 0 1

TIA
Rob
(I thought of getting the first registration, and last drop (if any),
but it wont work as there can be adds and drops in between)

(I ran the DDL this time, and it returned what I expected when I
selected it)

CREATE TABLE "DBO"."REGTRACK "
("STUDENT_SK EY" INT NOT NULL,
"SESSION_ID " CHAR(6) NOT NULL,
"FULL_CLASS _ID" CHAR(15) NOT NULL,
"ACTIVITY_C ODE" CHAR(1) NOT NULL,
"ACTIVITY_D T" DATETIME NOT NULL,
"ACTIVITY_COUNT " INT)



INSERT INTO REGTRACK
VALUES(250,'200 602','MAT100001 024','A',CONVER T(DATETIME,'200 5-08-23'),1)
INSERT INTO REGTRACK
VALUES(250,'200 602','ENG200001 024','A',CONVER T(DATETIME,'200 5-08-23'),1)
INSERT INTO REGTRACK
VALUES(260,'200 602','BUS100002 011','A',CONVER T(DATETIME,'200 5-07-18'),1)
INSERT INTO REGTRACK
VALUES(260,'200 602','CIS105001 011','A',CONVER T(DATETIME,'200 5-07-18'),1)
INSERT INTO REGTRACK
VALUES(260,'200 602','CIS105002 011','A',CONVER T(DATETIME,'200 5-07-19'),1)
INSERT INTO REGTRACK
VALUES(260,'200 602','CIS105001 011','D',CONVER T(DATETIME,'200 5-07-19'),1)
INSERT INTO REGTRACK
VALUES(260,'200 602','CIS105002 011','D',CONVER T(DATETIME,'200 5-09-30'),1)
INSERT INTO REGTRACK
VALUES(265,'200 602','PAD500001 024','A',CONVER T(DATETIME,'200 5-08-26'),1)
INSERT INTO REGTRACK
VALUES(266,'200 602','CIS110001 006','A',CONVER T(DATETIME,'200 5-09-19'),1)
INSERT INTO REGTRACK
VALUES(267,'200 602','ECO100001 004','A',CONVER T(DATETIME,'200 5-09-07'),1)
INSERT INTO REGTRACK
VALUES(267,'200 602','BUS520012 016','A',CONVER T(DATETIME,'200 5-09-07'),1)
INSERT INTO REGTRACK
VALUES(267,'200 602','BUS520012 016','D',CONVER T(DATETIME,'200 5-10-10'),1)
INSERT INTO REGTRACK
VALUES(267,'200 602','ECO100001 004','D',CONVER T(DATETIME,'200 5-10-10'),1)
INSERT INTO REGTRACK
VALUES(275,'200 602','ITB300001 016','A',CONVER T(DATETIME,'200 5-08-17'),1)
INSERT INTO REGTRACK
VALUES(275,'200 602','BUS310006 016','A',CONVER T(DATETIME,'200 5-08-31'),1)
INSERT INTO REGTRACK
VALUES(288,'200 602','FIN100002 016','A',CONVER T(DATETIME,'200 5-07-28'),1)
INSERT INTO REGTRACK
VALUES(288,'200 602','POL300003 016','A',CONVER T(DATETIME,'200 5-07-28'),1)
INSERT INTO REGTRACK
VALUES(288,'200 602','FIN100002 016','D',CONVER T(DATETIME,'200 5-08-30'),1)
INSERT INTO REGTRACK
VALUES(288,'200 602','MKT200002 016','A',CONVER T(DATETIME,'200 5-08-30'),1)
INSERT INTO REGTRACK
VALUES(321,'200 602','CIS105004 010','A',CONVER T(DATETIME,'200 5-08-03'),1)
INSERT INTO REGTRACK
VALUES(321,'200 602','BUS100005 010','A',CONVER T(DATETIME,'200 5-08-03'),1)
INSERT INTO REGTRACK
VALUES(321,'200 602','CIS105004 010','D',CONVER T(DATETIME,'200 5-11-15'),1)
INSERT INTO REGTRACK
VALUES(321,'200 602','BUS100005 010','D',CONVER T(DATETIME,'200 5-11-28'),1)
INSERT INTO REGTRACK
VALUES(243172,' 200602','ENG102 001001','A',CON VERT(DATETIME,' 2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(243172,' 200602','CIS105 023016','A',CON VERT(DATETIME,' 2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(243172,' 200602','ACC100 002010','A',CON VERT(DATETIME,' 2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(334,'200 602','MAT300009 016','A',CONVER T(DATETIME,'200 5-08-29'),1)
INSERT INTO REGTRACK
VALUES(334,'200 602','CIS111009 016','A',CONVER T(DATETIME,'200 5-08-29'),1)
INSERT INTO REGTRACK
VALUES(256542,' 200602','CIS460 002016','A',CON VERT(DATETIME,' 2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(256542,' 200602','CIS500 019016','A',CON VERT(DATETIME,' 2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(349,'200 602','CIS500001 003','A',CONVER T(DATETIME,'200 5-09-22'),1)
INSERT INTO REGTRACK
VALUES(255713,' 200602','BUS520 008016','A',CON VERT(DATETIME,' 2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(359,'200 602','BUS531001 029','A',CONVER T(DATETIME,'200 5-09-01'),1)
INSERT INTO REGTRACK
VALUES(359,'200 602','CIS514001 029','A',CONVER T(DATETIME,'200 5-09-01'),1)
INSERT INTO REGTRACK
VALUES(367,'200 602','ENG102005 001','A',CONVER T(DATETIME,'200 5-09-16'),1)
INSERT INTO REGTRACK
VALUES(367,'200 602','ENG102005 001','D',CONVER T(DATETIME,'200 5-10-26'),1)
INSERT INTO REGTRACK
VALUES(367,'200 602','ENG102005 001','A',CONVER T(DATETIME,'200 5-11-08'),1)
INSERT INTO REGTRACK
VALUES(368,'200 602','CIS110003 016','A',CONVER T(DATETIME,'200 5-08-16'),1)
INSERT INTO REGTRACK
VALUES(368,'200 602','HUM300001 016','A',CONVER T(DATETIME,'200 5-08-16'),1)
INSERT INTO REGTRACK
VALUES(369,'200 602','BUS530011 016','A',CONVER T(DATETIME,'200 5-09-13'),1)
INSERT INTO REGTRACK
VALUES(381,'200 602','BUS100026 016','A',CONVER T(DATETIME,'200 5-08-02'),1)
INSERT INTO REGTRACK
VALUES(381,'200 602','ECO405001 016','A',CONVER T(DATETIME,'200 5-08-02'),1)
INSERT INTO REGTRACK
VALUES(385,'200 602','BUS100002 008','A',CONVER T(DATETIME,'200 5-07-27'),1)
INSERT INTO REGTRACK
VALUES(385,'200 602','BUS107001 008','A',CONVER T(DATETIME,'200 5-07-27'),1)
INSERT INTO REGTRACK
VALUES(249922,' 200602','ECO405 008016','A',CON VERT(DATETIME,' 2005-09-12'),1)
INSERT INTO REGTRACK
VALUES(249922,' 200602','POL300 011016','A',CON VERT(DATETIME,' 2005-09-12'),1)
INSERT INTO REGTRACK
VALUES(249922,' 200602','HUM100 022016','A',CON VERT(DATETIME,' 2005-09-12'),1)
INSERT INTO REGTRACK
VALUES(249922,' 200602','HUM100 022016','D',CON VERT(DATETIME,' 2005-10-03'),1)
INSERT INTO REGTRACK
VALUES(395,'200 602','HUM400011 016','A',CONVER T(DATETIME,'200 5-08-17'),1)
INSERT INTO REGTRACK
VALUES(395,'200 602','CIS499001 016','A',CONVER T(DATETIME,'200 5-08-17'),1)
INSERT INTO REGTRACK
VALUES(395,'200 602','CIS499002 016','A',CONVER T(DATETIME,'200 5-09-21'),1)
INSERT INTO REGTRACK
VALUES(395,'200 602','CIS499001 016','D',CONVER T(DATETIME,'200 5-09-21'),1)
INSERT INTO REGTRACK
VALUES(395,'200 602','CIS499001 015','A',CONVER T(DATETIME,'200 5-09-22'),1)
INSERT INTO REGTRACK
VALUES(395,'200 602','CIS499002 016','D',CONVER T(DATETIME,'200 5-09-22'),1)
INSERT INTO REGTRACK
VALUES(397,'200 602','ENG095001 001','A',CONVER T(DATETIME,'200 5-09-19'),1)
INSERT INTO REGTRACK
VALUES(397,'200 602','ENG096001 001','A',CONVER T(DATETIME,'200 5-09-19'),1)
INSERT INTO REGTRACK
VALUES(397,'200 602','ENG097001 001','A',CONVER T(DATETIME,'200 5-09-19'),1)
INSERT INTO REGTRACK
VALUES(398,'200 602','HUM200005 016','A',CONVER T(DATETIME,'200 5-08-05'),1)
INSERT INTO REGTRACK
VALUES(398,'200 602','HUM400004 016','A',CONVER T(DATETIME,'200 5-08-05'),1)
INSERT INTO REGTRACK
VALUES(398,'200 602','CIS427001 016','A',CONVER T(DATETIME,'200 5-08-05'),1)
INSERT INTO REGTRACK
VALUES(406,'200 602','ECO550008 016','A',CONVER T(DATETIME,'200 5-08-01'),1)
INSERT INTO REGTRACK
VALUES(406,'200 602','MAT540004 016','A',CONVER T(DATETIME,'200 5-08-01'),1)
INSERT INTO REGTRACK
VALUES(406,'200 602','MAT540004 016','D',CONVER T(DATETIME,'200 5-11-14'),1)
INSERT INTO REGTRACK
VALUES(429,'200 602','POL300006 016','A',CONVER T(DATETIME,'200 5-08-03'),1)
INSERT INTO REGTRACK
VALUES(429,'200 602','SOC300006 016','A',CONVER T(DATETIME,'200 5-08-03'),1)
INSERT INTO REGTRACK
VALUES(429,'200 602','ACC403003 016','A',CONVER T(DATETIME,'200 5-09-01'),1)
INSERT INTO REGTRACK
VALUES(429,'200 602','SOC300006 016','D',CONVER T(DATETIME,'200 5-09-01'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','ACC560001 021','A',CONVER T(DATETIME,'200 5-09-19'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','MAT540001 021','A',CONVER T(DATETIME,'200 5-09-19'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','BUS531001 021','A',CONVER T(DATETIME,'200 5-09-19'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','ACC560001 021','D',CONVER T(DATETIME,'200 5-09-27'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','ENG102001 021','A',CONVER T(DATETIME,'200 5-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','BUS533001 021','A',CONVER T(DATETIME,'200 5-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','ACC560001 021','A',CONVER T(DATETIME,'200 5-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','BUS531001 021','D',CONVER T(DATETIME,'200 5-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','MAT540001 021','D',CONVER T(DATETIME,'200 5-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200 602','ENG102001 021','D',CONVER T(DATETIME,'200 5-09-29'),1)
INSERT INTO REGTRACK
VALUES(448,'200 602','ENG102013 016','A',CONVER T(DATETIME,'200 5-09-27'),1)
INSERT INTO REGTRACK
VALUES(448,'200 602','HUM101013 016','A',CONVER T(DATETIME,'200 5-09-27'),1)
INSERT INTO REGTRACK
VALUES(459,'200 602','HUM101002 010','A',CONVER T(DATETIME,'200 5-08-25'),1)
INSERT INTO REGTRACK
VALUES(459,'200 602','BUS310001 010','A',CONVER T(DATETIME,'200 5-08-25'),1)
INSERT INTO REGTRACK
VALUES(466,'200 602','HUM100004 016','A',CONVER T(DATETIME,'200 5-07-18'),1)
INSERT INTO REGTRACK
VALUES(466,'200 602','CIS111003 016','A',CONVER T(DATETIME,'200 5-07-18'),1)
INSERT INTO REGTRACK
VALUES(479,'200 602','BUS100050 016','A',CONVER T(DATETIME,'200 5-09-20'),1)
INSERT INTO REGTRACK
VALUES(253486,' 200602','ENG102 001012','A',CON VERT(DATETIME,' 2005-10-05'),1)
INSERT INTO REGTRACK
VALUES(253486,' 200602','MAT105 001012','A',CON VERT(DATETIME,' 2005-10-05'),1)
INSERT INTO REGTRACK
VALUES(490,'200 602','BUS532001 003','A',CONVER T(DATETIME,'200 5-09-20'),1)
INSERT INTO REGTRACK
VALUES(509,'200 602','ENG102021 016','A',CONVER T(DATETIME,'200 5-10-01'),1)
INSERT INTO REGTRACK
VALUES(509,'200 602','MAT100021 016','A',CONVER T(DATETIME,'200 5-10-01'),1)
INSERT INTO REGTRACK
VALUES(511,'200 602','LEG100001 012','A',CONVER T(DATETIME,'200 5-08-29'),1)
INSERT INTO REGTRACK
VALUES(556,'200 602','LEG100013 016','A',CONVER T(DATETIME,'200 5-09-24'),1)
INSERT INTO REGTRACK
VALUES(556,'200 602','SOC304001 003','A',CONVER T(DATETIME,'200 5-09-24'),1)
INSERT INTO REGTRACK
VALUES(576,'200 602','ACC100002 026','A',CONVER T(DATETIME,'200 5-08-30'),1)
INSERT INTO REGTRACK
VALUES(576,'200 602','BUS100043 016','A',CONVER T(DATETIME,'200 5-08-30'),1)
INSERT INTO REGTRACK
VALUES(581,'200 602','CIS288001 010','A',CONVER T(DATETIME,'200 5-09-08'),1)
INSERT INTO REGTRACK
VALUES(581,'200 602','CIS450001 002','A',CONVER T(DATETIME,'200 5-09-08'),1)
INSERT INTO REGTRACK
VALUES(581,'200 602','CIS286001 002','A',CONVER T(DATETIME,'200 5-09-08'),1)
INSERT INTO REGTRACK
VALUES(583,'200 602','BUS490001 017','A',CONVER T(DATETIME,'200 5-08-09'),1)
INSERT INTO REGTRACK
VALUES(583,'200 602','SOC300004 016','A',CONVER T(DATETIME,'200 5-08-09'),1)
INSERT INTO REGTRACK
VALUES(583,'200 602','BUS490001 017','D',CONVER T(DATETIME,'200 5-09-07'),1)

[Ed Murphy]

Re: Summarize Unique changes of registration

On 20 Aug 2006 13:59:01 -0700, "rcamarda" <robc390@hotmai l.comwrote:

>We summarize registrations of students on a daily basis, however they
>are net changes.
>Example:
>A student registers one class for the first time for the Fall quarter
>on a Monday. A report would reflect that change for Monday.
>Next, the same student adds another class on Tuesday. Since the student
>was already counted on Monday, I dont want the student to count on
>Tuesday.
>On Wednesday, the student decides to drop both classes. Since the
>student no longer has any classes, I wish to decrement the student
>count on Wednesday for that one student.
>If the same student adds a new class on Friday, then they would count
>on Friday since their previous classes net to zero.
>After the end of the session, I would be able to sum up the daily
>balance of adds and drops and it would net out to be equal with the
>total number of unique students registered for the quarter.
>- Students can add and or drop classes on the same day, or on different
>days.
>- I need to know when the net effect when a student is changed and
>reflect that quantity on the date for the quarter (SESSION).
>We have reports on our legacy system written in IBM's Universe (its a
>business basic). Its pretty straight forward as we would traverse the
>data using a basic program. However, trying to something in batch in
>SQL has eluded me.

Make sure you've properly evaluated the pros and cons of having BB
traverse the MS SQL data. That said, I would probably implement this
as outlined in the following pseudocode:

create temp table X
create temp table Y
cursor #1 iterates over all students in the table
cursor #2 iterates over the student's activities in date order
switch (activity code)
case 'A'
if X contains no records for the student
then add (date, student, 'first add') to Y
insert (student, class) into X
break
case 'D'
if X contains exactly one record for the student
then add (date, student, 'last drop') to Y
delete (student, class) from X
break
end switch
end cursor #2
end cursor #1
temp table Y now contains the data you want

[rcamarda]

Re: Summarize Unique changes of registration

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-repeatit 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_co unt) 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,ST UDENT_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_co unt)
from f_bi_registrati on_tracking
where a.student_id=st udent_id and
a.session_id=se ssion_id and activity_dt<= a.activity_dt) 0
and
(select min(activity_dt )
from f_bi_registrati on_tracking
where a.student_id=st udent_id and
a.session_id=se ssion_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_co unt) from
f_bi_registrati on_tracking where a.student_id=st udent_id and
a.session_id=se ssion_id and activity_dt<= a.activity_dt)< =0
and (select MAX(activity_dt ) from f_bi_registrati on_tracking where
a.student_id=st udent_id and a.session_id=se ssion_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_registrati on_tracking a,
f_session_dates b,
d_bi_student c,
F_BI_Student_Su mmary_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