Is it possible to do ? - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Is it possible to do ?

Author

Topic

assafsn
Starting Member

8 Posts

Posted - 2004-09-27 : 10:21:38

Hi all.

I have 2 tables:
test1:
Name Age Class
x 1 a
x 4 a
x 6 b
(this table means that x was sampled 3 times and in age 1 and 4 he was an 'a' class and in age six he was 'b' class).
test2:
Name Age
x 1
x 2
x 3
x 4
x 5
x 6
x 7
x 8
x 9
x 10
(this is a list of the x ages till now).

I wish to write a view in MS-SQL that will produce the following:

Name Age Class
x 1 a
x 2 a
x 3 a
x 4 a
x 5 a
x 6 b
x 7 b
x 8 b
x 9 b
x 10 b

How can I do it ???

Thanks for your help.

Stern Assaf

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-27 : 11:58:52

In your example, how do you know that Age 5 is a grade of "a"?

assafsn
Starting Member

8 Posts

Posted - 2004-09-27 : 12:13:10

well there is no grade here. Test1 and test2 are joined using the name and age. the class is the parameter I wish to extact

assafsn
Starting Member

8 Posts

Posted - 2004-09-27 : 12:13:31

well there is no grade here. Test1 and test2 are joined using the name and age. the class is the parameter I wish to extact

influent
Constraint Violating Yak Guru

367 Posts

Posted - 2004-09-27 : 13:00:31

I don't know where I got grade, I meant class. The point is that there needs to be some logic to determine how null classes (those ages where class was not determined) are assigned an "a" or a "b" or whatever for each particular age.

assafsn
Starting Member

8 Posts

Posted - 2004-09-27 : 13:22:45

well it pretty simple - null classes are assigned the value of the the closest age that is also smaller then the current Age.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-27 : 14:05:58

break it down into parts:

for each age, you need to get age of the sample that is <= to it:


select a.name, a.age, 
       (select max(age) 
        from test1 b 
        where a.name=b.name and b.age <= a.age) as PrevAge
from test2 a

once you have that, you just do a join:


select 
  a.name, a.age, b.class
from 
  (above SQL) a
inner join 
  test1 b on a.name = b.name and a.prevage = b.age

Always take these 1 step at a time, break it down into parts, test each one out to make sure it returns the results you want, and add one join at a time.

by way -- try not to post your problem in more than 1 thread, it can get annoying. and always avoid RIGHT joins.

- Jeff

assafsn
Starting Member

8 Posts

Posted - 2004-09-27 : 14:40:41

Well Jeff you are the man !!!

I admit I do not realy understand how it works but it does.

Thanks alot.

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-09-27 : 14:54:00

even with the step by step info? work your way through it, and you should be able to get it. it will be worth it in the long run, it is not difficult to understand, except at first.

but the key is: build 1 step at a time, think logically about how you will derive your answer, and then write a little bit of sql at a time, added layer upon layer and join by join, testing as you go. and always work it out with a little bit of sample data.

- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources