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_SKEY" INT NOT NULL,
"SESSION_ID" CHAR(6) NOT NULL,
"FULL_CLASS_ID" CHAR(15) NOT NULL,
"ACTIVITY_CODE" CHAR(1) NOT NULL,
"ACTIVITY_DT" DATETIME NOT NULL,
"ACTIVITY_COUNT" INT)

INSERT INTO REGTRACK
VALUES(250,'200602','MAT100001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)
INSERT INTO REGTRACK
VALUES(250,'200602','ENG200001024','A',CONVERT(DAT ETIME,'2005-08-23'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','BUS100002011','A',CONVERT(DAT ETIME,'2005-07-18'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','CIS105001011','A',CONVERT(DAT ETIME,'2005-07-18'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','CIS105002011','A',CONVERT(DAT ETIME,'2005-07-19'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','CIS105001011','D',CONVERT(DAT ETIME,'2005-07-19'),1)
INSERT INTO REGTRACK
VALUES(260,'200602','CIS105002011','D',CONVERT(DAT ETIME,'2005-09-30'),1)
INSERT INTO REGTRACK
VALUES(265,'200602','PAD500001024','A',CONVERT(DAT ETIME,'2005-08-26'),1)
INSERT INTO REGTRACK
VALUES(266,'200602','CIS110001006','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(267,'200602','ECO100001004','A',CONVERT(DAT ETIME,'2005-09-07'),1)
INSERT INTO REGTRACK
VALUES(267,'200602','BUS520012016','A',CONVERT(DAT ETIME,'2005-09-07'),1)
INSERT INTO REGTRACK
VALUES(267,'200602','BUS520012016','D',CONVERT(DAT ETIME,'2005-10-10'),1)
INSERT INTO REGTRACK
VALUES(267,'200602','ECO100001004','D',CONVERT(DAT ETIME,'2005-10-10'),1)
INSERT INTO REGTRACK
VALUES(275,'200602','ITB300001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)
INSERT INTO REGTRACK
VALUES(275,'200602','BUS310006016','A',CONVERT(DAT ETIME,'2005-08-31'),1)
INSERT INTO REGTRACK
VALUES(288,'200602','FIN100002016','A',CONVERT(DAT ETIME,'2005-07-28'),1)
INSERT INTO REGTRACK
VALUES(288,'200602','POL300003016','A',CONVERT(DAT ETIME,'2005-07-28'),1)
INSERT INTO REGTRACK
VALUES(288,'200602','FIN100002016','D',CONVERT(DAT ETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(288,'200602','MKT200002016','A',CONVERT(DAT ETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(321,'200602','CIS105004010','A',CONVERT(DAT ETIME,'2005-08-03'),1)
INSERT INTO REGTRACK
VALUES(321,'200602','BUS100005010','A',CONVERT(DAT ETIME,'2005-08-03'),1)
INSERT INTO REGTRACK
VALUES(321,'200602','CIS105004010','D',CONVERT(DAT ETIME,'2005-11-15'),1)
INSERT INTO REGTRACK
VALUES(321,'200602','BUS100005010','D',CONVERT(DAT ETIME,'2005-11-28'),1)
INSERT INTO REGTRACK
VALUES(243172,'200602','ENG102001001','A',CONVERT( DATETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(243172,'200602','CIS105023016','A',CONVERT( DATETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(243172,'200602','ACC100002010','A',CONVERT( DATETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(334,'200602','MAT300009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)
INSERT INTO REGTRACK
VALUES(334,'200602','CIS111009016','A',CONVERT(DAT ETIME,'2005-08-29'),1)
INSERT INTO REGTRACK
VALUES(256542,'200602','CIS460002016','A',CONVERT( DATETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(256542,'200602','CIS500019016','A',CONVERT( DATETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(349,'200602','CIS500001003','A',CONVERT(DAT ETIME,'2005-09-22'),1)
INSERT INTO REGTRACK
VALUES(255713,'200602','BUS520008016','A',CONVERT( DATETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(359,'200602','BUS531001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)
INSERT INTO REGTRACK
VALUES(359,'200602','CIS514001029','A',CONVERT(DAT ETIME,'2005-09-01'),1)
INSERT INTO REGTRACK
VALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-09-16'),1)
INSERT INTO REGTRACK
VALUES(367,'200602','ENG102005001','D',CONVERT(DAT ETIME,'2005-10-26'),1)
INSERT INTO REGTRACK
VALUES(367,'200602','ENG102005001','A',CONVERT(DAT ETIME,'2005-11-08'),1)
INSERT INTO REGTRACK
VALUES(368,'200602','CIS110003016','A',CONVERT(DAT ETIME,'2005-08-16'),1)
INSERT INTO REGTRACK
VALUES(368,'200602','HUM300001016','A',CONVERT(DAT ETIME,'2005-08-16'),1)
INSERT INTO REGTRACK
VALUES(369,'200602','BUS530011016','A',CONVERT(DAT ETIME,'2005-09-13'),1)
INSERT INTO REGTRACK
VALUES(381,'200602','BUS100026016','A',CONVERT(DAT ETIME,'2005-08-02'),1)
INSERT INTO REGTRACK
VALUES(381,'200602','ECO405001016','A',CONVERT(DAT ETIME,'2005-08-02'),1)
INSERT INTO REGTRACK
VALUES(385,'200602','BUS100002008','A',CONVERT(DAT ETIME,'2005-07-27'),1)
INSERT INTO REGTRACK
VALUES(385,'200602','BUS107001008','A',CONVERT(DAT ETIME,'2005-07-27'),1)
INSERT INTO REGTRACK
VALUES(249922,'200602','ECO405008016','A',CONVERT( DATETIME,'2005-09-12'),1)
INSERT INTO REGTRACK
VALUES(249922,'200602','POL300011016','A',CONVERT( DATETIME,'2005-09-12'),1)
INSERT INTO REGTRACK
VALUES(249922,'200602','HUM100022016','A',CONVERT( DATETIME,'2005-09-12'),1)
INSERT INTO REGTRACK
VALUES(249922,'200602','HUM100022016','D',CONVERT( DATETIME,'2005-10-03'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','HUM400011016','A',CONVERT(DAT ETIME,'2005-08-17'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499001016','A',CONVERT(DAT ETIME,'2005-08-17'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499002016','A',CONVERT(DAT ETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499001016','D',CONVERT(DAT ETIME,'2005-09-21'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499001015','A',CONVERT(DAT ETIME,'2005-09-22'),1)
INSERT INTO REGTRACK
VALUES(395,'200602','CIS499002016','D',CONVERT(DAT ETIME,'2005-09-22'),1)
INSERT INTO REGTRACK
VALUES(397,'200602','ENG095001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(397,'200602','ENG096001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(397,'200602','ENG097001001','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(398,'200602','HUM200005016','A',CONVERT(DAT ETIME,'2005-08-05'),1)
INSERT INTO REGTRACK
VALUES(398,'200602','HUM400004016','A',CONVERT(DAT ETIME,'2005-08-05'),1)
INSERT INTO REGTRACK
VALUES(398,'200602','CIS427001016','A',CONVERT(DAT ETIME,'2005-08-05'),1)
INSERT INTO REGTRACK
VALUES(406,'200602','ECO550008016','A',CONVERT(DAT ETIME,'2005-08-01'),1)
INSERT INTO REGTRACK
VALUES(406,'200602','MAT540004016','A',CONVERT(DAT ETIME,'2005-08-01'),1)
INSERT INTO REGTRACK
VALUES(406,'200602','MAT540004016','D',CONVERT(DAT ETIME,'2005-11-14'),1)
INSERT INTO REGTRACK
VALUES(429,'200602','POL300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)
INSERT INTO REGTRACK
VALUES(429,'200602','SOC300006016','A',CONVERT(DAT ETIME,'2005-08-03'),1)
INSERT INTO REGTRACK
VALUES(429,'200602','ACC403003016','A',CONVERT(DAT ETIME,'2005-09-01'),1)
INSERT INTO REGTRACK
VALUES(429,'200602','SOC300006016','D',CONVERT(DAT ETIME,'2005-09-01'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','MAT540001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','BUS531001021','A',CONVERT(DAT ETIME,'2005-09-19'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ACC560001021','D',CONVERT(DAT ETIME,'2005-09-27'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ENG102001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','BUS533001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ACC560001021','A',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','BUS531001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','MAT540001021','D',CONVERT(DAT ETIME,'2005-09-28'),1)
INSERT INTO REGTRACK
VALUES(433,'200602','ENG102001021','D',CONVERT(DAT ETIME,'2005-09-29'),1)
INSERT INTO REGTRACK
VALUES(448,'200602','ENG102013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)
INSERT INTO REGTRACK
VALUES(448,'200602','HUM101013016','A',CONVERT(DAT ETIME,'2005-09-27'),1)
INSERT INTO REGTRACK
VALUES(459,'200602','HUM101002010','A',CONVERT(DAT ETIME,'2005-08-25'),1)
INSERT INTO REGTRACK
VALUES(459,'200602','BUS310001010','A',CONVERT(DAT ETIME,'2005-08-25'),1)
INSERT INTO REGTRACK
VALUES(466,'200602','HUM100004016','A',CONVERT(DAT ETIME,'2005-07-18'),1)
INSERT INTO REGTRACK
VALUES(466,'200602','CIS111003016','A',CONVERT(DAT ETIME,'2005-07-18'),1)
INSERT INTO REGTRACK
VALUES(479,'200602','BUS100050016','A',CONVERT(DAT ETIME,'2005-09-20'),1)
INSERT INTO REGTRACK
VALUES(253486,'200602','ENG102001012','A',CONVERT( DATETIME,'2005-10-05'),1)
INSERT INTO REGTRACK
VALUES(253486,'200602','MAT105001012','A',CONVERT( DATETIME,'2005-10-05'),1)
INSERT INTO REGTRACK
VALUES(490,'200602','BUS532001003','A',CONVERT(DAT ETIME,'2005-09-20'),1)
INSERT INTO REGTRACK
VALUES(509,'200602','ENG102021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)
INSERT INTO REGTRACK
VALUES(509,'200602','MAT100021016','A',CONVERT(DAT ETIME,'2005-10-01'),1)
INSERT INTO REGTRACK
VALUES(511,'200602','LEG100001012','A',CONVERT(DAT ETIME,'2005-08-29'),1)
INSERT INTO REGTRACK
VALUES(556,'200602','LEG100013016','A',CONVERT(DAT ETIME,'2005-09-24'),1)
INSERT INTO REGTRACK
VALUES(556,'200602','SOC304001003','A',CONVERT(DAT ETIME,'2005-09-24'),1)
INSERT INTO REGTRACK
VALUES(576,'200602','ACC100002026','A',CONVERT(DAT ETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(576,'200602','BUS100043016','A',CONVERT(DAT ETIME,'2005-08-30'),1)
INSERT INTO REGTRACK
VALUES(581,'200602','CIS288001010','A',CONVERT(DAT ETIME,'2005-09-08'),1)
INSERT INTO REGTRACK
VALUES(581,'200602','CIS450001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)
INSERT INTO REGTRACK
VALUES(581,'200602','CIS286001002','A',CONVERT(DAT ETIME,'2005-09-08'),1)
INSERT INTO REGTRACK
VALUES(583,'200602','BUS490001017','A',CONVERT(DAT ETIME,'2005-08-09'),1)
INSERT INTO REGTRACK
VALUES(583,'200602','SOC300004016','A',CONVERT(DAT ETIME,'2005-08-09'),1)
INSERT INTO REGTRACK
VALUES(583,'200602','BUS490001017','D',CONVERT(DAT ETIME,'2005-09-07'),1)

[Ed Murphy]

On 20 Aug 2006 13:59:01 -0700, "rcamarda" <ro*****@hotmail.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]

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_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