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)
|||Hi Earl,
Hugo's suggestion is good. If you could provide those information, it would
be easier for us to understand the actual problem.
Thank you,
Bill Cheng
Microsoft Online Partner Support
Get Secure! www.microsoft.com/security
This posting is provided as is with no warranties and confers no rights.
| Reply-To: "Earl" <brikshoe@.newsgroups.nospam>
| From: "Earl" <brikshoe@.newsgroups.nospam>
| References: <OnOQ1JhXEHA.3988@.tk2msftngp13.phx.gbl>
<ub7Q1ShXEHA.1000@.TK2MSFTNGP12.phx.gbl>
| Subject: Re: Calculation problem
| Date: Tue, 29 Jun 2004 17:53:12 -0500
| Lines: 53
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
| X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
| Message-ID: <uHYEYviXEHA.2972@.TK2MSFTNGP12.phx.gbl>
| Newsgroups: microsoft.public.sqlserver.server
| NNTP-Posting-Host: pcp02482226pcs.spedwy01.in.comcast.net 68.57.249.69
| Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP12.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349225
| X-Tomcat-NG: microsoft.public.sqlserver.server
|
| 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
| > 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
| > >
| > >
| >
| >
|
|
|
|||Thanks to Bill, Hugo, Andrew, and Aaron. I just ran out of time to work with
that issue, so I pulled the 3 values across and did all the math in code
functions. I will revisit this issue as soon as possible.
""Bill Cheng"" <billchng@.online.microsoft.com> wrote in message
news:zwdq6umXEHA.3648@.cpmsftngxa06.phx.gbl...
> Hi Earl,
> Hugo's suggestion is good. If you could provide those information, it
would
> be easier for us to understand the actual problem.
>
> Thank you,
> Bill Cheng
> Microsoft Online Partner Support
> Get Secure! - www.microsoft.com/security
> This posting is provided "as is" with no warranties and confers no rights.
> --
> | Reply-To: "Earl" <brikshoe@.newsgroups.nospam>
> | From: "Earl" <brikshoe@.newsgroups.nospam>
> | References: <OnOQ1JhXEHA.3988@.tk2msftngp13.phx.gbl>
> <ub7Q1ShXEHA.1000@.TK2MSFTNGP12.phx.gbl>
> | Subject: Re: Calculation problem
> | Date: Tue, 29 Jun 2004 17:53:12 -0500
> | Lines: 53
> | X-Priority: 3
> | X-MSMail-Priority: Normal
> | X-Newsreader: Microsoft Outlook Express 6.00.2800.1409
> | X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2800.1409
> | Message-ID: <uHYEYviXEHA.2972@.TK2MSFTNGP12.phx.gbl>
> | Newsgroups: microsoft.public.sqlserver.server
> | NNTP-Posting-Host: pcp02482226pcs.spedwy01.in.comcast.net 68.57.249.69
> | Path: cpmsftngxa06.phx.gbl!TK2MSFTNGP08.phx.gbl!TK2MSFTN GP12.phx.gbl
> | Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.server:349225
> | X-Tomcat-NG: microsoft.public.sqlserver.server
> |
> | 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
> | > 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
> | > >
> | > >
> | >
> | >
> |
> |
> |
>