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_MAJOR, 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_MAJOR, 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******@cablespeed.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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******@cablespeed.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.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_Student_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"STUDENT_LEAD_ID" VARCHAR(10) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_LASTNAME" VARCHAR(40) NULL,
"STUDENT_FIRSTNAME" VARCHAR(40) NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL,
"STUDENT_MARKETCODE_ID" VARCHAR(10) NULL
)
;

insert into [F_Student_Sample] VALUES
('100','900','2005-05-01','CAMARDA','ROBERT','HOST*001','TLS*123','I20')
CREATE TABLE "dbo"."Student_Changes_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"CHANGE_CODE" 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_SAMPLE, and the 4 changes in the apporiate columns with
all the fields populated.
Thanks

[rcamarda]

CREATE TABLE "dbo"."F_Student_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"STUDENT_LEAD_ID" VARCHAR(10) NULL,
"RECORD_DT" DATETIME NULL,
"STUDENT_LASTNAME" VARCHAR(40) NULL,
"STUDENT_FIRSTNAME" VARCHAR(40) NULL,
"STUDENT_CAMPUS_ID" VARCHAR(10) NULL,
"STUDENT_ADMREP_ID" VARCHAR(10) NULL,
"STUDENT_MARKETCODE_ID" VARCHAR(10) NULL
)
;

insert into [F_Student_Sample] VALUES
('100','900','2005-05-01','CAMARDA','ROBERT','HOST*001','TLS*123','I20')
CREATE TABLE "dbo"."Student_Changes_Sample"
(
"STUDENT_ID" VARCHAR(20) NOT NULL,
"CHANGE_CODE" 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_SAMPLE, 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_lastname
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_campus_id,
t2.student_lastname
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_lastname
from student_changes where student_id = '1000139200'
union
select
t2.student_id,
t2.student_campus_id,
t2.student_lastname
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_lastname, S.student_firstname,
COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
S.student_marketcode_id
FROM f_student_sample 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_lastname, S.student_firstname,
COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
S.student_marketcode_id
FROM f_student_sample AS S,
student_changes_sample AS C

--
David Portas
SQL Server MVP
--

[David Portas]

CORRECTION: Add the WHERE clause:

...
WHERE S.student_id = C.student_id

--
David Portas
SQL Server MVP
--

"David Portas" <RE****************************@acm.org> wrote in message
news:O-********************@giganews.com...

Try this:

SELECT S.student_id, S.student_lead_id, C.record_dt,
S.student_lastname, S.student_firstname,
COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
S.student_marketcode_id
FROM f_student_sample AS S,
student_changes_sample AS C

--
David Portas
SQL Server MVP
--

[David Portas]

CORRECTION: Add the WHERE clause:

...
WHERE S.student_id = C.student_id

--
David Portas
SQL Server MVP
--

"David Portas" <RE****************************@acm.org> wrote in message
news:O-********************@giganews.com...

Try this:

SELECT S.student_id, S.student_lead_id, C.record_dt,
S.student_lastname, S.student_firstname,
COALESCE(C.student_campus_id,S.student_campus_id) AS student_campus_id,
COALESCE(C.student_admrep_id,S.student_admrep_id) AS student_admrep_id,
S.student_marketcode_id
FROM f_student_sample AS S,
student_changes_sample AS C

--
David Portas
SQL Server MVP
--

[rcamarda]

David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

COALESCE(C.student_campus_id,S*.student_campus_id) AS
student_campus_id,
COALESCE(C.student_admrep_id,S*.student_admrep_id) AS
student_admrep_id,
COALESCE(C.student_market_id,s.student_market_id) as student_market_id,
COALESCE(c.changeN, s.Student_N) as Student_N
Now I just have to expand this into all the fields that I'm tracking.

Pretty cool, I don't think I would have thought of this before, but now
you've given me another tool in my arsenal.
Thanks

[rcamarda]

David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

COALESCE(C.student_campus_id,S*.student_campus_id) AS
student_campus_id,
COALESCE(C.student_admrep_id,S*.student_admrep_id) AS
student_admrep_id,
COALESCE(C.student_market_id,s.student_market_id) as student_market_id,
COALESCE(c.changeN, s.Student_N) as Student_N
Now I just have to expand this into all the fields that I'm tracking.

Pretty cool, I don't think I would have thought of this before, but now
you've given me another tool in my arsenal.
Thanks

[Erland Sommarskog]

rcamarda (rc******@cablespeed.com) writes:

David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

That's it!

As for where to read about coalesce, CASE etc, see my signature.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Erland Sommarskog]

