calculation problem

[jordan11]

I need some help into how to approach this problem I have 5
tables and based on the results in table A and B I need to do a query that will first take the name of the hobby in table A and match them with each customerid hobby in Table C, which I have done


The table above is the Hobbies table A,

Hobbyid Clientid hobbyname statename
1 1 swimming GA
2 1 jogging CO
3 1 dancing NY
4 1 swimming SC
5 2 swimming NY
6 3 swimming NY


table B is just a sample of the income table

B
hobbyid minsalary(10) expectedsal(10)

1 20000 100000
2 30000 400000
3 40000 500000
4 50000 600000
5 60000 200000
6 70000 700000



C
customerid State(10) cminsalary(5) cexpsalary(10) hobby
1 GA 20000 100000 swimming
2 NY 20000 30000 jogging
3 SC 30000 10000 dancing
4 CO 10000 30000 swimming





D
customerid stateSchools (10) StateBirth (20) PreferredState (10)
1 GA NY SC
2 NY NC GA
3 SC GA CO
4 CO CO GA


The tables already have the data and i would like to compare tables A and b with tables C and D so anything that is a state in table C and D is compared to the statename in table A and if the states match they will be given points I would then like the sum of the points , this category will be called geography. Table B minsalary and expectedsalary will be compared to table C CSalary and cexpsalary and put in a category called income a match will be given points. The points allocated are in brackets next to each field. Once I have a total points for each category I want to add them together and then add them to the table below.


TABLE E
customerid hobbyid finalscore


so if customerid 1 filled out a questionare
the hobby is swimming so the first thing will be to select all the hobbies that equal swimming from table A
once we have all the hobbies that = swimming ie( hobbyid 1,4,5,6) then the statename for ( hobbyid 1,4,5,6) will be compared to the values that have state in them in tables C AND D e.g stateSchools, StateBirth , PreferredState,etc and if the statename matches any of these feilds e.g table A Statename for hobbyid 1 GA is a match for customerid 1 stateschools GA so points will be allocated for a match and no points if no match. i then want to get the total points from all the questions and then put them in a table called total score (Table D) which will hold the custermerid, the hobbid and TOTAL



score so the results will look like this based on the mactches for each categorgy

TABLE D
customerid hobbyid score
1 1 40
1 4 10
1 5 10
1 6 10


the clientid and customerid are different.

thanks in advance

 

[Catadmin]

Consider doing an INSERT statement for TableD, utilizing a CASE statement in your SELECT statement. Obviously, you're going to have to play with joins to get the intended results.

This is a very generic code, you'll have to play with it, but try..

Insert into TableD (Column1, Column2, Column3...)
(Select Case State When GA Then Sum(points)
                   When SC Then Sum(points)
  from tableA a
  join tableB b 
....
  where ... )

Catadmin - MCDBA, MCSA
"If a person is Microsoft Certified, does that mean that Microsoft pays the bills for the funny white jackets that tie in the back???

 

[jordan11]

ok I will give it a try
thanks