Showing posts with label useful. Show all posts
Showing posts with label useful. Show all posts

Monday, March 12, 2012

how to use looping

i have a condition where i think looping would be most useful but am
not being able to write it..so i need a little help...it is for
creating a report and has no primary keys on an checks and no
constraints...that's why i haven't posted the DDL
My table looks like:
old_course professor category
acc*2007 john 2007*m1
acc*2007 john 2007*m3
acc*2008 john 2008*m2
acc*2008 john 2008*m2
acc*2006 john 2006*m3
mat*2007 sam 2007*m2
mat*2006 sam 2006*m1
soc*2006 kery 2006*m1
here professor john has taught accounting for 5 times in the past and
for three different categories m1,m2 and m3 where m3 is latest one
then m2 and then m2. so m3 must be given first priority, then m2 and
finally m1.
i need to assign the professor the latest course he has taught in the
past with the latest category for the coming year which is 2009. final
table would be
new_course professor old_course category
acc*2009 john acc*2007 2007*m3
mat*2009 sam mat*2007 2007*m2
soc*2006 kery soc*2006 2006*m1
here john is getting 2007*m2 because he has taught in category m3 and
2007 is latest for m3. similarly for mat 2007*m2 is latest one because
he has not taught in category m3 and checks for m2 for which 2007 is
latest. and for kery, she has not taught for m3 and neither for m2 so
she gets for m1 for which latest year is 2006.
i know it looks little complex but for the experts like u guys it
should be pretty simple,,i believe...
thanks...Smells like a Home work assignment in a Comp Sci. course to me.
;-)
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:96ff203a-4d6b-4749-a009-c28bbb04ba65@.v4g2000hsf.googlegroups.com...
>i have a condition where i think looping would be most useful but am
> not being able to write it..so i need a little help...it is for
> creating a report and has no primary keys on an checks and no
> constraints...that's why i haven't posted the DDL
> My table looks like:
> old_course professor category
> acc*2007 john 2007*m1
> acc*2007 john 2007*m3
> acc*2008 john 2008*m2
> acc*2008 john 2008*m2
> acc*2006 john 2006*m3
> mat*2007 sam 2007*m2
> mat*2006 sam 2006*m1
> soc*2006 kery 2006*m1
> here professor john has taught accounting for 5 times in the past and
> for three different categories m1,m2 and m3 where m3 is latest one
> then m2 and then m2. so m3 must be given first priority, then m2 and
> finally m1.
> i need to assign the professor the latest course he has taught in the
> past with the latest category for the coming year which is 2009. final
> table would be
> new_course professor old_course category
> acc*2009 john acc*2007 2007*m3
> mat*2009 sam mat*2007 2007*m2
> soc*2006 kery soc*2006 2006*m1
> here john is getting 2007*m2 because he has taught in category m3 and
> 2007 is latest for m3. similarly for mat 2007*m2 is latest one because
> he has not taught in category m3 and checks for m2 for which 2007 is
> latest. and for kery, she has not taught for m3 and neither for m2 so
> she gets for m1 for which latest year is 2006.
> i know it looks little complex but for the experts like u guys it
> should be pretty simple,,i believe...
> thanks...|||Please don't cross post in multiple news groups. Thanks.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:96ff203a-4d6b-4749-a009-c28bbb04ba65@.v4g2000hsf.googlegroups.com...
>i have a condition where i think looping would be most useful but am
> not being able to write it..so i need a little help...it is for
> creating a report and has no primary keys on an checks and no
> constraints...that's why i haven't posted the DDL
> My table looks like:
> old_course professor category
> acc*2007 john 2007*m1
> acc*2007 john 2007*m3
> acc*2008 john 2008*m2
> acc*2008 john 2008*m2
> acc*2006 john 2006*m3
> mat*2007 sam 2007*m2
> mat*2006 sam 2006*m1
> soc*2006 kery 2006*m1
> here professor john has taught accounting for 5 times in the past and
> for three different categories m1,m2 and m3 where m3 is latest one
> then m2 and then m2. so m3 must be given first priority, then m2 and
> finally m1.
> i need to assign the professor the latest course he has taught in the
> past with the latest category for the coming year which is 2009. final
> table would be
> new_course professor old_course category
> acc*2009 john acc*2007 2007*m3
> mat*2009 sam mat*2007 2007*m2
> soc*2006 kery soc*2006 2006*m1
> here john is getting 2007*m2 because he has taught in category m3 and
> 2007 is latest for m3. similarly for mat 2007*m2 is latest one because
> he has not taught in category m3 and checks for m2 for which 2007 is
> latest. and for kery, she has not taught for m3 and neither for m2 so
> she gets for m1 for which latest year is 2006.
> i know it looks little complex but for the experts like u guys it
> should be pretty simple,,i believe...
> thanks...|||On Dec 19, 1:48 am, sql_learner <mail...@.gmail.com> wrote:
> i have a condition where i think looping would be most useful but am
> not being able to write it..so i need a little help...it is for
> creating a report and has no primary keys on an checks and no
> constraints...that's why i haven't posted the DDL
> My table looks like:
> old_course professor category
> acc*2007 john 2007*m1
> acc*2007 john 2007*m3
> acc*2008 john 2008*m2
> acc*2008 john 2008*m2
> acc*2006 john 2006*m3
> mat*2007 sam 2007*m2
> mat*2006 sam 2006*m1
> soc*2006 kery 2006*m1
> here professor john has taught accounting for 5 times in the past and
> for three different categories m1,m2 and m3 where m3 is latest one
> then m2 and then m2. so m3 must be given first priority, then m2 and
> finally m1.
> i need to assign the professor the latest course he has taught in the
> past with the latest category for the coming year which is 2009. final
> table would be
> new_course professor old_course category
> acc*2009 john acc*2007 2007*m3
> mat*2009 sam mat*2007 2007*m2
> soc*2006 kery soc*2006 2006*m1
> here john is getting 2007*m2 because he has taught in category m3 and
> 2007 is latest for m3. similarly for mat 2007*m2 is latest one because
> he has not taught in category m3 and checks for m2 for which 2007 is
> latest. and for kery, she has not taught for m3 and neither for m2 so
> she gets for m1 for which latest year is 2006.
> i know it looks little complex but for the experts like u guys it
> should be pretty simple,,i believe...
> thanks...
select distinct new_course=substring(mt.old_course,
1,4)+'2009',mt.professor, mt.old_course, mt.category
from my_table mt,
(select distinct a.professor, a.old_course, category=(select top 1
b.category from my_table b where a.professor = b.professor
order by substring(category,6,2) desc)
from my_table a) mt2
where mt.professor = mt2.professor
and mt.category = mt2.category
HTH.|||I don't care what smells to u cause i don't think u can smell anything
good,,,that is ur problem...if u think u r really smart then why
couldn't u write answer then putting a comment on it....by the way
for ur kind information it is for multi-million project not an
assignment..|||Hi...if that hurt u but i was just wondering what's wrong with posting
on multiple groups...i was not getting any good response and i
thought it might be a better idea...sorry if i was wrong...|||For this multi-million dollar project may I recommend you hire a database
expert to design/develop/implement/maintain the backend? That will provide
the project a huge array of benefits, both short and long term.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:62b1d1ab-8d7c-4ced-813e-b5247aa96aa8@.r60g2000hsc.googlegroups.com...
>I don't care what smells to u cause i don't think u can smell anything
> good,,,that is ur problem...if u think u r really smart then why
> couldn't u write answer then putting a comment on it....by the way
> for ur kind information it is for multi-million project not an
> assignment..|||No real offense intended. I was just joking around.
Sorry you misunderstood my intentions.
Good luck on you project.
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:62b1d1ab-8d7c-4ced-813e-b5247aa96aa8@.r60g2000hsc.googlegroups.com...
>I don't care what smells to u cause i don't think u can smell anything
> good,,,that is ur problem...if u think u r really smart then why
> couldn't u write answer then putting a comment on it....by the way
> for ur kind information it is for multi-million project not an
> assignment..|||It is not a problem to post to multiple newsgroups just not independently.
You can add multiple newsgroups to the header and then all the answers
appear as one.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:3e8124fa-1364-4e5b-8385-edaa72ee1845@.d21g2000prf.googlegroups.com...
> Hi...if that hurt u but i was just wondering what's wrong with posting
> on multiple groups...i was not getting any good response and i
> thought it might be a better idea...sorry if i was wrong...

