sql - I need to calculate values for a record in a database based off of other values in other records

Question

I need to calculate values for a record in a database based off of other values in other records. Using SqlServer 2012, what would be the best way to do this? I'm thinking some type of script that runs on the server that may be able to query for the values it needs to compute, compute them, and insert them into the record it needs to. I know you can have computed columns based off of other columns in SqlServer, but what about new records based off of different columns in different records?

Thanks!

EDIT:

I'm using a google charts table on an MVC4 Razor website to show items purchased by specific users by month and year; looks something like this:

Email Address | Purchase Value | Year | Month

This currently works absolutely fine. I query the database for purchases by user and group by month and year and sum the purchases, and I put the values in the table. I also have category filters that only show one month and one year, so only one user is shown at a time.

Now management wants an 'All' selection on the category filter, which means that for every month of every year, and every year total, I'm going to have to compute a cumulative purchase value for each user and put it in the table; you can imagine, if the users list gets very long, this could take some time. So, I think the best option would probably be to have a script that groups purchases by year and by user and updates a new record every time a donation is made anytime within that year; obviously, you'd do the same for each month of the year. That way, I wouldn't have to worry about computing this when the user requests the page. I'm just not sure how to go about writing a script for SQLServer that would be able to do something like this.

Answer 1

This shows how to calculate values for a record in a database based off of other values in other records. The example is written in TSQL and can be executed on SQL Server. You will need to change the script to use your tables and columns.

DECLARE @total dec(12,2), @num int --Variable declaration

SET @total = (SELECT SUM(Salary) FROM Employee) --Capture sum of employee salaries

SET @num = (SELECT COUNT(ID) FROM Employee) --Capture the number of employees

SELECT @total 'Total', --calculate values for a record in a database based off of other values in other records
        @num 'Number of employees',
        @total/@num 'Average'
INTO
    dbo.AverageSalary

Hope this helps.