-
Help with procedure
Greetings,
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:
499, .37
2499, .18
9999, .14
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.
(
@UserId int,
@Amount money
@NumUse int,
)
AS
BEGIN
SELECT COUNT(*) AS NumUse FROM Users
WHERE IsApproved=True
AND IsLockedOut=False
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:
dbo.Track
UserId, (Passed)
Amount, (Passed)
NumUsers, (From Count)
Perc, (Percentage from Rev_Perc)
Co_Rev, (Amount * Perc = Co_Rev)
Date_Time, (Date/Time record is generated)
Thanks in advance for any willing to assist.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|