Stored procedure to cancel an order

[Rettla]

Hi guys,

I am working on a project that simulates an online shares and securities trading environment. Participants can place buy/sell orders and cancel them. If an order is placed before the market closes at 1700, it should only be cancelled before 1700. It however seems that there is a loophole and participants are cancelling orders that would have already been processed.I have attached a copy of the stored procedure that I had tried but it still has that loophole. Please take a look and see how I can improve it.

Thanks

USE [JSESchoolsChallenge]
GO

SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER OFF
GO

Alter PROCEDURE [dbo].[spInstrumentOrderCancelTrialReg]

@StudentOrderTieID int,
@UserID int,
@retVal int OUTPUT

/*

Description    : This stored procedure Cancels the  Instrument Order with the orderID supplied
	           
Business Rules : 
	
Inputs         :	@InstrumentOrderID 		- Required

Outputs         :	@retVal 			- 1 there was an error; 0 = everything went fine

*/

AS

DECLARE @EntryID int
DECLARE @InstumentID int
DECLARE @Quantity int
DECLARE @Price float
DECLARE @InstrumentOrderID int
declare @marketCloseDate datetime
declare @orderPlacedDate datetime
declare @dayOfTheWeek int

SET NOCOUNT ON
SET @marketCloseDate = DATEADD(hour, 17, DATEDIFF(DAY, 2, GETDATE()))
SET @dayOfTheWeek = DATEPART(DW, GETDATE())

-- Check to see if the user is a administrator or not if not do student check
IF NOT EXISTS (SELECT ID FROM Administrator WHERE ID = @UserID )
BEGIN
	-- Check to see that the student cancelling the order is infact the student that placed the order.
	IF NOT EXISTS ( SELECT TOP 1 StudentOrderTie.StudentOrderTieID
	FROM StudentOrderTie
	WHERE  StudentOrderTie.StudentID = @UserID AND StudentOrderTie.StudentOrderTieID = @StudentOrderTieID)
	BEGIN
		SET @retVal = 1
		RETURN
	END
END

IF EXISTS ( SELECT TOP 1 InstrumentOrder.EntryID,  InstrumentOrder.instrumentID, InstrumentOrder.placedTimestamp,  StudentOrderTie.quantity,  StudentOrderTie.PriceAtPlacement,  OrderID
FROM InstrumentOrder INNER JOIN StudentOrderTie
	ON StudentOrderTie.OrderID = InstrumentOrder.InstrumentOrderID
WHERE  StudentOrderTie.StudentOrderTieID = @StudentOrderTieID AND StudentOrderTie.OrderStatusID = 4)

BEGIN
	SELECT @EntryID = InstrumentOrder.EntryID,  @InstumentID= InstrumentOrder.instrumentID, @Quantity = StudentOrderTie.quantity, @Price = StudentOrderTie.PriceAtPlacement, @InstrumentOrderID = OrderID, @orderPlacedDate= instrumentOrder.placedTimestamp
	FROM InstrumentOrder INNER JOIN StudentOrderTie
		ON StudentOrderTie.OrderID = InstrumentOrder.InstrumentOrderID
	WHERE  StudentOrderTie.StudentOrderTieID = @StudentOrderTieID AND StudentOrderTie.OrderStatusID = 4
	
	IF(@orderPlacedDate<@marketCloseDate)
	Begin 
	SET @retVal = 1
		
		-- StudentOrderTie order set to cancelled
		UPDATE StudentOrderTie SET OrderStatusID = 2 WHERE StudentOrderTie.StudentOrderTieID = @StudentOrderTieID 
		SET @Price = @Price * @Quantity
		EXEC spTransactionHistoryAdd  @EntryID, 5, @InstumentID, @Quantity, 0 -- 5 = 'OrderCancellation'
		
		-- Decrease the order of the main Order
		UPDATE InstrumentOrder SET Quantity = Quantity - @Quantity
		WHERE InstrumentOrderID = @InstrumentOrderID
		
		-- Main OrderCancelled if nessasary
		UPDATE InstrumentOrder SET InstrumentOrderStatusID = 2 WHERE Quantity = 0
	Print 'I am working'
	SET @retval = 0
	End
END
ELSE
BEGIN
	SET @retVal = 1
END

SET NOCOUNT OFF

[Rabbit]

Please post code within the thread itself and not as an attachment. Be sure to use the code tags.

You never check that the cancel command is before 1700.

[Rettla]

Please post code within the thread itself and not as an attachment. Be sure to use the code tags.

You never check that the cancel command is before 1700.

How would I do that? I thought the if statement with the two variables, @marketCloseDat e, @orderPlacedDat e does that?

[Rabbit]

Your market close date is set to two days before today at 1700. I don't know why you want two days prior to today.

Your order placed date is your order placed date.

In essence, you're checking to see if the order is placed before 2 days ago before 1700. That makes no sense to me. Shouldn't you be checking if the cancel is before 1700? Not if the order was placed before 1700.

[Rettla]

Thanks Rabbit, I am really pretty stupid, I don't know why I failed to remember that I needed to put a 0 instead of 2. Thanks

[Rabbit]

I still don't think it's doing what you think it's doing. Doesn't the cancel order have to be before 1700? It doesn't matter when the buy/sell order was placed, it matters what time they try to cancel the order.

[Rettla]

if you place an order, whether a buy or sell, before 1700 from monday to friday, you should only be able to cancel the order before 1700, the orders are processed after 1700. If you place an order after 1700, you can cancel it but up until 1700 of the next day.

On friday, all orders placed after 1700, should be cancellable after 1700 on saturday and sunday until 1700 on monday.

How best can I implement this

[Rabbit]

What you need to do is take the date time of the cancel into account. You don't do that in your stored procedure. I only see a reference to the buy/sell date time.

[Rettla]

I get your drift, so then what will I compare it to? The market closing date, or with both the market close date and the placed order date

[Rabbit]

Well, Ican only assume this as you haven't said otherwise, but I'm working under the assumption that the date time the stored procedure is run is the cancel date time. So you need to take the current date time into account when checking if they are allowed to cancel the order. I.E. something along the lines of

if current hour < 17 and order date is the same day as current date then
   cancel order
else if etc. etc. etc.

Where the etc. etc. etc. is your additional business constraints you mentioned earlier.