Concatenating fields: consider

Data:

PROJ PLAN TOTTIME UNIT
A P1 10 DAY
A P2 10 HOUR
A P3 1 MONTH

WHEN I'M DOING GROUP BY ON PROJ

AND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTH

I WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.

10 DAY=10 DAYS
10 HOUR=1.25 DAYS
1 MONTH=20 DAYS

THE RESULT SHOULD BE LIKE THIS:

PROJ PLAN TOTTIME (IN DAYS)
----------
A ALL 31.25
----------

THANKS IN ADV.

T.S.NEGI
tilak.negi@.mind-infotech.comtilak.negi@.mind-infotech.com (T.S.Negi) wrote in message news:<a1930058.0401052155.53e0e35a@.posting.google.com>...
> Data:
> PROJ PLAN TOTTIME UNIT
> A P1 10 DAY
> A P2 10 HOUR
> A P3 1 MONTH
>
> WHEN I'M DOING GROUP BY ON PROJ
> AND CALCULATING TOTTIME IT CONSIDER ONE OF THE UNIT I.E. DAY, HOUR, MONTH
> I WANT TO SUMUP ALL WITH HAVING UNIT CALCULATION ALSO.
>
> 10 DAY=10 DAYS
> 10 HOUR=1.25 DAYS
> 1 MONTH=20 DAYS
> THE RESULT SHOULD BE LIKE THIS:
> PROJ PLAN TOTTIME (IN DAYS)
> ----------
> A ALL 31.25
> ----------
>
>
> THANKS IN ADV.
> T.S.NEGI
> tilak.negi@.mind-infotech.com

Here is one way to do the summing using a CASE statement

SELECT
SUM(CASE UNIT
WHEN 'DAY' THEN TOTTIME
WHEN 'HOUR' THEN TOTTIME * 0.125
WHEN 'MONTH' THEN TOTTIME * 20
END)
FROM
PLANSAMPLE
WHERE
<CONDITION>
GROUP BY
<Grouping>

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
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:

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')

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:

--
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
--

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.

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:

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

ref: DecisionStream Fact build Cognos SCD slowly changing dimensions

Thursday, March 29, 2012

Configuration set up

Configuration set up

Configuration set up

Friday, February 24, 2012

condition with group by

Tuesday, February 14, 2012

Conceptual ideas - 2 tables one changes other complete Cursors?

Sunday, February 12, 2012

concatenation but with a format...

Concatenating fields

Blog Archive

About Me