Basic math in MS SQL stored procedure across three tables
Posted on 2010-08-24
I'm trying to do some very basic math in MS SQL by using one stored procedure to SUM the value of a column in Table_A, then divide the SUM from Table_A by the page view total from Table_B - and UPDATE the value in Table_C.
Basically I'm trying to work out the eCPC for a lead generating site on a page view basis (so every time a user visits a profile the eCPC is updated against the SUM of leads generated).
val = common denominator across all three tables
So step 1 is:
SELECT SUM(lead_value) as lead_sum FROM Table_A WHERE camp_id='val'
SELECT page_views FROM Table_B WHERE camp_id='val'
new_eCPC = lead_sum / page_views
UPDATE Table_C SET lead_eCPC='new_eCPC' WHERE camp_id='val'
Now I can easilly do this in ASP, but I have to execute an SQL script three times to do this - which I believe is terribily inefficient as I want it to happen each time a profile is viewed.
Can someone please help we write a stored procedure for this so I only have to execute it once on each profile view?