I come from lazy developer land where I had a very nice DBA who would write stored procedures based on variables I would pass for data manipulation.
Now, Iíve moved to hard-luck developer land where there are no nice DBAs and my SQL is a bit rusty and out of date.
Iím hoping for a bit of help. Iím working on SQL 2008
At the time a payment is made, I have variables being passed called UserId and Amount.
I have to get a percentage of this amount and insert that into another tracking table.
Here is what I have:
User table with columns: IsApproved (bit), IsLockedOut (bit)
Rev_Perc table with columns: NumUsers (int), Perc (decimal 2,2)
The Rev_Perc table is populated with these values:
Upon getting passed the UserId and Amount, the procedure needs to get the number of active (IsApprove=True, IsLockedOut=False) records in the User table.
SELECT COUNT(*) AS NumUse FROM Users
I need to compare the NumUse to the Rev_Perc table and draw the appropriate Perc value. (e.g. NumUse = 2499 or less, greater than 499 = .18), then multiply that value by the Amount passed, then insert a record into another table as follows:
NumUsers, (From Count)
Perc, (Percentage from Rev_Perc)
Co_Rev, (Amount * Perc = Co_Rev)
Date_Time, (Date/Time record is generated)