SQL Server Query with complex calculations for every row

Question

Hello,

This is the situation.
table a
cola1 cola2

table b
colb1 colb2 colb3 colb4 colb5

table c
colc1 colc2 colc3

for every value of cola2 = colb1 and colb4 = colc12 , fetch colb2
for every value of cola2 = colb1 and colb5 = colc3, fetch colb3

calculate (colb3- colb2) * size * factor1 for every cola2.
Calculate SUM((colb3- colb2) * size * factor1) for every cola2
Calculate AVG((colb3- colb2) * size * factor1) for every cola2

the value of (colb3- colb2) * size * factor1 needs to stored in a table for other calculations as well. I tried using multi statement table valued user defined function. But unable to return both aggregate values and single values in the same table. How would I use multiple UDFs in the same join query? Is there any other feature I can use?

Sample data : table a
id1 prod1

table b
id1 datefrom dateto id1 id2
id2 datefrom dateto id3 id4

table c
id1 date price
id2 date price
id3 date price
id4 date price
table b(id1) matches table c(id1)
table b(id2) matches table c(id2)
i need to fetch the price from table c for both the ids in table b so i can subtract the values and find the sum of the results Appreciate any help. Thanks.

Answer 1

Why can't you do it in simple SQL statements? I'm not seeing anything too difficult here

select cola2, 
CASE WHEN cola2 = colb1 AND colb4 = colc12 THEN colb2 ELSE NULL END as Calculation1, 
CASE WHEN cola2 = colb1 AND colb5 = colc3 THEN colb3 ELSE NULL END AS Calculation2,
 (colb3 - colb2) * size * factor1 as Calculation3, <...>
INTO #intermediate
FROM tablea 
INNER JOIN tableb ON <...>
INNER JOIN tablec ON <...>

SELECT SUM(Calculation3) as SumCalculation3, AVG(Calculation3) as AvgCalculation3
FROM #intermediate

Unless of course... there are additional parts in your problem you left out. But in general, using intermediary tables is ok, and often the fastest way to process these kinds of complicated calculations. Trying to bunch it all up into one SQL SELECT is just asking for a maintenance nightmare.