August 17, 2015 at 11:12 am
Hello,
I'm struggling to develop a stored procedure with business logic for a bicycle rental company. I have a table called OrderItems with an attribute for the "RentalRate" for an item and another table called Inventory with an attribute called "CompetitorRateForItem". In the event that a competitor's rate for an item is below what we are charging for our rental rate, I want to apply a 5% discount to our rental rate so that we may remain competitive. I also want to add comments "Customer loyalty discount applied" in an attribute called "GeneralRentalOrderInformation" in the OrderItems table. I have attached the data model below and the fields in each table. Any assistance in how I could write this business logic encapsulated in a stored procedure would be greatly appreciated!
Thanks again in advance,
GreyKitten
[dbo].[Inventory]
[ItemID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[StoreID] INT NOT NULL,
[UniqueID] uniqueidentifier NULL,
[ProductSKU] NCHAR(20) NULL,
[ProductCategory] NVARCHAR(30) NULL,
[ProductDescription] NVARCHAR(4000) NULL,
[QuantityAvailable] INT NULL,
[Make] NVARCHAR(30) NULL,
[Model] NVARCHAR(30) NULL,
[ModelYear] NCHAR(4) NULL,
[ItemNotes] NVARCHAR(5000) NULL,
[MerchandiseCondition] NCHAR(2) NULL,
[ItemStatus] NCHAR(1) NULL,
[RentalStatus] BIT NULL,
[RepairsNeeded] NVARCHAR(4000) NULL,
[CompetitorRateForItem] MONEY NULL,
[CreateDate] DATETIME NOT NULL DEFAULT GETDATE(),
[UpdateDate] DATETIME NOT NULL,
CONSTRAINT INVENTORY_STORE_DETAILS FOREIGN KEY (StoreID)
REFERENCES STORE_DETAILS (StoreID)
ON DELETE CASCADE
ON UPDATE CASCADE
[dbo].[OrderItems]
[ItemID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[OrderID] INT PRIMARY KEY NOT NULL,
[EmployeeID] INT NOT NULL,
[RentalTypeName] NVARCHAR(20) NULL,
[RentalRate] MONEY NULL,
[CheckOutTime] DATETIME NULL,
[CheckInTime] DATETIME NULL,
[RentalDuration] TIME NULL,
[DelinquentRentalStatus] BIT NULL,
[ReturnCondition] NCHAR(2) NULL,
[LocationReturn] NVARCHAR(30) NULL,
[GeneralRentalOrderInformation] NVARCHAR(4000) NULL,
[LocationPhoneNumber] NVARCHAR(15) NULL,
[ReturnStatus] NCHAR(2) NULL,
[CreateDate] DATETIME NOT NULL DEFAULT GETDATE(),
[UpdateDate] DATETIME NULL,
CONSTRAINT ORDER_ITEM_pk PRIMARY KEY ([ItemID],[OrderID]),
CONSTRAINT ORDER_ITEM_INVENTORY FOREIGN KEY ([ItemID])
REFERENCES [dbo].[INVENTORY] ([ItemID])
ON DELETE CASCADE
ON UPDATE CASCADE,
CONSTRAINT ORDER_ITEM_ORDERS FOREIGN KEY ([OrderID])
REFERENCES [dbo].[Orders] ([OrderID])
ON DELETE CASCADE
ON UPDATE CASCADE
August 18, 2015 at 3:40 am
What parts are you having issues with?
Post what you have written already along with some sample data.
August 18, 2015 at 5:14 am
Your business logic needs some clarification.
First, i can hardly belive the company and its competitors have shared inventory database to compare prices Itemid by Itemid. Shouldn't it be some goods nomenclature, defined for example by
[ProductCategory] NVARCHAR(30) NULL,
[Make] NVARCHAR(30) NULL,
[Model] NVARCHAR(30) NULL,
[ModelYear] NCHAR(4) NULL,
[MerchandiseCondition] NCHAR(2) NULL
which is comparable from the customers point of view?
Next, what are parameters of the procedure in question? Will it process a single order (id as parameter) or parameters define some criteria to find orders to process?
August 18, 2015 at 5:58 am
The ItemID would be the input parameter because it's the primary key that allows the item to be joined between the two tables in query. The output parameters should include the ItemID, Make, Model, Year, ProductDescription, new adjusted RentalRate and Competitor Rate For Item.
August 18, 2015 at 3:31 pm
redmittens7 (8/17/2015)
[dbo].[OrderItems]
[ItemID] INT IDENTITY(1,1) PRIMARY KEY NOT NULL,
[OrderID] INT PRIMARY KEY NOT NULL,
It's not logical, or even realistic, for the linking ItemID to be an IDENTITY in this table like it is in Inventory.
Instead, it should be something like:
OrderItemId int IDENTITY(1,1) NOT NULL,
ItemID int NOT NULL,
OrderID int NOT NULL,
PRIMARY KEY ( OrderID, OrderItemId )
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply