Conceptual ideas - 2 tables one changes other complete Cursors?

[rcamarda]

I think cursors might help me, but I'm not sure. I'm looking for ideas
on how to solve a problem I have.

Consider two tables, one table contains student information (very wide
100 fields) , the other historical changes of the student information,
(narrow, just fields that record changes).

As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
RECORD_DT and has one student in it.

Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
records, since the student changed their major 2 times.

I want to end up with a table the contains 3 rows, the 2 changes to the
Major and the current student record. I want each row to be complete.
Everything that I have tried (joins, outer joins, union) I end up with
some field being null (in my example, the STUDENT_NAME would on be in
the original row, and null for the two changes)
I know this is pretty vague, but I am wondering if this is a place to
use CURSORS?
(Some of you may recognize this as a type 2 dimension or slowly
changing dimension as used in a data warehouse, which it is. I need to
build up my historical changes to I can feed it to my warehouse. I have
the current student record, and all the descreet changes made to the
student.)
TIA
Rob

[Stu]

How about:

--represents current status
SELECT STUDENT_ID, STUDENT_MAJOR, RECORD_DT, STUDENT_NAME
FROM Table1
UNION ALL
SELECT t2.STUDENT_ID, t2.STUDENT_MAJO R, t2.CHANGE_DT, t1.STUDENT_NAME
FROM Table2 t2 JOIN Table1 t1 ON t2.STUDENT_ID = t1.STUDENT_ID

Or am I missing something?

Stu

[Stu]

How about:

--represents current status
SELECT STUDENT_ID, STUDENT_MAJOR, RECORD_DT, STUDENT_NAME
FROM Table1
UNION ALL
SELECT t2.STUDENT_ID, t2.STUDENT_MAJO R, t2.CHANGE_DT, t1.STUDENT_NAME
FROM Table2 t2 JOIN Table1 t1 ON t2.STUDENT_ID = t1.STUDENT_ID

Or am I missing something?

Stu

[AntiTrust]

"rcamarda" <rc******@cable speed.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I think cursors might help me, but I'm not sure. I'm looking for ideas
on how to solve a problem I have.

Consider two tables, one table contains student information (very wide
100 fields) , the other historical changes of the student information,
(narrow, just fields that record changes).

As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
RECORD_DT and has one student in it.

Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
records, since the student changed their major 2 times.

I want to end up with a table the contains 3 rows, the 2 changes to the
Major and the current student record. I want each row to be complete.
Everything that I have tried (joins, outer joins, union) I end up with
some field being null (in my example, the STUDENT_NAME would on be in
the original row, and null for the two changes)
I know this is pretty vague, but I am wondering if this is a place to
use CURSORS?
(Some of you may recognize this as a type 2 dimension or slowly
changing dimension as used in a data warehouse, which it is. I need to
build up my historical changes to I can feed it to my warehouse. I have
the current student record, and all the descreet changes made to the
student.)
TIA
Rob

Hi Rob,

Cursors are the devils toenails. There has to be a join that will do what
you want. Can you identify specifically what your primary key is? Once we
have this we might move forward.

regards

SYM.

[AntiTrust]

"rcamarda" <rc******@cable speed.com> wrote in message
news:11******** **************@ z14g2000cwz.goo glegroups.com.. .

I think cursors might help me, but I'm not sure. I'm looking for ideas
on how to solve a problem I have.

Consider two tables, one table contains student information (very wide
100 fields) , the other historical changes of the student information,
(narrow, just fields that record changes).

As an example Table one has STUDENT_ID, STUDENT_MAJOR, STUDENT_NAME,
RECORD_DT and has one student in it.

Table two contains STUDENT_ID, STUDENT_MAJOR , CHANGE_DT and contains 2
records, since the student changed their major 2 times.

I want to end up with a table the contains 3 rows, the 2 changes to the
Major and the current student record. I want each row to be complete.
Everything that I have tried (joins, outer joins, union) I end up with
some field being null (in my example, the STUDENT_NAME would on be in
the original row, and null for the two changes)
I know this is pretty vague, but I am wondering if this is a place to
use CURSORS?
(Some of you may recognize this as a type 2 dimension or slowly
changing dimension as used in a data warehouse, which it is. I need to
build up my historical changes to I can feed it to my warehouse. I have
the current student record, and all the descreet changes made to the
student.)
TIA
Rob

Hi Rob,

Cursors are the devils toenails. There has to be a join that will do what
you want. Can you identify specifically what your primary key is? Once we
have this we might move forward.

regards

SYM.

[rcamarda]

CREATE TABLE "dbo"."F_Studen t_Sample"
(
"STUDENT_ID " VARCHAR(20) NOT NULL,
"STUDENT_LEAD_I D" VARCHAR(10) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_LASTNA ME" VARCHAR(40) NULL,
"STUDENT_FIRSTN AME" VARCHAR(40) NULL,
"STUDENT_CAMPUS _ID" VARCHAR(10) NULL,
"STUDENT_ADMREP _ID" VARCHAR(10) NULL,
"STUDENT_MARKET CODE_ID" VARCHAR(10) NULL
)
;

insert into [F_Student_Sampl e] VALUES
('100','900','2 005-05-01','CAMARDA',' ROBERT','HOST*0 01','TLS*123',' I20')
CREATE TABLE "dbo"."Student_ Changes_Sample"
(
"STUDENT_ID " VARCHAR(20) NOT NULL,
"CHANGE_COD E" NUMERIC(19) NULL,
"CHANGE" VARCHAR(100) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_CAMPUS _ID" VARCHAR(10) NULL,
"STUDENT_ADMREP _ID" VARCHAR(10) NULL
)
;
-- The addtion of the two columns my be redundant, (STUDENT_CAMPUS _ID
and STUDENT_ADMREP_ ID)
-- CHANGE_CODE = 7, CHANGE will contain the new value for
STUDENT_CAMPUS_ ID
-- CHANGE_CODE = 10, CHANGE will contain the new value for
STUDENT_ADMREP_ ID
-- STUDENT_ID is my "primary key" but it is not unique in this case,
since I need all the rows.

INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'HOST* 002','2001-01-03','HOST*002', NULL)
INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'HOST* 003','2002-04-03','HOST*003', NULL)
INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'HOST* 004','2003-02-13','HOST*004', NULL)
INSERT INTO [Student_Changes _Sample] VALUES
('100',7,'DMI10 ','2003-02-13',NULL,'DMI10 ')

I need to end up with 5 rows of information, the current record found
in F_STUDENT_SAMPL E, and the 4 changes in the apporiate columns with
all the fields populated.
Thanks

[rcamarda]

Thanks Stu,
I'm ending up with null data again.
Using you example, I created:
select
student_id,
student_campus_ id,
'' as student_lastnam e
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_camp us_id,
t2.student_last name
from
student t2 join student_changes t1 on t2.student_id = t1.student_id
WHERE T2.STUDENT_ID = '1000139200'
I get:
1000139200 NULL
1000139200 003
1000139200 006
1000139200 016
1000139200 HOST*006 Iverson Iii

I need the last name (Iverson Iii) to be on all rows

[rcamarda]

Thanks Stu,
I'm ending up with null data again.
Using you example, I created:
select
student_id,
student_campus_ id,
'' as student_lastnam e
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_camp us_id,
t2.student_last name
from
student t2 join student_changes t1 on t2.student_id = t1.student_id
WHERE T2.STUDENT_ID = '1000139200'
I get:
1000139200 NULL
1000139200 003
1000139200 006
1000139200 016
1000139200 HOST*006 Iverson Iii

I need the last name (Iverson Iii) to be on all rows

[David Portas]

Try this:

SELECT S.student_id, S.student_lead_ id, C.record_dt,
S.student_lastn ame, S.student_first name,
COALESCE(C.stud ent_campus_id,S .student_campus _id) AS student_campus_ id,
COALESCE(C.stud ent_admrep_id,S .student_admrep _id) AS student_admrep_ id,
S.student_marke tcode_id
FROM f_student_sampl e AS S,
student_changes _sample AS C

--
David Portas
SQL Server MVP
--

[David Portas]

Try this:

SELECT S.student_id, S.student_lead_ id, C.record_dt,
S.student_lastn ame, S.student_first name,
COALESCE(C.stud ent_campus_id,S .student_campus _id) AS student_campus_ id,
COALESCE(C.stud ent_admrep_id,S .student_admrep _id) AS student_admrep_ id,
S.student_marke tcode_id
FROM f_student_sampl e AS S,
student_changes _sample AS C

--
David Portas
SQL Server MVP
--