how to use looping

i have a condition where i think looping would be most useful but am
not being able to write it..so i need a little help...it is for
creating a report and has no primary keys on an checks and no
constraints...that's why i haven't posted the DDL
My table looks like:
old_course professor category
acc*2007 john 2007*m1
acc*2007 john 2007*m3
acc*2008 john 2008*m2
acc*2008 john 2008*m2
acc*2006 john 2006*m3
mat*2007 sam 2007*m2
mat*2006 sam 2006*m1
soc*2006 kery 2006*m1
here professor john has taught accounting for 5 times in the past and
for three different categories m1,m2 and m3 where m3 is latest one
then m2 and then m2. so m3 must be given first priority, then m2 and
finally m1.
i need to assign the professor the latest course he has taught in the
past with the latest category for the coming year which is 2009. final
table would be
new_course professor old_course category
acc*2009 john acc*2007 2007*m3
mat*2009 sam mat*2007 2007*m2
soc*2006 kery soc*2006 2006*m1
here john is getting 2007*m2 because he has taught in category m3 and
2007 is latest for m3. similarly for mat 2007*m2 is latest one because
he has not taught in category m3 and checks for m2 for which 2007 is
latest. and for kery, she has not taught for m3 and neither for m2 so
she gets for m1 for which latest year is 2006.
i know it looks little complex but for the experts like u guys it
should be pretty simple,,i believe...
thanks...
Smells like a Home work assignment in a Comp Sci. course to me.
;-)
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:96ff203a-4d6b-4749-a009-c28bbb04ba65@.v4g2000hsf.googlegroups.com...
>i have a condition where i think looping would be most useful but am
> not being able to write it..so i need a little help...it is for
> creating a report and has no primary keys on an checks and no
> constraints...that's why i haven't posted the DDL
> My table looks like:
> old_course professor category
> acc*2007 john 2007*m1
> acc*2007 john 2007*m3
> acc*2008 john 2008*m2
> acc*2008 john 2008*m2
> acc*2006 john 2006*m3
> mat*2007 sam 2007*m2
> mat*2006 sam 2006*m1
> soc*2006 kery 2006*m1
> here professor john has taught accounting for 5 times in the past and
> for three different categories m1,m2 and m3 where m3 is latest one
> then m2 and then m2. so m3 must be given first priority, then m2 and
> finally m1.
> i need to assign the professor the latest course he has taught in the
> past with the latest category for the coming year which is 2009. final
> table would be
> new_course professor old_course category
> acc*2009 john acc*2007 2007*m3
> mat*2009 sam mat*2007 2007*m2
> soc*2006 kery soc*2006 2006*m1
> here john is getting 2007*m2 because he has taught in category m3 and
> 2007 is latest for m3. similarly for mat 2007*m2 is latest one because
> he has not taught in category m3 and checks for m2 for which 2007 is
> latest. and for kery, she has not taught for m3 and neither for m2 so
> she gets for m1 for which latest year is 2006.
> i know it looks little complex but for the experts like u guys it
> should be pretty simple,,i believe...
> thanks...
|||Please don't cross post in multiple news groups. Thanks.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:96ff203a-4d6b-4749-a009-c28bbb04ba65@.v4g2000hsf.googlegroups.com...
>i have a condition where i think looping would be most useful but am
> not being able to write it..so i need a little help...it is for
> creating a report and has no primary keys on an checks and no
> constraints...that's why i haven't posted the DDL
> My table looks like:
> old_course professor category
> acc*2007 john 2007*m1
> acc*2007 john 2007*m3
> acc*2008 john 2008*m2
> acc*2008 john 2008*m2
> acc*2006 john 2006*m3
> mat*2007 sam 2007*m2
> mat*2006 sam 2006*m1
> soc*2006 kery 2006*m1
> here professor john has taught accounting for 5 times in the past and
> for three different categories m1,m2 and m3 where m3 is latest one
> then m2 and then m2. so m3 must be given first priority, then m2 and
> finally m1.
> i need to assign the professor the latest course he has taught in the
> past with the latest category for the coming year which is 2009. final
> table would be
> new_course professor old_course category
> acc*2009 john acc*2007 2007*m3
> mat*2009 sam mat*2007 2007*m2
> soc*2006 kery soc*2006 2006*m1
> here john is getting 2007*m2 because he has taught in category m3 and
> 2007 is latest for m3. similarly for mat 2007*m2 is latest one because
> he has not taught in category m3 and checks for m2 for which 2007 is
> latest. and for kery, she has not taught for m3 and neither for m2 so
> she gets for m1 for which latest year is 2006.
> i know it looks little complex but for the experts like u guys it
> should be pretty simple,,i believe...
> thanks...
|||On Dec 19, 1:48 am, sql_learner <mail...@.gmail.com> wrote:
> i have a condition where i think looping would be most useful but am
> not being able to write it..so i need a little help...it is for
> creating a report and has no primary keys on an checks and no
> constraints...that's why i haven't posted the DDL
> My table looks like:
> old_course professor category
> acc*2007 john 2007*m1
> acc*2007 john 2007*m3
> acc*2008 john 2008*m2
> acc*2008 john 2008*m2
> acc*2006 john 2006*m3
> mat*2007 sam 2007*m2
> mat*2006 sam 2006*m1
> soc*2006 kery 2006*m1
> here professor john has taught accounting for 5 times in the past and
> for three different categories m1,m2 and m3 where m3 is latest one
> then m2 and then m2. so m3 must be given first priority, then m2 and
> finally m1.
> i need to assign the professor the latest course he has taught in the
> past with the latest category for the coming year which is 2009. final
> table would be
> new_course professor old_course category
> acc*2009 john acc*2007 2007*m3
> mat*2009 sam mat*2007 2007*m2
> soc*2006 kery soc*2006 2006*m1
> here john is getting 2007*m2 because he has taught in category m3 and
> 2007 is latest for m3. similarly for mat 2007*m2 is latest one because
> he has not taught in category m3 and checks for m2 for which 2007 is
> latest. and for kery, she has not taught for m3 and neither for m2 so
> she gets for m1 for which latest year is 2006.
> i know it looks little complex but for the experts like u guys it
> should be pretty simple,,i believe...
> thanks...
select distinct new_course=substring(mt.old_course,
1,4)+'2009',mt.professor, mt.old_course, mt.category
from my_table mt,
(select distinct a.professor, a.old_course, category=(select top 1
b.category from my_table b where a.professor = b.professor
order by substring(category,6,2) desc)
from my_table a) mt2
where mt.professor = mt2.professor
and mt.category = mt2.category
HTH.
|||I don't care what smells to u cause i don't think u can smell anything
good,,,that is ur problem...if u think u r really smart then why
couldn't u write answer then putting a comment on it....by the way
for ur kind information it is for multi-million project not an
assignment..
|||Hi...if that hurt u but i was just wondering what's wrong with posting
on multiple groups...i was not getting any good response and i
thought it might be a better idea...sorry if i was wrong...
|||For this multi-million dollar project may I recommend you hire a database
expert to design/develop/implement/maintain the backend? That will provide
the project a huge array of benefits, both short and long term.
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:62b1d1ab-8d7c-4ced-813e-b5247aa96aa8@.r60g2000hsc.googlegroups.com...
>I don't care what smells to u cause i don't think u can smell anything
> good,,,that is ur problem...if u think u r really smart then why
> couldn't u write answer then putting a comment on it....by the way
> for ur kind information it is for multi-million project not an
> assignment..
|||No real offense intended. I was just joking around.
Sorry you misunderstood my intentions.
Good luck on you project.
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:62b1d1ab-8d7c-4ced-813e-b5247aa96aa8@.r60g2000hsc.googlegroups.com...
>I don't care what smells to u cause i don't think u can smell anything
> good,,,that is ur problem...if u think u r really smart then why
> couldn't u write answer then putting a comment on it....by the way
> for ur kind information it is for multi-million project not an
> assignment..
|||It is not a problem to post to multiple newsgroups just not independently.
You can add multiple newsgroups to the header and then all the answers
appear as one.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:3e8124fa-1364-4e5b-8385-edaa72ee1845@.d21g2000prf.googlegroups.com...
> Hi...if that hurt u but i was just wondering what's wrong with posting
> on multiple groups...i was not getting any good response and i
> thought it might be a better idea...sorry if i was wrong...

how to use looping

i have a condition where i think looping would be most useful but am
not being able to write it..so i need a little help...it is for
creating a report and has no primary keys on an checks and no
constraints...that's why i haven't posted the DDL
My table looks like:
old_course professor category
acc*2007 john 2007*m1
acc*2007 john 2007*m3
acc*2008 john 2008*m2
acc*2008 john 2008*m2
acc*2006 john 2006*m3
mat*2007 sam 2007*m2
mat*2006 sam 2006*m1
soc*2006 kery 2006*m1
here professor john has taught accounting for 5 times in the past and
for three different categories m1,m2 and m3 where m3 is latest one
then m2 and then m2. so m3 must be given first priority, then m2 and
finally m1.
i need to assign the professor the latest course he has taught in the
past with the latest category for the coming year which is 2009. final
table would be
new_course professor old_course category
acc*2009 john acc*2007 2007*m3
mat*2009 sam mat*2007 2007*m2
soc*2006 kery soc*2006 2006*m1
here john is getting 2007*m2 because he has taught in category m3 and
2007 is latest for m3. similarly for mat 2007*m2 is latest one because
he has not taught in category m3 and checks for m2 for which 2007 is
latest. and for kery, she has not taught for m3 and neither for m2 so
she gets for m1 for which latest year is 2006.
i know it looks little complex but for the experts like u guys it
should be pretty simple,,i believe...
thanks...Please don't cross post in multiple news groups. Thanks.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:96ff203a-4d6b-4749-a009-c28bbb04ba65@.v4g2000hsf.googlegroups.com...
>i have a condition where i think looping would be most useful but am
> not being able to write it..so i need a little help...it is for
> creating a report and has no primary keys on an checks and no
> constraints...that's why i haven't posted the DDL
> My table looks like:
> old_course professor category
> acc*2007 john 2007*m1
> acc*2007 john 2007*m3
> acc*2008 john 2008*m2
> acc*2008 john 2008*m2
> acc*2006 john 2006*m3
> mat*2007 sam 2007*m2
> mat*2006 sam 2006*m1
> soc*2006 kery 2006*m1
> here professor john has taught accounting for 5 times in the past and
> for three different categories m1,m2 and m3 where m3 is latest one
> then m2 and then m2. so m3 must be given first priority, then m2 and
> finally m1.
> i need to assign the professor the latest course he has taught in the
> past with the latest category for the coming year which is 2009. final
> table would be
> new_course professor old_course category
> acc*2009 john acc*2007 2007*m3
> mat*2009 sam mat*2007 2007*m2
> soc*2006 kery soc*2006 2006*m1
> here john is getting 2007*m2 because he has taught in category m3 and
> 2007 is latest for m3. similarly for mat 2007*m2 is latest one because
> he has not taught in category m3 and checks for m2 for which 2007 is
> latest. and for kery, she has not taught for m3 and neither for m2 so
> she gets for m1 for which latest year is 2006.
> i know it looks little complex but for the experts like u guys it
> should be pretty simple,,i believe...
> thanks...|||Smells like a Home work assignment in a Comp Sci. course to me.
;-)
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:96ff203a-4d6b-4749-a009-c28bbb04ba65@.v4g2000hsf.googlegroups.com...
>i have a condition where i think looping would be most useful but am
> not being able to write it..so i need a little help...it is for
> creating a report and has no primary keys on an checks and no
> constraints...that's why i haven't posted the DDL
> My table looks like:
> old_course professor category
> acc*2007 john 2007*m1
> acc*2007 john 2007*m3
> acc*2008 john 2008*m2
> acc*2008 john 2008*m2
> acc*2006 john 2006*m3
> mat*2007 sam 2007*m2
> mat*2006 sam 2006*m1
> soc*2006 kery 2006*m1
> here professor john has taught accounting for 5 times in the past and
> for three different categories m1,m2 and m3 where m3 is latest one
> then m2 and then m2. so m3 must be given first priority, then m2 and
> finally m1.
> i need to assign the professor the latest course he has taught in the
> past with the latest category for the coming year which is 2009. final
> table would be
> new_course professor old_course category
> acc*2009 john acc*2007 2007*m3
> mat*2009 sam mat*2007 2007*m2
> soc*2006 kery soc*2006 2006*m1
> here john is getting 2007*m2 because he has taught in category m3 and
> 2007 is latest for m3. similarly for mat 2007*m2 is latest one because
> he has not taught in category m3 and checks for m2 for which 2007 is
> latest. and for kery, she has not taught for m3 and neither for m2 so
> she gets for m1 for which latest year is 2006.
> i know it looks little complex but for the experts like u guys it
> should be pretty simple,,i believe...
> thanks...|||On Dec 19, 1:48 am, sql_learner <mail...@.gmail.com> wrote:
> i have a condition where i think looping would be most useful but am
> not being able to write it..so i need a little help...it is for
> creating a report and has no primary keys on an checks and no
> constraints...that's why i haven't posted the DDL
> My table looks like:
> old_course professor category
> acc*2007 john 2007*m1
> acc*2007 john 2007*m3
> acc*2008 john 2008*m2
> acc*2008 john 2008*m2
> acc*2006 john 2006*m3
> mat*2007 sam 2007*m2
> mat*2006 sam 2006*m1
> soc*2006 kery 2006*m1
> here professor john has taught accounting for 5 times in the past and
> for three different categories m1,m2 and m3 where m3 is latest one
> then m2 and then m2. so m3 must be given first priority, then m2 and
> finally m1.
> i need to assign the professor the latest course he has taught in the
> past with the latest category for the coming year which is 2009. final
> table would be
> new_course professor old_course category
> acc*2009 john acc*2007 2007*m3
> mat*2009 sam mat*2007 2007*m2
> soc*2006 kery soc*2006 2006*m1
> here john is getting 2007*m2 because he has taught in category m3 and
> 2007 is latest for m3. similarly for mat 2007*m2 is latest one because
> he has not taught in category m3 and checks for m2 for which 2007 is
> latest. and for kery, she has not taught for m3 and neither for m2 so
> she gets for m1 for which latest year is 2006.
> i know it looks little complex but for the experts like u guys it
> should be pretty simple,,i believe...
> thanks...
select distinct new_course=substring(mt.old_course,
1,4)+'2009',mt.professor, mt.old_course, mt.category
from my_table mt,
(select distinct a.professor, a.old_course, category=(select top 1
b.category from my_table b where a.professor = b.professor
order by substring(category,6,2) desc)
from my_table a) mt2
where mt.professor = mt2.professor
and mt.category = mt2.category
HTH.|||I don't care what smells to u cause i don't think u can smell anything
good,,,that is ur problem...if u think u r really smart then why
couldn't u write answer then putting a comment on it....by the way
for ur kind information it is for multi-million project not an
assignment..|||Hi...if that hurt u but i was just wondering what's wrong with posting
on multiple groups...i was not getting any good response and i
thought it might be a better idea...sorry if i was wrong...|||For this multi-million dollar project may I recommend you hire a database
expert to design/develop/implement/maintain the backend? That will provide
the project a huge array of benefits, both short and long term.
--
Kevin G. Boles
TheSQLGuru
Indicium Resources, Inc.
kgboles a earthlink dt net
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:62b1d1ab-8d7c-4ced-813e-b5247aa96aa8@.r60g2000hsc.googlegroups.com...
>I don't care what smells to u cause i don't think u can smell anything
> good,,,that is ur problem...if u think u r really smart then why
> couldn't u write answer then putting a comment on it....by the way
> for ur kind information it is for multi-million project not an
> assignment..|||No real offense intended. I was just joking around.
Sorry you misunderstood my intentions.
Good luck on you project.
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:62b1d1ab-8d7c-4ced-813e-b5247aa96aa8@.r60g2000hsc.googlegroups.com...
>I don't care what smells to u cause i don't think u can smell anything
> good,,,that is ur problem...if u think u r really smart then why
> couldn't u write answer then putting a comment on it....by the way
> for ur kind information it is for multi-million project not an
> assignment..|||It is not a problem to post to multiple newsgroups just not independently.
You can add multiple newsgroups to the header and then all the answers
appear as one.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"sql_learner" <mailfrd@.gmail.com> wrote in message
news:3e8124fa-1364-4e5b-8385-edaa72ee1845@.d21g2000prf.googlegroups.com...
> Hi...if that hurt u but i was just wondering what's wrong with posting
> on multiple groups...i was not getting any good response and i
> thought it might be a better idea...sorry if i was wrong...

