SQL Server - HowTo : Pro-rata individual records over an aggregate

Asked By pete_bristol on 23-Oct-08 10:33 PM
Dear All,

Please can you help?

I am new to SQL (although not to programming) and am confused how to
tackle the following problem, which is a much cut down version of my
real-life issues but hopefully demonstrates the point?

Essentially I need to pro-rata indiviual records pro-rata to some
lookup value in an associated table. I can link the tables OK but what
I get stuck with is the pro-ratering of the values primarily because
the "factor" is dependant on the sum of the records, but once the
factor is calculated, the records have past. Confused? perhaps an
example would help.


Table1        Value
A




pete_bristol replied on 23-Oct-08 10:33 PM
Apologies, I seem to have pressed post by mistake!!

carrying on:

Table1
Project      Value
A                10
A                20
A                30


Table2
Project     Value
A               120



Revised Table 1 should read

Project      Value
A                20
A                40
A                60

i.e  Lookup Table2 (120) / sum of project (60) * individual row i.e 2
* 10 etc

I hope this is clearer.

I look forward to your replies. Many thanks


Pete
Plamen Ratchev replied on 23-Oct-08 03:13 PM
On SQL Server 2005/2008 you can use the aggregate functions with the
OVER clause to accomplish this:

SELECT B.project,
B.value / SUM(A.value) OVER(PARTITION BY A.project) * A.value
FROM Table1 AS A
JOIN Table2 AS B
ON A.project = B.project;

--
Plamen Ratchev
http://www.SQLStudio.com
pete_bristol replied on 23-Oct-08 10:33 PM
alue

Thank you for your prompt reply.

But what if I was using SqlServer 2000?

Is there a plain sql work around? Ideally I dont want to create temp
tables but could if I had to.

Thanks in anticipation

Pete
Plamen Ratchev replied on 23-Oct-08 03:26 PM
Two methods for SQL Server 2000:

SELECT B.project,
B.value / C.total * A.value
FROM Table1 AS A
JOIN Table2 AS B
ON A.project = B.project
JOIN (SELECT project, SUM(value) AS total
FROM Table1
GROUP BY project) AS C
ON A.project = C.project;

SELECT B.project,
B.value / (SELECT SUM(C.value)
FROM Table1 AS C
WHERE C.project = A.project) * A.value
FROM Table1 AS A
JOIN Table2 AS B
ON A.project = B.project;

--
Plamen Ratchev
http://www.SQLStudio.com
pete_bristol replied on 23-Oct-08 10:33 PM
.value

Fantastic - sincere thanks!

Pete