Posted by Ed Murphy on 08/21/06 06:36

On 20 Aug 2006 13:59:01 -0700, "rcamarda" <robc390@hotmail.com> wrote:

>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

[Back to original message]