Friday, March 9, 2012

How to use information from a dimension in a calculated member

It would be really useful to be able to extract information from an Analysis Services 2005 dimension such as:-

"The number of seconds in the current time dimension member".

This will enable calculated members such as the following to compute a process percent operation time.

Member = [Measures].[Sum of seconds the process was operating] / "No of seconds in the current time dimension member" (eg.Day, Week, Month etc.)

How can I get this information from the dimension for use in a calculated member?

Many thanks

There could be various approaches to this - here's an example for Adventure Works, which computes the number of seconds for the selected Date dimension members, based on counting the number of days (leaf level) which "exist" with the currently selected Date members:

>>

With Member [Measures].[Seconds] as

Count(Existing [Date].[Date].[Date].Members) * 24 * 60 * 60,

FORMAT_STRING = '#,#'

select {[Measures].[Seconds]} on 0

from [Adventure Works]

where {[Date].[Calendar].[Date].&[1],

[Date].[Calendar].[Date].&[46]}

-

Seconds
172,800

>>

But this approach may not work as desired with nested from sub-cube queries:

>>

With Member [Measures].[Seconds] as

Count(Existing [Date].[Date].[Date].Members) * 24 * 60 * 60,

FORMAT_STRING = '#,#'

select {[Measures].[Seconds]} on 0

from

(select {[Date].[Calendar].[Date].&[1],

[Date].[Calendar].[Date].&[46]} on 0

from [Adventure Works])

Seconds
100,051,200

>>

A more tedious approach, which will work in both scenarios above, is to create a separate measure group on the Date dimension table, with a Named Calculation like: Seconds = 24 * 60 * 60. Then a "Seconds" sum measure can be created for this Named Calculation, which returns the seconds spanned by the selected Date members.

|||

Thanks for the suggestions Deepak however what I am trying to do does not directly involve the time dimension.

I have another dimension called Shift which contains information about the process operator shifts. The shift dimension information comes from a SQL table which has ShiftID, Shift Start Date Time, and Shift End Date Time fields. The main process data table links to the shift table using ShiftID.

Using the shift dimension I can now extract from the cube the process run time per operator shift - all works very well so far.

What I now want to do for each of the shifts, is to set up a calculated measure to compute the shift length in seconds based on the shift start and shift end fields in the shift dimension. I can therefore compute the percent run time per shift using this information together with the process run time.

I am very new to MDX so any help would be appreciated.

Thanks in advance.

|||

Add a Named Calculation to the Shift dimension table, and make this an Attribute (its hierarchy doesn't need to be enabled) - the attribute value can be set equal to the key (ie. integer):

ShiftLength: datediff(second, "Shift Start Date Time", "Shift End Date Time")

Then, [Measures].[ShiftLength]: [Shift].[Shift].Properties("ShiftLength", TYPED)

|||

Hi Deepak,

I have tried out your suggestion:-

- Set up the calculated member ShiftLength which works fine
- Added ShiftLength as an attribute to the Shift Dimenson (Property of the key ShiftID)

I can now browse the shift dimension and see the ShiftLength member property which is fine.

I have created a calculated member:-

CREATE MEMBER CURRENTCUBE.[MEASURES].[ShiftLength]

AS [Shift].[Shift ID].Properties( "Shift Length", TYPED),

VISIBLE = 1 ;

However for some reason no values are returned from the cube for this calculated member. What am I doing wrong?

Thanks for your continued help.

|||

A couple of things to consider:

Is "Shift Length" the name of the attribute, or ShiftLength - the same spelling should be used everywhere?|||

Hi Deepak,

Thanks for the reply - I can now extract the shift length successfully, it was a ShiftID level problem!.

Would you have an answer to my earlier unanswered post "strToMember Execution Problem"?

Many thanks

Adrian

|||

Adrian West wrote:

Hi Deepak,

Thanks for the reply - I can now extract the shift length successfully, it was a ShiftID level problem!.

Would you have an answer to my earlier unanswered post "strToMember Execution Problem"?

Many thanks

Adrian

I'm struggling a bit here
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=524766&SiteID=1

I changed mine to this, no luck (doesn't show up on Browser)

CREATE MEMBER CURRENTCUBE.[MEASURES].[% Capacity Sold]

AS [Dim Performances].[Dim Performances].Properties( "Perf Capacity", TYPED),

VISIBLE = 1 ;

What exactly goes into the 2nd [ ] bracket? Does it have to be a hierarchy, or can it be an attribute? Does it have to be "Key"?

How to use information from a dimension in a calculated member

It would be really useful to be able to extract information from an Analysis Services 2005 dimension such as:-

"The number of seconds in the current time dimension member".

This will enable calculated members such as the following to compute a process percent operation time.

Member = [Measures].[Sum of seconds the process was operating] / "No of seconds in the current time dimension member" (eg.Day, Week, Month etc.)

How can I get this information from the dimension for use in a calculated member?

Many thanks

There could be various approaches to this - here's an example for Adventure Works, which computes the number of seconds for the selected Date dimension members, based on counting the number of days (leaf level) which "exist" with the currently selected Date members:

>>

With Member [Measures].[Seconds] as

Count(Existing [Date].[Date].[Date].Members) * 24 * 60 * 60,

FORMAT_STRING = '#,#'

select {[Measures].[Seconds]} on 0

from [Adventure Works]

where {[Date].[Calendar].[Date].&[1],

[Date].[Calendar].[Date].&[46]}

-

Seconds
172,800

>>

But this approach may not work as desired with nested from sub-cube queries:

>>

With Member [Measures].[Seconds] as

Count(Existing [Date].[Date].[Date].Members) * 24 * 60 * 60,

FORMAT_STRING = '#,#'

select {[Measures].[Seconds]} on 0

from

(select {[Date].[Calendar].[Date].&[1],

[Date].[Calendar].[Date].&[46]} on 0

from [Adventure Works])

Seconds
100,051,200

>>

A more tedious approach, which will work in both scenarios above, is to create a separate measure group on the Date dimension table, with a Named Calculation like: Seconds = 24 * 60 * 60. Then a "Seconds" sum measure can be created for this Named Calculation, which returns the seconds spanned by the selected Date members.

|||

Thanks for the suggestions Deepak however what I am trying to do does not directly involve the time dimension.

I have another dimension called Shift which contains information about the process operator shifts. The shift dimension information comes from a SQL table which has ShiftID, Shift Start Date Time, and Shift End Date Time fields. The main process data table links to the shift table using ShiftID.

Using the shift dimension I can now extract from the cube the process run time per operator shift - all works very well so far.

What I now want to do for each of the shifts, is to set up a calculated measure to compute the shift length in seconds based on the shift start and shift end fields in the shift dimension. I can therefore compute the percent run time per shift using this information together with the process run time.

I am very new to MDX so any help would be appreciated.

Thanks in advance.

|||

Add a Named Calculation to the Shift dimension table, and make this an Attribute (its hierarchy doesn't need to be enabled) - the attribute value can be set equal to the key (ie. integer):

ShiftLength: datediff(second, "Shift Start Date Time", "Shift End Date Time")

Then, [Measures].[ShiftLength]: [Shift].[Shift].Properties("ShiftLength", TYPED)

|||

Hi Deepak,

I have tried out your suggestion:-

- Set up the calculated member ShiftLength which works fine
- Added ShiftLength as an attribute to the Shift Dimenson (Property of the key ShiftID)

I can now browse the shift dimension and see the ShiftLength member property which is fine.

I have created a calculated member:-

CREATE MEMBER CURRENTCUBE.[MEASURES].[ShiftLength]

AS [Shift].[Shift ID].Properties( "Shift Length", TYPED),

VISIBLE = 1 ;

However for some reason no values are returned from the cube for this calculated member. What am I doing wrong?

Thanks for your continued help.

|||

A couple of things to consider:

Is "Shift Length" the name of the attribute, or ShiftLength - the same spelling should be used everywhere?|||

Hi Deepak,

Thanks for the reply - I can now extract the shift length successfully, it was a ShiftID level problem!.

Would you have an answer to my earlier unanswered post "strToMember Execution Problem"?

Many thanks

Adrian

|||

Adrian West wrote:

Hi Deepak,

Thanks for the reply - I can now extract the shift length successfully, it was a ShiftID level problem!.

Would you have an answer to my earlier unanswered post "strToMember Execution Problem"?

Many thanks

Adrian

I'm struggling a bit here
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=524766&SiteID=1

I changed mine to this, no luck (doesn't show up on Browser)

CREATE MEMBER CURRENTCUBE.[MEASURES].[% Capacity Sold]

AS [Dim Performances].[Dim Performances].Properties( "Perf Capacity", TYPED),

VISIBLE = 1 ;

What exactly goes into the 2nd [ ] bracket? Does it have to be a hierarchy, or can it be an attribute? Does it have to be "Key"?

How to use information from a dimension in a calculated member

It would be really useful to be able to extract information from an Analysis Services 2005 dimension such as:-

"The number of seconds in the current time dimension member".

This will enable calculated members such as the following to compute a process percent operation time.

Member = [Measures].[Sum of seconds the process was operating] / "No of seconds in the current time dimension member" (eg.Day, Week, Month etc.)

How can I get this information from the dimension for use in a calculated member?

Many thanks

There could be various approaches to this - here's an example for Adventure Works, which computes the number of seconds for the selected Date dimension members, based on counting the number of days (leaf level) which "exist" with the currently selected Date members:

>>

With Member [Measures].[Seconds] as

Count(Existing [Date].[Date].[Date].Members) * 24 * 60 * 60,

FORMAT_STRING = '#,#'

select {[Measures].[Seconds]} on 0

from [Adventure Works]

where {[Date].[Calendar].[Date].&[1],

[Date].[Calendar].[Date].&[46]}

-

Seconds
172,800

>>

But this approach may not work as desired with nested from sub-cube queries:

>>

With Member [Measures].[Seconds] as

Count(Existing [Date].[Date].[Date].Members) * 24 * 60 * 60,

FORMAT_STRING = '#,#'

select {[Measures].[Seconds]} on 0

from

(select {[Date].[Calendar].[Date].&[1],

[Date].[Calendar].[Date].&[46]} on 0

from [Adventure Works])

Seconds
100,051,200

>>

A more tedious approach, which will work in both scenarios above, is to create a separate measure group on the Date dimension table, with a Named Calculation like: Seconds = 24 * 60 * 60. Then a "Seconds" sum measure can be created for this Named Calculation, which returns the seconds spanned by the selected Date members.

|||

Thanks for the suggestions Deepak however what I am trying to do does not directly involve the time dimension.

I have another dimension called Shift which contains information about the process operator shifts. The shift dimension information comes from a SQL table which has ShiftID, Shift Start Date Time, and Shift End Date Time fields. The main process data table links to the shift table using ShiftID.

Using the shift dimension I can now extract from the cube the process run time per operator shift - all works very well so far.

What I now want to do for each of the shifts, is to set up a calculated measure to compute the shift length in seconds based on the shift start and shift end fields in the shift dimension. I can therefore compute the percent run time per shift using this information together with the process run time.

I am very new to MDX so any help would be appreciated.

Thanks in advance.

|||

Add a Named Calculation to the Shift dimension table, and make this an Attribute (its hierarchy doesn't need to be enabled) - the attribute value can be set equal to the key (ie. integer):

ShiftLength: datediff(second, "Shift Start Date Time", "Shift End Date Time")

Then, [Measures].[ShiftLength]: [Shift].[Shift].Properties("ShiftLength", TYPED)

|||

Hi Deepak,

I have tried out your suggestion:-

- Set up the calculated member ShiftLength which works fine
- Added ShiftLength as an attribute to the Shift Dimenson (Property of the key ShiftID)

I can now browse the shift dimension and see the ShiftLength member property which is fine.

I have created a calculated member:-

CREATE MEMBER CURRENTCUBE.[MEASURES].[ShiftLength]

AS [Shift].[Shift ID].Properties( "Shift Length", TYPED),

VISIBLE = 1 ;

However for some reason no values are returned from the cube for this calculated member. What am I doing wrong?

Thanks for your continued help.

|||

A couple of things to consider:

Is "Shift Length" the name of the attribute, or ShiftLength - the same spelling should be used everywhere?|||

Hi Deepak,

Thanks for the reply - I can now extract the shift length successfully, it was a ShiftID level problem!.

Would you have an answer to my earlier unanswered post "strToMember Execution Problem"?

Many thanks

Adrian

|||

Adrian West wrote:

Hi Deepak,

Thanks for the reply - I can now extract the shift length successfully, it was a ShiftID level problem!.

Would you have an answer to my earlier unanswered post "strToMember Execution Problem"?

Many thanks

Adrian

I'm struggling a bit here
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=524766&SiteID=1

I changed mine to this, no luck (doesn't show up on Browser)

CREATE MEMBER CURRENTCUBE.[MEASURES].[% Capacity Sold]

AS [Dim Performances].[Dim Performances].Properties( "Perf Capacity", TYPED),

VISIBLE = 1 ;

What exactly goes into the 2nd [ ] bracket? Does it have to be a hierarchy, or can it be an attribute? Does it have to be "Key"?