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