rcamarda (rc******@cablespeed.com) writes:

David, this is pretty cool (although I'm not sure whats going on...Ill
have to read up on coalesce).
It seems that eh coalesce is returning the first non-null field that
it's given in the argument list.

That's it!

As for where to read about coalesce, CASE etc, see my signature.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[rcamarda]

Follow up:
This works like a champ! A generalize form:

SELECT
<< current student fields. >>
-- brings in all students records
FROM current_records
UNION
SELECT
-- bring in all the student changes
<< 'static' fields>>,
COALESCE(changed_records.<field>, current_records.<field>) AS <FIELD>
<<n fields>>
FROM changed_records
WHERE current_records.business_id=changed_records.busine ss_id

actual SQL I created: (I may need to look this up some day *grin*)

SELECT
"STUDENT_ID",
"STUDENT_APPLICATION_DT",
"STUDENT_ETHNIC_ID",
"STUDENT_VISA_TYPE",
"STUDENT_GENDER",
"STUDENT_MARITAL",
"STUDENT_BIRTH_DT",
"STUDENT_BIRTH_PLACE",
"STUDENT_LEAD_ID",
"STUDENT_INPUT_DT",
"STUDENT_FINAID_REQ",
"STUDENT_VA_STATUS",
"STUDENT_VA_DT",
"STUDENT_EMAIL",
"STUDENT_FAX",
"STUDENT_COUNTRY_ID",
"STUDENT_COUNTRY_CAPTION",
"RECORD_DT",
"STUDENT_LASTNAME",
"STUDENT_FIRSTNAME",
"STUDENT_MI",
"STUDENT_ADDRESS1",
"STUDENT_ADDRESS2",
"STUDENT_CITY",
"STUDENT_STATE",
"STUDENT_ZIP",
"STUDENT_HOME_PHONE",
"STUDENT_WORK_PHONE",
"STUDENT_HS_NAME",
"STUDENT_EXTERNAL_ID",
"STUDENT_HS_GRAD_DT",
"STUDENT_FINANCIAL_AID",
"STUDENT_COMPANY_ID",
"STUDENT_LPROGRAM_ID",
"STUDENT_OTHER_COMPANY_CAPTION",
"STUDENT_CAMPUS_ID",
"STUDENT_ADVISOR_ID",
"STUDENT_PIN_ID",
"STUDENT_WORK_EXTENSION",
"STUDENT_EXPECTED_START_DT",
"STUDENT_SPONSOR_ID",
"STUDENT_LOAD_DT",
"STUDENT_DO_NOT_CALL",
"STUDENT_DO_NOT_MAIL",
"STUDENT_DO_NOT_EMAIL",
"STUDENT_VISA_EXCPT_SESSION_ID",
"STUDENT_CREATE_DT",
"STUDENT_CREATE_TIME",
"STUDENT_TALISMA_ID",
"STUDENT_TALISMA_STATUS",
"STUDENT_TALISMA_SUBSTATUS",
"STUDENT_PEP_DT",
"STUDENT_VOC_REHAB",
"STUDENT_ADMREP_ID",
"STUDENT_MARKETCODE_ID"
FROM "dbo"."STUDENT"
UNION
SELECT
STUDENT."STUDENT_ID",
STUDENT."STUDENT_APPLICATION_DT",
STUDENT."STUDENT_ETHNIC_ID",
STUDENT."STUDENT_VISA_TYPE",
STUDENT."STUDENT_GENDER",
STUDENT."STUDENT_MARITAL",
STUDENT."STUDENT_BIRTH_DT",
STUDENT."STUDENT_BIRTH_PLACE",
STUDENT."STUDENT_LEAD_ID",
STUDENT."STUDENT_INPUT_DT",
STUDENT."STUDENT_FINAID_REQ",
STUDENT."STUDENT_VA_STATUS",
STUDENT."STUDENT_VA_DT",
STUDENT."STUDENT_EMAIL",
STUDENT."STUDENT_FAX",
STUDENT."STUDENT_COUNTRY_ID",
STUDENT."STUDENT_COUNTRY_CAPTION",
STUDENT_CHANGES."RECORD_DT",
STUDENT."STUDENT_LASTNAME",
STUDENT."STUDENT_FIRSTNAME",
STUDENT."STUDENT_MI",
STUDENT."STUDENT_ADDRESS1",
STUDENT."STUDENT_ADDRESS2",
STUDENT."STUDENT_CITY",
STUDENT."STUDENT_STATE",
STUDENT."STUDENT_ZIP",
STUDENT."STUDENT_HOME_PHONE",
STUDENT."STUDENT_WORK_PHONE",
STUDENT."STUDENT_HS_NAME",
STUDENT."STUDENT_EXTERNAL_ID",
STUDENT."STUDENT_HS_GRAD_DT",
STUDENT."STUDENT_FINANCIAL_AID",
STUDENT."STUDENT_COMPANY_ID",
STUDENT."STUDENT_LPROGRAM_ID",
STUDENT."STUDENT_OTHER_COMPANY_CAPTION",
COALESCE(student_changes.student_campus_id,student .student_campus_id)
AS STUDENT_CAMPUS_ID,
STUDENT."STUDENT_ADVISOR_ID",
STUDENT."STUDENT_PIN_ID",
STUDENT."STUDENT_WORK_EXTENSION",
STUDENT."STUDENT_EXPECTED_START_DT",
STUDENT."STUDENT_SPONSOR_ID",
STUDENT."STUDENT_LOAD_DT",
STUDENT."STUDENT_DO_NOT_CALL",
STUDENT."STUDENT_DO_NOT_MAIL",
STUDENT."STUDENT_DO_NOT_EMAIL",
STUDENT."STUDENT_VISA_EXCPT_SESSION_ID",
STUDENT."STUDENT_CREATE_DT",
STUDENT."STUDENT_CREATE_TIME",
STUDENT."STUDENT_TALISMA_ID",
STUDENT."STUDENT_TALISMA_STATUS",
STUDENT."STUDENT_TALISMA_SUBSTATUS",
STUDENT."STUDENT_PEP_DT",
STUDENT."STUDENT_VOC_REHAB",

COALESCE(student_changes.student_ADMREP_id,student .student_ADMREP_id)
AS STUDENT_ADMREP_ID,
STUDENT."STUDENT_MARKETCODE_ID"
FROM
"dbo"."STUDENT",
"dbo"."STUDENT_CHANGES"
WHERE
STUDENT.STUDENT_ID = STUDENT_CHANGES.STUDENT_ID

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions

[rcamarda]

Follow up:
This works like a champ! A generalize form:

SELECT
<< current student fields. >>
-- brings in all students records
FROM current_records
UNION
SELECT
-- bring in all the student changes
<< 'static' fields>>,
COALESCE(changed_records.<field>, current_records.<field>) AS <FIELD>
<<n fields>>
FROM changed_records
WHERE current_records.business_id=changed_records.busine ss_id

actual SQL I created: (I may need to look this up some day *grin*)

SELECT
"STUDENT_ID",
"STUDENT_APPLICATION_DT",
"STUDENT_ETHNIC_ID",
"STUDENT_VISA_TYPE",
"STUDENT_GENDER",
"STUDENT_MARITAL",
"STUDENT_BIRTH_DT",
"STUDENT_BIRTH_PLACE",
"STUDENT_LEAD_ID",
"STUDENT_INPUT_DT",
"STUDENT_FINAID_REQ",
"STUDENT_VA_STATUS",
"STUDENT_VA_DT",
"STUDENT_EMAIL",
"STUDENT_FAX",
"STUDENT_COUNTRY_ID",
"STUDENT_COUNTRY_CAPTION",
"RECORD_DT",
"STUDENT_LASTNAME",
"STUDENT_FIRSTNAME",
"STUDENT_MI",
"STUDENT_ADDRESS1",
"STUDENT_ADDRESS2",
"STUDENT_CITY",
"STUDENT_STATE",
"STUDENT_ZIP",
"STUDENT_HOME_PHONE",
"STUDENT_WORK_PHONE",
"STUDENT_HS_NAME",
"STUDENT_EXTERNAL_ID",
"STUDENT_HS_GRAD_DT",
"STUDENT_FINANCIAL_AID",
"STUDENT_COMPANY_ID",
"STUDENT_LPROGRAM_ID",
"STUDENT_OTHER_COMPANY_CAPTION",
"STUDENT_CAMPUS_ID",
"STUDENT_ADVISOR_ID",
"STUDENT_PIN_ID",
"STUDENT_WORK_EXTENSION",
"STUDENT_EXPECTED_START_DT",
"STUDENT_SPONSOR_ID",
"STUDENT_LOAD_DT",
"STUDENT_DO_NOT_CALL",
"STUDENT_DO_NOT_MAIL",
"STUDENT_DO_NOT_EMAIL",
"STUDENT_VISA_EXCPT_SESSION_ID",
"STUDENT_CREATE_DT",
"STUDENT_CREATE_TIME",
"STUDENT_TALISMA_ID",
"STUDENT_TALISMA_STATUS",
"STUDENT_TALISMA_SUBSTATUS",
"STUDENT_PEP_DT",
"STUDENT_VOC_REHAB",
"STUDENT_ADMREP_ID",
"STUDENT_MARKETCODE_ID"
FROM "dbo"."STUDENT"
UNION
SELECT
STUDENT."STUDENT_ID",
STUDENT."STUDENT_APPLICATION_DT",
STUDENT."STUDENT_ETHNIC_ID",
STUDENT."STUDENT_VISA_TYPE",
STUDENT."STUDENT_GENDER",
STUDENT."STUDENT_MARITAL",
STUDENT."STUDENT_BIRTH_DT",
STUDENT."STUDENT_BIRTH_PLACE",
STUDENT."STUDENT_LEAD_ID",
STUDENT."STUDENT_INPUT_DT",
STUDENT."STUDENT_FINAID_REQ",
STUDENT."STUDENT_VA_STATUS",
STUDENT."STUDENT_VA_DT",
STUDENT."STUDENT_EMAIL",
STUDENT."STUDENT_FAX",
STUDENT."STUDENT_COUNTRY_ID",
STUDENT."STUDENT_COUNTRY_CAPTION",
STUDENT_CHANGES."RECORD_DT",
STUDENT."STUDENT_LASTNAME",
STUDENT."STUDENT_FIRSTNAME",
STUDENT."STUDENT_MI",
STUDENT."STUDENT_ADDRESS1",
STUDENT."STUDENT_ADDRESS2",
STUDENT."STUDENT_CITY",
STUDENT."STUDENT_STATE",
STUDENT."STUDENT_ZIP",
STUDENT."STUDENT_HOME_PHONE",
STUDENT."STUDENT_WORK_PHONE",
STUDENT."STUDENT_HS_NAME",
STUDENT."STUDENT_EXTERNAL_ID",
STUDENT."STUDENT_HS_GRAD_DT",
STUDENT."STUDENT_FINANCIAL_AID",
STUDENT."STUDENT_COMPANY_ID",
STUDENT."STUDENT_LPROGRAM_ID",
STUDENT."STUDENT_OTHER_COMPANY_CAPTION",
COALESCE(student_changes.student_campus_id,student .student_campus_id)
AS STUDENT_CAMPUS_ID,
STUDENT."STUDENT_ADVISOR_ID",
STUDENT."STUDENT_PIN_ID",
STUDENT."STUDENT_WORK_EXTENSION",
STUDENT."STUDENT_EXPECTED_START_DT",
STUDENT."STUDENT_SPONSOR_ID",
STUDENT."STUDENT_LOAD_DT",
STUDENT."STUDENT_DO_NOT_CALL",
STUDENT."STUDENT_DO_NOT_MAIL",
STUDENT."STUDENT_DO_NOT_EMAIL",
STUDENT."STUDENT_VISA_EXCPT_SESSION_ID",
STUDENT."STUDENT_CREATE_DT",
STUDENT."STUDENT_CREATE_TIME",
STUDENT."STUDENT_TALISMA_ID",
STUDENT."STUDENT_TALISMA_STATUS",
STUDENT."STUDENT_TALISMA_SUBSTATUS",
STUDENT."STUDENT_PEP_DT",
STUDENT."STUDENT_VOC_REHAB",

COALESCE(student_changes.student_ADMREP_id,student .student_ADMREP_id)
AS STUDENT_ADMREP_ID,
STUDENT."STUDENT_MARKETCODE_ID"
FROM
"dbo"."STUDENT",
"dbo"."STUDENT_CHANGES"
WHERE
STUDENT.STUDENT_ID = STUDENT_CHANGES.STUDENT_ID

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions