Compute value from settings table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Compute value from settings table

Author

Topic

bobred
Starting Member

14 Posts

Posted - 2011-09-07 : 11:05:51

Not at all sure how to go about this, here goes

I have a table of goods which have a cost, a markup and a tax. The levels of markup and tax are stored in a settings table. When a new goods item is added or updated I wish to compute the markup and tax for the item based on the values inthe settings table.

How do I go about this?

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-09-07 : 12:02:02

You are going to have to supply another level of detail before anyone can be of assistance. What does the Settings table look like (Schema) and how does it define the items you want calculated? Some sample data and expected results would also clarify your requirements and allow us to help you.

=======================================
The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies)

bobred
Starting Member

14 Posts

Posted - 2011-09-07 : 12:27:23

Sorry.
The settings table contains only one row with an id and the markup and tax settings, both of which are decimal values 0.15 and 0.20 respectively.

What I need is
CostMarkup = (markup*cost)+cost
CostTax = (CostMarkup*tax)+CostMarkup

I have also been informed that markup has a ceiling of 100, so some conditional will be needed. I have tried various things but none work.
James

Bustaz Kool
Master Smack Fu Yak Hacker

1834 Posts

Posted - 2011-09-07 : 14:50:44

Does this fit the bill? If not, can you elaborate on what you need?[CODE]select
p.cost + (p.cost * case when s.markup > 100.0 then 100.0 else s.markup end) CostMarkup,
(p.cost + (p.cost * case when s.markup > 100.0 then 100.0 else s.markup end)) * (1 + s.tax) CostTax
from
Products p
cross join
Settings s[/CODE]When you say that Markup has an upper limit of 100, do you mean 100%? If so, change the 100.0 to 1.0

=======================================
The first rule of Tautology Club is the first rule of Tautology Club. -xkcd, (Honor Societies)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources