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 GrossMarginPerJobIf 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
> 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
> >
> >
>|||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!TK2MSFTNGP12.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!TK2MSFTNGP12.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
> | > >
> | > >
> | >
> | >
> |
> |
> |
>