-
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.
-
To get the appropriate Perc value you may use following select
SELECT TOP 1 @Perc = Perc FROM Rev_Perc WHERE NumUsers >= @NumUsers ORDER BY NumUsers
you should consider scenario when your number of users is greater than max value register in Reg_Perc
currently If you pass value greater than 9999 it will return empty result and that could be a problem for your insert statement.
You Have To Be Happy With What You Have To Be Happy With (KC)
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
|
|