Hello - I'm looking for a little advanced sql advice on a project i'm working on. Here's the lowdown:
I have two tables - we'll call them A and B. Table A has one row for every customer in the database and fifteen columns with a number in them specific to the customer. Table B has a hard coded set of decimal numbers that correspond to the columns of Table A. There is no common "join" between the two tables other than the column headers of Table A and a possible field in table B.
What I need to do is perform a rather lengthy math calculation using one field from table A multiplied to the variable in table B. It would be easy to do if I could join the tables together on something - but I'm not aware of a way to join tables from one field to another's column headers. I thought about pulling all the values into a temp table and doing the math from there, but the tables may change structure over time and according to other math formulas.
Is there a better way to get the math done than writing out, say, "select (tablea.field1 * tableb.field1)+(tablea.field2 * tableb.field2)+... on down the line?
Any advice or guidance is appreciated. Thanks!
RJ
Hi RJ,
Could you not create a local system table with the following columns?
id, tableA_field_name, tableB_field_name
Then you could create a view that joined something like this:
Select *
from
Table_A a
Join ltr_table_xref x on a.field_1 = x.tableA_field_name
join Table_B b on x.tableB_field_name = b.field1
You could create multiple views or CTEs joining on different entries in your local table depending on your needs. If the calculation is always the same between fields, you could potentially add a formiula column to your table as well: ( (tableA_field1 * tableB field1)/100) and use some dynamic sql to generate your calculated amounts.
In the example here, I added a row to tr_gooesoft_dropdown with a new code for testing purposes, the description field has my formula (cont_amt * 10).
declare @select_statement varchar(1000)
select @select_statement = 'select top 100 customer_no,cont_amt,' + description + ' as new_amt from t_contribution c' from tr_gooesoft_dropdown g where code = 1178
print @select_statement
exec (@select_statement)
You could do the same by substituting your join to the custom system table and using the formula field. Then if formulas ever needed changing, you could do it in the table (1 location) rather than in all referenced views and/or procedures.
Best,
lisa