Stored Procedure help needed please!
-
sasa
-
15df2o+1caatrp9bznv8 (4/28/2015)
ive hit a wall would anyone be able to help me with this ?TSQL Stored procedure
Create a stored procedure
which will insert a purchase of shares into the CustomerPortfolioTBL for a customer. The data that will be passed to the stored procedure is: the CustomerID, the ShareID, and the TransactionAmount. The data for the other columns in the CustomerPortfolioTBL will be got as follows
i)TransactionID ? make sure that this column is an identity column which will automatically generate the next TransactionID.
ii)TransactionType – this column will be filled by your sproc with the word “Purchase”.
iii)SharePrice ? the price will be read from the share SellPrice column in the ShareTBl for that ShareID.
iv)TransactionDate ? this column will be filled by your sproc with todays date ? use the GetDate() function.
Business Requirement
A.Customers are not allowed to purchase more than 5 times in the current month. Your sproc must therefore:-
i)Count the number of purchases the customer has made this month ? use the Month() and the GetDate() functions.
ii)If the no of purchases is greater than or equal to 5 your sproc must send an error message of “Sorry purchase is refused 5 purchases have been made this month”
B.Customers cannot spend over their exposure limit for a particular share category – get this from the CustomerExposureTBl. Your sproc must therefore:-
i)Read in to an internal variable the exposure limit from the CustomerExposureTBl for the inputted CustomerID.
ii)Add up all the transaction amount of shares that the customer has purchases (TransactionType = purchase) for shares whose category is the same as the share being purchased). Put this calculation into an internal variable.
iii)Add up all the transaction amount of shares that the customer has sold (TransactionType = sale) for shares whose category is the same as the share being purchased). Put this calculation into an internal variable.
iv)If ((Total Purchases of share in this category – Total Sales of share in this category) + TransactionAmount) is greater than exposure limit your sproc must send an error message of “Sorry purchase is refused Exposure limit is breached”
At first glance this looks like homework.
What have you done so far to meet the requirements?
-
sasa
-
can you help me please ?
-
15df2o+1caatrp9bznv8 (4/28/2015)
can you help me please ?Why did you delete your posts?
You also realize I am a volunteer and that I have my own job to work, right?
-
I agree with Lynn, let's start by seeing what you have done so far and see if we can tell exactly where you are getting hung up.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events -
...and if your classmates are on, what is your teacher's email address...?
-------------------------------------------------------------------------------------------------------------------------------------
Please follow Best Practices For Posting On Forums to receive quicker and higher quality responses
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply