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
RobHow 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|||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|||"rcamarda" <rcamarda@.cablespeed.com> wrote in message
news:1118684160.349709.100810@.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" <rcamarda@.cablespeed.com> wrote in message
news:1118684160.349709.100810@.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.|||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|||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|||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:
1000139200NULL
1000139200003
1000139200006
1000139200016
1000139200HOST*006Iverson Iii
I need the last name (Iverson Iii) to be on all rows|||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:
1000139200NULL
1000139200003
1000139200006
1000139200016
1000139200HOST*006Iverson Iii
I need the last name (Iverson Iii) to be on all rows|||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
--|||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
--|||CORRECTION: Add the WHERE clause:
...
WHERE S.student_id = C.student_id
--
David Portas
SQL Server MVP
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O-2dnduoiZPNdjDfRVn-oA@.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
> --|||CORRECTION: Add the WHERE clause:
...
WHERE S.student_id = C.student_id
--
David Portas
SQL Server MVP
--
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:O-2dnduoiZPNdjDfRVn-oA@.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, 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|||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 (rcamarda@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||rcamarda (rcamarda@.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, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||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|||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
No comments:
Post a Comment