Summarize Unique changes of registration

Giganews Newsgroups
Subject: Summarize Unique changes of registration
Posted by:  rcamarda (robc3…@hotmail.com)
Date: 20 Aug 2006

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

Replies