Multiply value in N columns by N columns in a second table?

So I have been handed a database that I need to do some reporting out of to find cost per encounter. It basically looks like this:

ID     ItmQty1    ItmQty2    ItmQty3    etc...
1      3             1        0

I need to get a total cost by multiplying that by a prices table like so:

ItmCost1    ItmCost2    ItmCost3    etc...
21.50         10.75        3.53

and would like to end with a result like:

ID    ItmQty1    ItmTTL1    ItmQty2    ItmTTL2      ...    EncounterTTL
1        3        64.50         1        10.75                  75.25

Just not sure how a join would work with the many to one relationship between the tables.

This will be done on SQL server 2008 and SSRS.

2012-04-05 18:15
by Thildemar

Are there really a series of columns in the prices table called itmcost1 through itemcostn? And, if so, what is n - Sorpigal 2012-04-05 18:17

Pretty much, though they are actually ITEMNAMECost and not numbered =( There are about 30 items - Thildemar 2012-04-05 18:18

Another example of SQL gone wrong :( Is it possible to "get it from a better source"? Otherwise, it is imperative coding for you! (Wherever that is done - NoName 2012-04-05 18:19

I could remake the prices table pretty easy into an item and cost column with a row for each item, but I am not sure how that will help.. - Thildemar 2012-04-05 18:21

If there was a way to JOIN records, then it's as simple as (joinedColA + joinedColB) as total (then rollup the totals later which is still an awkward issue, but an easier and more common one). The above is a very bad way to have an SQL schema defined and should only be the output for humans (e.g. a rollup report at the end). The problem here is the multiplicity is across columns (no!) instead of rows (yes!) - NoName 2012-04-05 18:22

Well, your schema is somewhat unorthodox (to say the least), but it's doable:

SELECT
    i.ID
,   i.ItmQty1
,   i.ItmQty1 * c.ItmCost1 as ItmTTL1
,   i.ItmQty2
,   i.ItmQty2 * c.ItmCost2 as ItmTTL2
,   i.ItmQty3
,   i.ItmQty3 * c.ItmCost3 as ItmTTL3
-- and so on
FROM Items i
JOIN Costs c on 1=1

2012-04-05 18:26
by dasblinkenlight

Will this even work considering that the prices table only has one row? the ID will only match one row from the items table.. - Thildemar 2012-04-05 18:29

@Thildemar Oh, I did not notice that your prices have no IDs... Take a look at the fix, it should do the trick - dasblinkenlight 2012-04-05 18:33

That works. I just hope there is not more sql like this in some of this apps other tables = - Thildemar 2012-04-05 19:42