Sum and calculations for each customer in SQL stored procedure

Question

Hi all,

In a SQL database I got storage information for each user, for each customer.

I need to write a stored procedure that sums the disc usage (MB) for the customers users. When I got the total sum of all users for a single customer (totalDiscUsage), I need to perform a calculation (simple example):

x = numberOfUsers * 200
y = (totalDiscUsage - x) / (10 * 5)

After that, I need to write y to the database, and do that for all customers.

My question is how I can do this the best way?

Maybe using a cursor to go through each customer, perform the sum and calculation, and write the result to the database? Would that be a good solution?

Thanks in advance. Help will be much appreciated!

Answer 1

"The best way" to report on a given data set can't really be talked about without reference to how the data is stored, i.e. the relevant table definitions.

Unless of course you are asking how best to store the data to allow for this reporting, in which case you may need to reword your question.

Answer 2

You would definitly be able to do this with a cursor. There is also a chance you could do this in one statement with sub-queries or possibly pre calculations and a temp table but its hard to tell without more information.

Answer 3

Please - do not go around using cursors again! :-) SQL is set-based - avoid cursors whenever you can!

And here you can - easily. So for each customer, you need to determine the number of users first, and then do a simple calculation, and update the customer.

My suggestion would be:

• create a little function that calculates the number of users for a given customer
• create a second little function to do the same to calculate the total disk usage

• write your stored proc as a simple update statement that does the calculation

  CREATE PROCEDURE dbo.UpdateCustomers()
  AS BEGIN
      UPDATE Customers
      SET y = (dbo.GetTotalDiskUsage(CustomerID) - dbo.GetTotalUsers(CustomerID) * 200) / 50
  END 
  

Seems too easy, almost :-)

Marc

Answer 4

You can do an insert with a select query for the values such as:

INSERT INTO DiskUsage
SELECT SUM(Users.Usage) FROM Customers INNER JOIN Users ON somefield and somefield

or something like that. not 100% sure if that will work for your situation since I'm not really clear on it from your question.