Calculation problem

The forumla for margin that I'm using is fairly simple:
(TotalSaleAmount -(MaterialCostTotal + LaborCostTotal)) / TotalSaleAmount
However, for some reason, I am coming up with some wacky values when I try
to implement this as shown below. I have rewritten this in several different
versions, but have been unsuccessful to get the correct values as I would in
the simple formula. Anyone with a set of eagle eyes see where I've gone
wrong?
Select
(Select ISNULL
(Sales.TotalSaleAmount -
((Select SUM (ISNULL (SupplyCosts.Quantity, 0) * ISNULL
(SupplyCosts.UnitPrice, 0))
FROM SupplyCosts
WHERE SupplyCosts.SalesID = Sales.SalesID)
+
(Select SUM (ISNULL (LaborCost, 0)) FROM LaborJobCosts
WHERE LaborJobCosts.SalesID = Sales.SalesID)
/Sales.TotalSaleAmount),0)
)
as GrossMarginPerJob
If these are integers and you are expecting decimals, you need to convert at
least one value to decimal.
(1.0 * (TotalSaleAmount - (MaterialCostTotal + LaborCostTotal))) /
(TotalSaleAmount * 1.0)
http://www.aspfaq.com/
(Reverse address to reply.)
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:OnOQ1JhXEHA.3988@.tk2msftngp13.phx.gbl...
> The forumla for margin that I'm using is fairly simple:
> (TotalSaleAmount -(MaterialCostTotal + LaborCostTotal)) / TotalSaleAmount
> However, for some reason, I am coming up with some wacky values when I try
> to implement this as shown below. I have rewritten this in several
different
> versions, but have been unsuccessful to get the correct values as I would
in
> the simple formula. Anyone with a set of eagle eyes see where I've gone
> wrong?
> Select
> (Select ISNULL
> (Sales.TotalSaleAmount -
> ((Select SUM (ISNULL (SupplyCosts.Quantity, 0) * ISNULL
> (SupplyCosts.UnitPrice, 0))
> FROM SupplyCosts
> WHERE SupplyCosts.SalesID = Sales.SalesID)
> +
> (Select SUM (ISNULL (LaborCost, 0)) FROM LaborJobCosts
> WHERE LaborJobCosts.SalesID = Sales.SalesID)
> /Sales.TotalSaleAmount),0)
> )
> as GrossMarginPerJob
>
|||And this worked? You appear to be missing a "FROM" pointing to the SALES
table statement for the first SELECT statement.
Andrew C. Madsen
Information Architect
Harley-Davidson Motor Company
"Earl" <brikshoe@.newsgroups.nospam> wrote in message
news:OnOQ1JhXEHA.3988@.tk2msftngp13.phx.gbl...
> The forumla for margin that I'm using is fairly simple:
> (TotalSaleAmount -(MaterialCostTotal + LaborCostTotal)) / TotalSaleAmount
> However, for some reason, I am coming up with some wacky values when I try
> to implement this as shown below. I have rewritten this in several
different
> versions, but have been unsuccessful to get the correct values as I would
in
> the simple formula. Anyone with a set of eagle eyes see where I've gone
> wrong?
> Select
> (Select ISNULL
> (Sales.TotalSaleAmount -
> ((Select SUM (ISNULL (SupplyCosts.Quantity, 0) * ISNULL
> (SupplyCosts.UnitPrice, 0))
> FROM SupplyCosts
> WHERE SupplyCosts.SalesID = Sales.SalesID)
> +
> (Select SUM (ISNULL (LaborCost, 0)) FROM LaborJobCosts
> WHERE LaborJobCosts.SalesID = Sales.SalesID)
> /Sales.TotalSaleAmount),0)
> )
> as GrossMarginPerJob
>
|||Quantity is integer and the other values are all of type "money".
"Aaron [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ub7Q1ShXEHA.1000@.TK2MSFTNGP12.phx.gbl...
> If these are integers and you are expecting decimals, you need to convert
at[vbcol=seagreen]
> least one value to decimal.
> (1.0 * (TotalSaleAmount - (MaterialCostTotal + LaborCostTotal))) /
> (TotalSaleAmount * 1.0)
> --
> http://www.aspfaq.com/
> (Reverse address to reply.)
>
>
> "Earl" <brikshoe@.newsgroups.nospam> wrote in message
> news:OnOQ1JhXEHA.3988@.tk2msftngp13.phx.gbl...
TotalSaleAmount[vbcol=seagreen]
try[vbcol=seagreen]
> different
would
> in
>
|||On Tue, 29 Jun 2004 14:51:29 -0500, Earl wrote:

>The forumla for margin that I'm using is fairly simple:
>(TotalSaleAmount -(MaterialCostTotal + LaborCostTotal)) / TotalSaleAmount
>However, for some reason, I am coming up with some wacky values when I try
>to implement this as shown below. I have rewritten this in several different
>versions, but have been unsuccessful to get the correct values as I would in
>the simple formula. Anyone with a set of eagle eyes see where I've gone
>wrong?
>Select
>(Select ISNULL
>(Sales.TotalSaleAmount -
>((Select SUM (ISNULL (SupplyCosts.Quantity, 0) * ISNULL
>(SupplyCosts.UnitPrice, 0))
>FROM SupplyCosts
>WHERE SupplyCosts.SalesID = Sales.SalesID)
>+
>(Select SUM (ISNULL (LaborCost, 0)) FROM LaborJobCosts
>WHERE LaborJobCosts.SalesID = Sales.SalesID)
>/Sales.TotalSaleAmount),0)
>)
>as GrossMarginPerJob
>
Hi Earl,
Without knowing what "wacky values" you got and what "unwacky values" you
expected, it's quite hard to see what might be wrong.
Could you please post the following:
1: Table stucture (as CREATE TABLE statements, including all constraints)
for all relevant tables. You may omit irrelevant columns.
2: Sample data (as INSERT statements).
3: Expected output from the sample data.
4: The results you got from the query (including any error messages).
With that, we've got something that we can work on!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)