March 30, 2012 at 4:38 am
Hi,
New here and admittedly, MSSQL is not my thing at all. I have a task to reshape some pricing in a database which i use for a website.
I have costs which are determined by a particular cost band. The pricing is somewhat messed up so it needs reshaping, given that there are thousands of entries, i dont fancy having to do this manually.
So an overview of the problem (MSSQL Server 2008 R2)
dbo.cost - Column_Cost Column_Mark_Up
"Cost1" = £10.00 Band A (add "Mark_Up" of 10%) outputs new total upon calculation
"Cost2"= £12.00 Band B (add "Mark_Up" 12.5%) outputs new total upon calculation
"Cost3"= £15.00 Band C (add "Mark_Up" 15%) outputs new total upon calculation
"Cost4"= £20.00 Band D (add "Mark_Up" 10%) outputs new total upon calculation
I would need to automate the process so the Stored proc would run at least daily.
Could somebody give me some guidance on how this could be best achieved.
March 30, 2012 at 4:58 am
Fopa (3/30/2012)
Hi,New here and admittedly, MSSQL is not my thing at all. I have a task to reshape some pricing in a database which i use for a website.
I have costs which are determined by a particular cost band. The pricing is somewhat messed up so it needs reshaping, given that there are thousands of entries, i dont fancy having to do this manually.
So an overview of the problem (MSSQL Server 2008 R2)
dbo.cost - Column_Cost Column_Mark_Up
"Cost1" = £10.00 Band A (add "Mark_Up" of 10%) outputs new total upon calculation
"Cost2"= £12.00 Band B (add "Mark_Up" 12.5%) outputs new total upon calculation
"Cost3"= £15.00 Band C (add "Mark_Up" 15%) outputs new total upon calculation
"Cost4"= £20.00 Band D (add "Mark_Up" 10%) outputs new total upon calculation
Your table design is not optimized for this, I would recommend having a Cost and Markup Table, with foreign keys. Below is a CASE Statement to handle the pricing in one statement, although I wouldn't say it's the best solution:
CASE WHEN BAND_COLUMN_NAME = 'A' Then ((Column_Cost * 10)/100)
WHEN BAND_COLUMN_NAME = 'B' Then ((......
WHEN BAND_COLUMN_NAME = 'C' Then ((....
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
This thing is addressing problems that dont exist. Its solution-ism at its worst. We are dumbing down machines that are inherently superior. - Gilfoyle
March 30, 2012 at 5:04 am
Hi and thanks for your suggestion, i shall give that a whirl now.
I have also tried the following:
DECLARE @Cost FLOAT
SELECT @Cost =SUM(Cost)*10/100 FROM dbo._Cost_Test
SELECT Mark_Up,@Cost,Mark_Up+@Cost FROM dbo._Cost_Test
Which outputs the calculation.
March 30, 2012 at 6:05 am
i would prefer to track the cost price and the retail price, and calcuate the percentage of markup myself.
so often the retail price is going to end in "9", to fit in with the typical advertising scheme of most businesses,right?
so even though i buy it for a dollar, and my profit target is 15%; i'd sell it for say $1.19, so i think it's better to calculate my markup, instead of using my markup to calculate the retail price.
Lowell
March 30, 2012 at 6:29 am
Hi Lowell, thanks for your reply.
The cost price is service related and not an end product as such, we add mark up to the price as a middleman scenario, so the fixed percentages are specific to price bands as there are thousands upon thousands of services.
cost price + our fixed mark up % = total cost price to end consumer.
The cost price responsibility is dictated by suppliers.
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy