Saturday, February 04, 2012 10:21 AM
There are few rules which i want to come out from database itself...like it should be configured according to month or according to days ....but database should support both...one at a time.
....Suppose i have registration date 1 jan 2012 now one rule can be like they want to provide 40% discount for first 30 days from the registration date then first 60 days after registration date they want 50% discount ....or one rule can be in jan month they want discount of 20% then in Feb 30 % ...
- Edited by glimpse_s Saturday, February 04, 2012 12:59 PM
Saturday, February 04, 2012 11:46 AM
create table #DiscountPolicyType ( id int primary key ,descr nvarchar(100) ,paramName nvarchar (100) ) go insert into #DiscountPolicyType values (1,'By days from registration', 'Number of days') , (2 ,'By calendar month', 'Month number') create table #DiscountPolicy ( id int primary key ,typeId int foreign key references #DiscountPolicyType(id) ,paramValue int ,dicsount decimal(2,2) ) go insert into #DiscountPolicy values (1,1,30,0.4) , (2,1,60,0.6) , (3,2,1,0.2) , (4,2,2,0.3) , (5,2,3,0.25) , (6,2,4,0.15) , (7,2,5,0.33) go create procedure DiscountOptions (@regDate datetime, @purchaseDate datetime) as begin select t.descr, t.paramName, p.paramValue, p.dicsount from #DiscountPolicyType t left join ( (select top 1 p1.* from #DiscountPolicy p1 where p1.typeId=1 and p1.paramValue >= DATEDIFF(day,@regDate, @purchaseDate) order by p1.paramValue) union select * from #DiscountPolicy p2 where p2.typeId=2 and p2.paramValue = MONTH(@purchaseDate) ) p on p.typeId=t.id end go DECLARE @RC int DECLARE @regDate datetime DECLARE @purchaseDate datetime set @regDate=GETDATE() set @purchaseDate= GETDATE()+4 EXECUTE @RC = [dbo].[DiscountOptions] @regDate ,@purchaseDate GO drop table #DiscountPolicyType go drop table #DiscountPolicy go drop procedure DiscountOptions go
Monday, February 06, 2012 1:54 PMAnswererSerg, that is a possible implementation. Though the code is right there, i found it confusing to follow. Please explain what the approach is.
Monday, February 06, 2012 4:17 PM
I see two ways over there.
1. Every DiscountPolicyType reflected as a separate entity:
Dicsount_By_days_from_registration(days (PK), discount)
2. Generalized table DiscountPolicy(type, paramValue,dicsount )
plus metadata DiscountPolicyType() about paramValue meaning depending on type.
I choose the second because a kind of run-time configuration was requested. which may imply adding more rules , etc.
- Marked As Answer by Ed Price - MSFTMicrosoft Employee, Owner Thursday, December 06, 2012 1:55 AM