Microsoft SQL Server forum - Conceptual ideas - 2 tables one changes other complete Cursors?

Author

Conceptual ideas - 2 tables one changes other complete Cursors?

rcamarda

2005-06-13, 1:23 pm

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

2005-06-13, 1:23 pm

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

2005-06-13, 1:23 pm

"rcamarda" <rcamarda@cablespeed.com> wrote in message
news:1118684160.349709.100810@z14g2000cwz.googlegroups.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

2005-06-13, 8:23 pm

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_I
D" VARCHAR(10) NULL
)
;

insert into & #91;F_Student_Sample
] VALUES
('100','900','2005-05- 01','CAMARDA','ROBER
T','HOST*001','TLS*1
23','I20')

CREATE TABLE "dbo"." Student_Changes_Samp
le"
(
"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 & #91;Student_Changes_
Sample] VALUES
('100',7,'HOST*002',
'2001-01- 03','HOST*002',NULL)

INSERT INTO & #91;Student_Changes_
Sample] VALUES
('100',7,'HOST*003',
'2002-04- 03','HOST*003',NULL)

INSERT INTO & #91;Student_Changes_
Sample] VALUES
('100',7,'HOST*004',
'2003-02- 13','HOST*004',NULL)

INSERT INTO & #91;Student_Changes_
Sample] VALUES
('100',7,'DMI10','20
03-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

2005-06-13, 8:23 pm

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

2005-06-13, 8:23 pm

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_i
d
FROM f_student_sample AS S,
student_changes_samp
le AS C

--
David Portas
SQL Server MVP
--

David Portas

2005-06-13, 8:23 pm

CORRECTION: Add the WHERE clause:

...
WHERE S.student_id = C.student_id

--
David Portas
SQL Server MVP
--

"David Portas" < REMOVE_BEFORE_REPLYI
NG_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_i
d
> FROM f_student_sample AS S,
> student_changes_samp
le AS C
>
> --
> David Portas
> SQL Server MVP
> --
>
>

rcamarda

2005-06-13, 8:23 pm

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=
AD.student_campus_id) AS
student_campus_id,
COALESCE(C. student_admrep_id,S=
AD.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

2005-06-13, 8:23 pm

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

2005-06-13, 8:23 pm

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_rec
ords.<field>, current_records.<field> ) AS <FIELD>
<<n fields>>
FROM changed_records
WHERE current_records. business_id=changed_
records.business_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_CAPT
ION",
"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_AI
D",
"STUDENT_COMPANY_ID",
"STUDENT_LPROGRAM_ID",
" STUDENT_OTHER_COMPAN
Y_CAPTION",
"STUDENT_CAMPUS_ID",
"STUDENT_ADVISOR_ID",
"STUDENT_PIN_ID",
" STUDENT_WORK_EXTENSI
ON",
" STUDENT_EXPECTED_STA
RT_DT",
"STUDENT_SPONSOR_ID",
"STUDENT_LOAD_DT",
"STUDENT_DO_NOT_CALL",
"STUDENT_DO_NOT_MAIL",
" STUDENT_DO_NOT_EMAIL
",
" STUDENT_VISA_EXCPT_S
ESSION_ID",
"STUDENT_CREATE_DT",
"STUDENT_CREATE_TIME",
"STUDENT_TALISMA_ID",
" STUDENT_TALISMA_STAT
US",
" STUDENT_TALISMA_SUBS
TATUS",
"STUDENT_PEP_DT",
"STUDENT_VOC_REHAB",
"STUDENT_ADMREP_ID",
" STUDENT_MARKETCODE_I
D"
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_CAPT
ION",
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_AI
D",
STUDENT."STUDENT_COMPANY_ID",
STUDENT."STUDENT_LPROGRAM_ID",
STUDENT." STUDENT_OTHER_COMPAN
Y_CAPTION",
COALESCE(student_cha
nges. student_campus_id,st
udent.student_campus_id)
AS STUDENT_CAMPUS_ID,
STUDENT."STUDENT_ADVISOR_ID",
STUDENT."STUDENT_PIN_ID",
STUDENT." STUDENT_WORK_EXTENSI
ON",
STUDENT." STUDENT_EXPECTED_STA
RT_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_S
ESSION_ID",
STUDENT."STUDENT_CREATE_DT",
STUDENT."STUDENT_CREATE_TIME",
STUDENT."STUDENT_TALISMA_ID",
STUDENT." STUDENT_TALISMA_STAT
US",
STUDENT." STUDENT_TALISMA_SUBS
TATUS",
STUDENT."STUDENT_PEP_DT",
STUDENT."STUDENT_VOC_REHAB",

COALESCE(student_cha
nges. student_ADMREP_id,st
udent.student_ADMREP_id)
AS STUDENT_ADMREP_ID,
STUDENT." STUDENT_MARKETCODE_I
D"
FROM
"dbo"."STUDENT",
"dbo"."STUDENT_CHANGES"
WHERE
STUDENT.STUDENT_ID = STUDENT_CHANGES.STUDENT_ID

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions

Sponsored Links