July 30, 2010 at 10:24 pm
We have an e-commerce website with hundreds of products . Need new functionality added to the website where after every 24 hours products will be automatically rotated in random manner.
How it will work
After every 24 hours products will be rotated in following manner. For eg we have 300 products in the database then first time 1st 20 products will be rotated with products from 40 to 60 in numbers , next time 20 to 40 products will be rotated with 60 to 80 numbers and so on. Please make sure after every 24 hours previous state of rotation will be maintained so if 1 to 20 are are rotated with 40 to 60 then we will have to make sure we track location of last product rotated so that after every rotation next series will come till the end of products and once all products are finished in the database it will start from 1 .
You can add more columns to the database as per your requirement.
Attached are tables and sp's attached for your review.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: <Nitin>
-- Updated by: Shyam
-- Create date: <28 jan 2009>
-- Description: <Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[insert_ProductNew]
@key nvarchar(50),
@ProductName nvarchar(50),
@ProductMainImagePath nvarchar(500),
@ProductSmallImagePath nvarchar(500),
@ProductVMImagePath nvarchar(500),
@ProductBigImagePath1 nvarchar(500),
@ProductBigImagePath2 nvarchar(500),
@ProductBigImagePath3 nvarchar(500),
@ProductSmallImagePath1 nvarchar(500),
@ProductSmallImagePath2 nvarchar(500),
@ProductSmallImagePath3 nvarchar(500),
@LongDescription nvarchar(max),
@Price numeric(18,2),
@PriceRangeID int,
@Quantity int,
@IsSingleVision bit,
@IsBifocalVision bit,
@BifocalVisionPrice numeric(18,2),
@IsVarifocalVision bit,
@VarifocalVisionPrice numeric(18,2),
@IsVisible bit,
--@Rank int,
@IsHotDeal bit,
@HotDealPrice numeric(18,2),
@LensTypeID int,
@FrameSizeID int,
@FrameMaterialID int,
@FrameLookID int,
@FrameTypeID int,
@IsLatestStyle bit,
@CategoriesXML text,
@ColorXML text,
@TintedLensColorXML text,
@PolarisedLensColor nvarchar(50),
@TransitionLensColor nvarchar(50),
@IsTintedLens bit,
@IsTransitionLens bit,
@IsPolarisedLens bit,
@TintedPrice numeric(18,2),
@TransitionPrice numeric(18,2),
@PolarisedPrice numeric(18,2),
@IsAntiReflective bit,
@AntiReflectivePrice numeric(18,2),
@IsUV100protective bit,
@UV100protectivePrice numeric(18,2),
@IsScratchResistant bit,
@ScratchResistantPrice numeric(18,2),
@PageURL nvarchar(50)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
BEGIN TRY
begin transaction
declare @a int
SET NOCOUNT ON;
set @a=1
--insert into product
-- Insert statements for procedure here
INSERT INTO [Product] (
ProductName,
ProductMainImagePath,
ProductSmallImagePath,
ProductVMImagePath,
ProductBigImagePath1,
ProductBigImagePath2,
ProductBigImagePath3,
ProductSmallImagePath1,
ProductSmallImagePath2,
ProductSmallImagePath3,
LongDescription,
Price,
PriceRangeID,
Quantity,
IsSingleVision,
IsBifocalVision,
IsVarifocalVision,
IsVisible,
--@Rank int,
IsHotDeal,
LensTypeID,
FrameSizeID,
FrameMaterialID,
FrameLookID,
FrameTypeID,
IsLatestStyle,
--@CategoriesXML text,
--@ColorXML text,
--@TintedLensColorXML text,
PolarisedColor,
TransitionColor,
IsTintedLens,
IsTransitionLens,
IsPolarisedLens,
IsAntiReflectiveCoating,
IsUV100protective,
IsScratchResistant,
BifocalVisionPrice,
VarifocalVisionPrice,
HotDealPrice,
TintedPrice,
TransitionPrice,
PolarisedLensPrice,
AntiReflectiveCoatingPrice,
UV100protectivePrice,
ScratchResistantPrice,
PageURL
)
--VALUES (
select
EncryptByPassPhrase(@Key, convert(varchar(50), @ProductName)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductMainImagePath)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductSmallImagePath)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductVMImagePath)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductBigImagePath1)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductBigImagePath2)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductBigImagePath3)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductSmallImagePath1)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductSmallImagePath2)),
EncryptByPassPhrase(@Key, convert(varchar(500), @ProductSmallImagePath3)),
EncryptByPassPhrase(@Key, convert(varchar(max), @LongDescription)),
@Price,
@PriceRangeID,
@Quantity,
@IsSingleVision,
@IsBifocalVision,
@IsVarifocalVision,
@IsVisible,
--@Rank int,
@IsHotDeal,
@LensTypeID,
@FrameSizeID,
@FrameMaterialID,
@FrameLookID,
@FrameTypeID,
@IsLatestStyle,
--@CategoriesXML text,
--@ColorXML text,
--@TintedLensColorXML text,
@PolarisedLensColor,
@TransitionLensColor,
@IsTintedLens,
@IsTransitionLens,
@IsPolarisedLens,
@IsAntiReflective,
@IsUV100protective,
@IsScratchResistant,
case when(@IsBifocalVision=1) then @BifocalVisionPrice else null end,
case when(@IsVarifocalVision=1) then @VarifocalVisionPrice else null end,
case when(@IsHotDeal=1) then @HotDealPrice else null end,
case when(@IsTintedLens=1) then @TintedPrice else null end,
case when(@IsTransitionLens=1) then @TransitionPrice else null end,
case when(@IsPolarisedLens=1) then @PolarisedPrice else null end,
case when(@IsAntiReflective=1) then @AntiReflectivePrice else null end,
case when(@IsUV100protective=1) then @UV100protectivePrice else null end,
case when(@IsScratchResistant=1) then @ScratchResistantPrice else null end,
EncryptByPassPhrase(@Key, convert(varchar(max), @PageURL))
set @a=2
-- if(@@error>0)
-- goto RollTran
declare @ProductID int
--update Rank
set @ProductID=scope_identity()
update [Product] set Rank =@ProductID, RankHotDeal=(select case when(max(RankHotDeal) is null) then 1 else Max(RankHotDeal)+1 end from Product), RankLatest =(select case when(max(RankLatest) is null) then 1 else Max(RankLatest)+1 end from Product) where ID=@ProductID
--insert into product_category
declare @XMLCategoriesXML int
exec sp_xml_preparedocument @XMLCategoriesXML output, @CategoriesXML
insert into ProductCategories(ProductID, CategoryID,Rank) select @ProductID, CategoryID,(select case when(max(Rank) is null) then 1 else Max(Rank)+1 end from ProductCategories) from openxml(@XMLCategoriesXML,'NewDataSet/dt_Category', 2) with (CategoryID int)
--insert into product_color
set @a=3
declare @XMLColorXML int
exec sp_xml_preparedocument @XMLColorXML output, @ColorXML
insert into Product_Color(ProductID,ColorID) select @ProductID, ColorID from openxml(@XMLColorXML,'NewDataSet/dt_Color',2) with (ColorID int)
--insert into product_tintedcolor
set @a=4
declare @XMLTintedLensColorXML int
exec sp_xml_preparedocument @XMLTintedLensColorXML output, @TintedLensColorXML
insert into Product_TintedColor(ProductID,TintedColorID) select @ProductID, TintedColorID from openxml(@XMLTintedLensColorXML,'NewDataSet/dt_TintedColor',2) with (TintedColorID int)
set @a=5
END TRY
BEGIN CATCH
Rollback transaction
--select @a as a
select '0'
return
END CATCH;
commit transaction
select @ProductID
return
END
------
CREATE TABLE [dbo].[Product](
[ID] [int] IDENTITY(1,1) NOT NULL,
[ProductName] [nvarchar](50) NOT NULL,
[ProductMainImagePath] [nvarchar](500) NOT NULL,
[ProductSmallImagePath] [nvarchar](500) NOT NULL,
[ProductVMImagePath] [nvarchar](500) NOT NULL,
[ProductBigImagePath1] [nvarchar](500) NOT NULL,
[ProductBigImagePath2] [nvarchar](500) NOT NULL,
[ProductBigImagePath3] [nvarchar](500) NOT NULL,
[ProductSmallImagePath1] [nvarchar](500) NOT NULL,
[ProductSmallImagePath2] [nvarchar](500) NOT NULL,
[ProductSmallImagePath3] [nvarchar](500) NOT NULL,
[LongDescription] [nvarchar](max) NULL,
[Price] [numeric](18, 2) NOT NULL,
[PriceRangeID] [int] NULL,
[Quantity] [int] NULL,
[IsSingleVision] [bit] NOT NULL,
[IsBifocalVision] [bit] NOT NULL,
[BifocalVisionPrice] [numeric](18, 2) NULL,
[IsVarifocalVision] [bit] NOT NULL,
[VarifocalVisionPrice] [numeric](18, 2) NULL,
[IsVisible] [bit] NOT NULL,
[Rank] [int] NULL,
[IsDelete] [bit] NOT NULL,
[IsHotDeal] [bit] NOT NULL,
[HotDealPrice] [numeric](18, 2) NULL,
[LensTypeID] [int] NULL,
[FrameSizeID] [int] NULL,
[FrameMaterialID] [int] NULL,
[FrameLookID] [int] NULL,
[FrameTypeID] [int] NULL,
[IsLatestStyle] [bit] NOT NULL,
[IsTintedLens] [bit] NOT NULL,
[TintedPrice] [numeric](18, 2) NULL,
[IsTransitionLens] [bit] NOT NULL,
[TransitionPrice] [numeric](18, 2) NULL,
[TransitionColor] [nvarchar](50) NULL,
[IsPolarisedLens] [bit] NOT NULL,
[PolarisedLensPrice] [numeric](18, 2) NULL,
[PolarisedColor] [nvarchar](50) NULL,
[IsAntiReflectiveCoating] [bit] NOT NULL,
[AntiReflectiveCoatingPrice] [numeric](18, 2) NULL,
[IsUV100Protective] [bit] NOT NULL,
[UV100ProtectivePrice] [numeric](18, 2) NULL,
[IsScratchResistant] [bit] NOT NULL,
[ScratchResistantPrice] [numeric](18, 2) NULL,
[Rating] [int] NOT NULL,
[PageURL] [nvarchar](50) NULL,
[RankHotDeal] [int] NULL,
[RankLatest] [int] NULL,
CONSTRAINT [PK_Product] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Define the display Order' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'Product', @level2type=N'COLUMN',@level2name=N'Rank'
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_ProductVMImagePath] DEFAULT (N'') FOR [ProductVMImagePath]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsDelete] DEFAULT ('False') FOR [IsDelete]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsHotDeal] DEFAULT ('False') FOR [IsHotDeal]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_HotDealPrice] DEFAULT ((0)) FOR [HotDealPrice]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsLatestStyle] DEFAULT ('False') FOR [IsLatestStyle]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsTintedLens] DEFAULT ('False') FOR [IsTintedLens]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_TintedPrice] DEFAULT ((0)) FOR [TintedPrice]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsTransitionLens] DEFAULT ('False') FOR [IsTransitionLens]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_TransitionPrice] DEFAULT ((0)) FOR [TransitionPrice]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_TransitionColor] DEFAULT (N'') FOR [TransitionColor]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsPolarisedLens] DEFAULT ('False') FOR [IsPolarisedLens]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_PolarisedLens] DEFAULT ((0)) FOR [PolarisedLensPrice]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_PolarisedColor] DEFAULT (N'') FOR [PolarisedColor]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsAntiReflectiveCoating] DEFAULT ('False') FOR [IsAntiReflectiveCoating]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_AntiReflectiveCoatingPrice] DEFAULT ((0)) FOR [AntiReflectiveCoatingPrice]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsUV100Protective] DEFAULT ('False') FOR [IsUV100Protective]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_UV100ProtectivePrice] DEFAULT ((0)) FOR [UV100ProtectivePrice]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_IsScratchResistant] DEFAULT ('False') FOR [IsScratchResistant]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_ScratchResistant] DEFAULT ((0)) FOR [ScratchResistantPrice]
GO
ALTER TABLE [dbo].[Product] ADD CONSTRAINT [DF_Product_Rating] DEFAULT ((2)) FOR [Rating]
GO
July 31, 2010 at 3:34 am
What have you tried so far? Where are you stuck?
We're happy to offer advice, but doing the entire project for you is a bit much to expect volunteers to do in their spare time.
Why is this urgent anyway?
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
July 31, 2010 at 6:33 am
Sounds like a consultant job to me. It would start with clarification of the requirement:
Either rotation is random (as requested) or rule-based (as described), why would I need the insert_ProductNew proc to swap products, and the list goes on....
Oh, before I forget: urgent means to double the hourly rate 😀
July 31, 2010 at 7:22 am
Gail, refresh my memory. What's our minimum hourly weekend rate for this kind of work?
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 31, 2010 at 9:09 am
As Gail mentioned, we are happy to help, but we are not here for code reviews or going through your requirements and trying to match them up.
If you want to explain what you expect each piece of code to do, or where it matches in the requirements and where you are storing data, we will do some checking.
But if something takes more than minutes, say 30, this isn't the place to post it. Hire someone that can help you.
July 31, 2010 at 9:29 am
I reread the original post. Where's the question? The post really sounded like: "Here, do this!".
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
July 31, 2010 at 12:10 pm
The way you're trying to handle the problem seems a bit awkward to me from an DA perspective.
- Why don't you just ad a "presentation_order" column and update that one to accomplish your problem ?
- Are you going to use a partitioned table to accomplish a fast sliding window ( out for the old date and in for the already prepared new data )
- Are there any dependent objects ? How are you going to handle them if needed ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
August 1, 2010 at 9:01 am
The "how it will work" description makes no sense, unless you are going to add products only in batches of 20. Even if it it were adapted to work for the case where the number of products is not a multiple of 20 it wouldn't satisfy the stated objective of having "random rotation", because (a) it isn't random or anything like it and (b) it doesn't do rotation.
A lot depends on how the application gets the products to be displayed from the database - presumably it doesn't display in product_id order (if it did rotation would create chaos with any bits of application which use database features involving any tables in which product_id is a foreign key). Does it get all the products (or all the "hot bargains" and use the appropriate the rank field to organise display order, or get all the products and display them in the order in which they occur in the returned dataset, or does it get a small number at a time (which makes rotation more difficult)?
The easy way is to have the app get all the products at once and display them in the order in which they are returned by the database, and let the database do a true rotation not a weird partial permutation as in the unworkable "how it works" description. Then you don't need any column(s) in the product table to describe the current order, the order can be described by a single integer in a single row table; if you want random rotations and want to get back to square one every so often you will need an extra integer in that single row to count the number of different rotations used since last the original order was used, and another which is the maximum that number is allowed to be; if you don't want any randomness you don't need the extra two numbers. The stored procedure which returns the set of products can ensure that they are returned in the rotated order by using something like "order by case when Product_ID > rotation_number then product_id-rotation_number else product_id+1073741823 end" (assuming your product IDs are ints greater than 0 and less than 1073741825, which is probably reasonable as you appear to generate them using IDENTITY) and you can have a rotate stored procedure called once a day (or whatever frequency you want) to adjust the values in the single row table as required.
Tom
August 1, 2010 at 1:50 pm
shishirbabel (7/30/2010)
We have an e-commerce website with hundreds of products . Need new functionality added to the website where after every 24 hours products will be automatically rotated in [font="Arial Black"]random[/font] manner.
How it will work
After every 24 hours products will be rotated in following manner. For eg we have 300 products in the database then first time 1st 20 products will be rotated with products from 40 to 60 in numbers , next time 20 to 40 products will be rotated with 60 to 80 numbers and so on. Please make sure after every 24 hours previous state of rotation will be maintained so if 1 to 20 are are rotated with 40 to 60 then we will have to make sure we track location of last product rotated so that [font="Arial Black"]after every rotation next series will come till the end of products and once all products are finished in the database it will start from 1 [/font].
The first paragraph says "random". The second paragraph says "series" and "rotation" neither of which are "random".
What's it going to be?
Also, while I very much appreciate the list of the tables, it's a little much for a forum. You need to dumb this down a bit and provide some test data if you actually want people to help with this. Please see the first link in my signature line below for how to do that properly.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 1, 2010 at 1:53 pm
This may be too much for the forum, but it would not be too much for a homework assignment.
For best practices on asking questions, please read the following article: Forum Etiquette: How to post data/code on a forum to get the best help[/url]
August 1, 2010 at 3:35 pm
Alvin Ramard (8/1/2010)
This may be too much for the forum, but it would not be too much for a homework assignment.
I actually don't believe it's a homework assignment or even a homework project. Just a gut feeling on my part and I could certainly be wrong. :hehe:
I would like it if the shishirbabel took the time to break the problem down for understanding instead of just posting everything he can get his hands on from the database. Spending that amount of time on his own job to create such an explanation might even help him solve his own problem because even he would understand it better. 😛
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2010 at 12:40 am
Jeff Moden (8/1/2010)
Alvin Ramard (8/1/2010)
This may be too much for the forum, but it would not be too much for a homework assignment.I actually don't believe it's a homework assignment or even a homework project. Just a gut feeling on my part and I could certainly be wrong. :hehe:
I would like it if the shishirbabel took the time to break the problem down for understanding instead of just posting everything he can get his hands on from the database. Spending that amount of time on his own job to create such an explanation might even help him solve his own problem because even he would understand it better. 😛
100% with you, Jeff 😎
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply