Stored procedure to calculate Sales discounts across the board
I'm looking for a stored procedure to calculate Discounts for publishers by passing their pubID.
Each discounts vary per publishers.
For Example:
All of A Publishers will get 10% discount this month for all thrie published books
All of B Publishers will get 20% discounts this month for all thrie published books
All of C Publishers will get 30% discounts on all their books this month.
How to build the stored procedure.
Each discounts vary per publishers.
For Example:
All of A Publishers will get 10% discount this month for all thrie published books
All of B Publishers will get 20% discounts this month for all thrie published books
All of C Publishers will get 30% discounts on all their books this month.
How to build the stored procedure.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Nope u dont need..
In most of the ERP Suits, All discounts or Price lists used like this
Just create these tables and check what i ve said..
In most of the ERP Suits, All discounts or Price lists used like this
Just create these tables and check what i ve said..
ASKER
thanks ,
Please explain this part
" And in this table PublisherID and DiscountStartDate ll be PK
and PublisherID and DiscountEndDate ll be the 2nd Index"
Usage of this table is there must be a startdate fr each row.. But the usable discount rate depends on the end date. Null value of the DiscountEndDate shows the last discount.
when u want to get the discount values in this table
u must create a view like
Create view view_GetPublisherDiscount
as
select top 100 Percent PublisherID,Discount where DiscountEndDate is null
Please explain this part
" And in this table PublisherID and DiscountStartDate ll be PK
and PublisherID and DiscountEndDate ll be the 2nd Index"
Usage of this table is there must be a startdate fr each row.. But the usable discount rate depends on the end date. Null value of the DiscountEndDate shows the last discount.
when u want to get the discount values in this table
u must create a view like
Create view view_GetPublisherDiscount
as
select top 100 Percent PublisherID,Discount where DiscountEndDate is null
As i said be4, there ll be a table to collect Discount Rates for Each period and Publisher.
PK is the primary Key of the table this ll be used while Joining this table and Publisher ID
Second index ll be used for getting Null value of the endDate
Is that what u want ? or more explanations ?
Melih SARICA
PK is the primary Key of the table this ll be used while Joining this table and Publisher ID
Second index ll be used for getting Null value of the endDate
Is that what u want ? or more explanations ?
Melih SARICA
ASKER
confuse which is PK and Index.. need step through please
ok..
PublisherID and DiscountStartDate must be PK ( Clustered First Index )
PublisherID and DiscountEndDate must be secondary Index
PublisherID and DiscountStartDate must be PK ( Clustered First Index )
PublisherID and DiscountEndDate must be secondary Index
ASKER