I have a textbox with a value like this:
="The average score is: " & 100/9.5 & " points."
I've tried all different conversion functions I can think of but with no
result.
/PeterPeter Larsson wrote:
> I have a textbox with a value like this:
> ="The average score is: " & 100/9.5 & " points."
> I've tried all different conversion functions I can think of but with no
> result.
> /Peter
Try this:
="The average score is: " & FormatNumber(100/9.5,2) & " points."
The second argument (2) returns the number of digits past the decimal.|||Michael, thanks for replying!
Your suggestion works fine (="The average score is: " & 100/9.5 & "
points." I simplified the calculation in my original posting.
Now I have the number of decimals correct, but still only an integer
value like 1.00
The thing is that I use this in a footer row of a table, it looks
exactly like this:
="The average score is: " &
FormatNumber(SUM(Fields!Score.Value*Fields!Exp.Value)/First(Fields!Count,"NumberOfPlayers"),2)
& " points."
The values left of the \ sign is from the table dataset itself, the
NumberOfPlayers dataset is not.
I guess I have to some conversion in the expression, but I can't find
the correct function. :(
ANy ideas?
Best regards,
Peter Larsson!
Michael wrote:
> Peter Larsson wrote:
>> I have a textbox with a value like this:
>> ="The average score is: " & 100/9.5 & " points."
>> I've tried all different conversion functions I can think of but with
>> no result.
>> /Peter
> Try this:
> ="The average score is: " & FormatNumber(100/9.5,2) & " points."
> The second argument (2) returns the number of digits past the decimal.|||On Feb 11, 9:47=A0am, Peter Larsson <scape...@.hotmail.com> wrote:
> Michael, thanks for replying!
> Your suggestion works fine (=3D"The average score is: " & 100/9.5 & "
> points." I simplified the calculation in my original posting.
> Now I have the number of decimals correct, but still only an integer
> value like 1.00
> The thing is that I use this in a footer row of a table, it looks
> exactly like this:
> =3D"The average score is: " &
> FormatNumber(SUM(Fields!Score.Value*Fields!Exp.Value)/First(Fields!Count,"=N=ADumberOfPlayers"),2)
> & " points."
> The values left of the \ sign is from the table dataset itself, the
> NumberOfPlayers dataset is not.
> I guess I have to some conversion in the expression, but I can't find
> the correct function. :(
> ANy ideas?
> Best regards,
> Peter Larsson!
>
> Michael wrote:
> > Peter Larsson wrote:
> >> I have a textbox with a value like this:
> >> =3D"The average score is: " & 100/9.5 & " points."
> >> I've tried all different conversion functions I can think of but with
> >> no =A0 result.
> >> /Peter
> > Try this:
> > =3D"The average score is: " & FormatNumber(100/9.5,2) & " points."
> > The second argument (2) returns the number of digits past the decimal.- =Hide quoted text -
> - Show quoted text -
I would make sure that the Data returned is a Floating point number,
and not Integer somewhere along the line. For example, your
denominator First(Fields!Count.Value) may actually be returning an
Integer (since Counts are typically whole numbers) and thus dividing
by an Integer is an Integer, which have no decimal places, hense the
"1.000"
=3D"The average score is: " &
Format( Sum(Fields!Score.Value * Fields!Exp.Value )
/ CDbl(First(Fields!Count.Value,"N=ADumberOfPlayers")), "0.00" ) & "
points."
-- Scott|||Orne wrote:
> On Feb 11, 9:47 am, Peter Larsson <scape...@.hotmail.com> wrote:
>> Michael, thanks for replying!
>> Your suggestion works fine (="The average score is: " & 100/9.5 & "
>> points." I simplified the calculation in my original posting.
>> Now I have the number of decimals correct, but still only an integer
>> value like 1.00
>> The thing is that I use this in a footer row of a table, it looks
>> exactly like this:
>> ="The average score is: " &
>> FormatNumber(SUM(Fields!Score.Value*Fields!Exp.Value)/First(Fields!Count,"NumberOfPlayers"),2)
>> & " points."
>> The values left of the \ sign is from the table dataset itself, the
>> NumberOfPlayers dataset is not.
>> I guess I have to some conversion in the expression, but I can't find
>> the correct function. :(
>> ANy ideas?
>> Best regards,
>> Peter Larsson!
>>
>> Michael wrote:
>> Peter Larsson wrote:
>> I have a textbox with a value like this:
>> ="The average score is: " & 100/9.5 & " points."
>> I've tried all different conversion functions I can think of but with
>> no result.
>> /Peter
>> Try this:
>> ="The average score is: " & FormatNumber(100/9.5,2) & " points."
>> The second argument (2) returns the number of digits past the decimal.- Hide quoted text -
>> - Show quoted text -
> I would make sure that the Data returned is a Floating point number,
> and not Integer somewhere along the line. For example, your
> denominator First(Fields!Count.Value) may actually be returning an
> Integer (since Counts are typically whole numbers) and thus dividing
> by an Integer is an Integer, which have no decimal places, hense the
> "1.000"
> ="The average score is: " &
> Format( Sum(Fields!Score.Value * Fields!Exp.Value )
> / CDbl(First(Fields!Count.Value,"NumberOfPlayers")), "0.00" ) & "
> points."
> -- Scott
Hi Scott,
Well, now it suddenly works, in the following format
formatnumber((val1*val2)/val3,2) without any conversion to double, all
numbers are integer. This is really strange because I tried it many times.
I just hate when stuff just suddenly start working again, and you have
no clue what was wrong.
Thanks for helping though. :)
Regards,
Peter Larsson!
Showing posts with label division. Show all posts
Showing posts with label division. Show all posts
Sunday, March 11, 2012
Division returning 1 or 0
=Fields!FeeSchedule.Value / 100
it should be doing 25 / 100 = .25
Division question
I'm trying to divide 14/30. I would like to show my
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
Vic
Because your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
David Portas
SQL Server MVP
|||http://www.aspfaq.com/2483
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
Vic
Because your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
David Portas
SQL Server MVP
|||http://www.aspfaq.com/2483
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic
Division question
I'm trying to divide 14/30. I would like to show my
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
VicBecause your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
David Portas
SQL Server MVP
--|||http://www.aspfaq.com/2483
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx
.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
VicBecause your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
David Portas
SQL Server MVP
--|||http://www.aspfaq.com/2483
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx
.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic
Division question
I'm trying to divide 14/30. I would like to show my
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
VicBecause your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
--
David Portas
SQL Server MVP
--|||http://www.aspfaq.com/2483
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic
output as 0.4666. Using the % gives me 14. How can I
get the output that I want?
TIA,
VicBecause your SELECT statement doesn't specifiy any data types the values are
assumed to be integers. Integer division will return an integer result.
You can include a decimal which will give a decimal result:
SELECT 14/30.0
Or you can specify a data type explicitly:
SELECT 14/CAST(30 AS REAL)
Both of the above will return a result with decimal precision but if you
want a particular number of decimals in the output you will have to CAST or
ROUND the result or do the formatting in your client app.
--
David Portas
SQL Server MVP
--|||http://www.aspfaq.com/2483
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Vic" <vduran@.specpro-inc.com> wrote in message
news:13c8101c4440d$fd1cc040$a101280a@.phx.gbl...
> I'm trying to divide 14/30. I would like to show my
> output as 0.4666. Using the % gives me 14. How can I
> get the output that I want?
> TIA,
> Vic
Division question
When I do the following query:
select convert(decimal(8,2),(1/3)) -- > returns .00
When I do the following query:
select convert(decimal(8,2),convert(float,1)/convert(float,3)) -- > returns
.33
Is there a better way to do the division other than converting each number
to float before dividing them ?
Thank you.I usually do this select 1.0*1/3
http://sqlservercode.blogspot.com/|||Hi Paul,
The reason that your first SQL statement returns .00 because it treated the
number 1 and 3 as integers and "if an integer is divided by an integer, the
result is an integer that has any fractional part of the result truncated.".
Therefore, only a integer is returned for the convert function. i.e. 0 -> .0
0.
So you do need to convert the numbers first unless you use this:
Select (1.0/3.0)
Hope this help.
Dennis Lam
"Paul fpvt2" wrote:
> When I do the following query:
> select convert(decimal(8,2),(1/3)) -- > returns .00
> When I do the following query:
> select convert(decimal(8,2),convert(float,1)/convert(float,3)) -- > return
s
> .33
> Is there a better way to do the division other than converting each number
> to float before dividing them ?
> Thank you.
select convert(decimal(8,2),(1/3)) -- > returns .00
When I do the following query:
select convert(decimal(8,2),convert(float,1)/convert(float,3)) -- > returns
.33
Is there a better way to do the division other than converting each number
to float before dividing them ?
Thank you.I usually do this select 1.0*1/3
http://sqlservercode.blogspot.com/|||Hi Paul,
The reason that your first SQL statement returns .00 because it treated the
number 1 and 3 as integers and "if an integer is divided by an integer, the
result is an integer that has any fractional part of the result truncated.".
Therefore, only a integer is returned for the convert function. i.e. 0 -> .0
0.
So you do need to convert the numbers first unless you use this:
Select (1.0/3.0)
Hope this help.
Dennis Lam
"Paul fpvt2" wrote:
> When I do the following query:
> select convert(decimal(8,2),(1/3)) -- > returns .00
> When I do the following query:
> select convert(decimal(8,2),convert(float,1)/convert(float,3)) -- > return
s
> .33
> Is there a better way to do the division other than converting each number
> to float before dividing them ?
> Thank you.
Division query
i have an equation which i have to implement in query:
availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
when tried the query i get avail as zero always, help me outNAVIN.D skrev:
> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out
Without knowing what the above equation is or where it is used the easy
explanation would be that it's caused by integer division, ie.
everything below 1.0 will become 0.
/impslayer, aka Birger Johansson|||You will get an integer result when all operands are integer. You can
specify a decimal operand if you need decimal result. The result will
decimal in that case because the decimal data type has a higher precedence
than integer. Try:
availa = (1.0-[return0]/sum(r1+r2+r3+r4+r5))
Hope this helps.
Dan Guzman
SQL Server MVP
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:48C5F07C-1023-4AB1-9B17-356321EB95CD@.microsoft.com...
> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out|||If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
return a value within the datatype integer. (The answer will be truncated).
Try
(1.00 - [return0]/sum(r1+r2+r3+r4+r5)
"NAVIN.D" wrote:
> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out|||thank you mark and imsplayer it was decimal and integer funda only i got it
immd after posting anyways thanks for the reply
"Mark Williams" wrote:
> If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
> return a value within the datatype integer. (The answer will be truncated)
.
> Try
> (1.00 - [return0]/sum(r1+r2+r3+r4+r5)
>
> --
>
> "NAVIN.D" wrote:
>
availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
when tried the query i get avail as zero always, help me outNAVIN.D skrev:
> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out
Without knowing what the above equation is or where it is used the easy
explanation would be that it's caused by integer division, ie.
everything below 1.0 will become 0.
/impslayer, aka Birger Johansson|||You will get an integer result when all operands are integer. You can
specify a decimal operand if you need decimal result. The result will
decimal in that case because the decimal data type has a higher precedence
than integer. Try:
availa = (1.0-[return0]/sum(r1+r2+r3+r4+r5))
Hope this helps.
Dan Guzman
SQL Server MVP
"NAVIN.D" <NAVIND@.discussions.microsoft.com> wrote in message
news:48C5F07C-1023-4AB1-9B17-356321EB95CD@.microsoft.com...
> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out|||If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
return a value within the datatype integer. (The answer will be truncated).
Try
(1.00 - [return0]/sum(r1+r2+r3+r4+r5)
"NAVIN.D" wrote:
> i have an equation which i have to implement in query:
> availa= (1-[return0]/sum(r1+r2+r3+r4+r5))
> when tried the query i get avail as zero always, help me out|||thank you mark and imsplayer it was decimal and integer funda only i got it
immd after posting anyways thanks for the reply
"Mark Williams" wrote:
> If SUM(r1+r2+r3+r4+r5) > (1-[return0]), you will get zero because SQL will
> return a value within the datatype integer. (The answer will be truncated)
.
> Try
> (1.00 - [return0]/sum(r1+r2+r3+r4+r5)
>
> --
>
> "NAVIN.D" wrote:
>
Division Problem
Hi there,
I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.
Any help is appreciated
I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.
If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:
1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1
The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:
1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression
The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.
I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,
Instead of using
<current value> / <Previous value> * 100 >= 10
to get the percentage.
I can use
(<current value> - <Previous value>) >= <current value> * 0.1
However the previous value may be bigger than the current so i also have to check
(<current value> - <Previous value>) <= (<current value> * 0.1) * -1
Therefor there is no division.
Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||
first. . . why two columns. . . this is not normalized. . .
you should have
Table: MonthlyTaskData:
TaskID|MonthID | MonthValue
any other schema is plain wrong!!!
[rant over]
given @.CurrMonthID and @.PrevMonthID
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)
This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.
I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.
Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)
Division Problem
Hi there,
I need some help on a query I'm trying to build. I have two columns, one has the current months value and the other has the previous months value. I only want to select the rows where the previous and current values differ by more than 10%. The problem I have is that some of the rows have values of 0.
Any help is appreciated
I am not sure what error you are getting or what is the expected behavior. From what I can understand, I will try to answer your queries.
If some rows have 0 value then you will get divide by zero errors. You can avoid this by doing one of the following:
1. use an expression like x/nullif(y, 0). This will prevent divide by zero errors and return NULL. You can further modify the NULL value like coalesce(x/nullif(y, 0), -1) or any other appropriate value
2. You can use SET ARITHABORT OFF setting to return NULL for such division errors. This is not a recommended way since this setting can affect use of indexes on computed columns or indexed views for example. Best is to handle the calculations correctly in your expressions as described in step #1
The other problem might be that you are performing integer arithmetic. SQL Server by default uses the data types of the involved variables/columns/values in the expression to determine how it performs the arithmetic operation. You can find more details in Books Online. So if both your columns are integer data types then dividing them will be based on integer arithmetic which may not produce the expected results. You can do one of the following:
1. coerce data type of one value to desired numeric data type so that the resulting expression is not integer value. Ex: x/cast(y as float) or more explicit cast(x as numeric(10, 0))/cast(y as numeric(10,0))
2. Or modify existing value using floating point operations for example to change the behavior. Ex: x/(y * 1.) will result in floating point value for the expression
The first method is preferable since you can explicitly control the resulting data type without letting SQL Server to interpret 1. and assign default precision/scale for the value. For more details, look in the using decimal/float data in Books Online.
I have, however, simplified the problem somewhat. I can strip out the chance of it dividing by zero by changing the way I compute the difference. For example,
Instead of using
<current value> / <Previous value> * 100 >= 10
to get the percentage.
I can use
(<current value> - <Previous value>) >= <current value> * 0.1
However the previous value may be bigger than the current so i also have to check
(<current value> - <Previous value>) <= (<current value> * 0.1) * -1
Therefor there is no division.
Only question I can think of now is, is there a way to force the result of a calculation to be positive?
|||Yes, try the ABS(numeric_expression) function.|||
first. . . why two columns. . . this is not normalized. . .
you should have
Table: MonthlyTaskData:
TaskID|MonthID | MonthValue
any other schema is plain wrong!!!
[rant over]
given @.CurrMonthID and @.PrevMonthID
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
prev.TaskID is null -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)
This is actually a stored procedure where the current month value is returned from the table and the previous months value is returned by something like you have above.
I was merely giving a hypothetical example that was simplified to show the problem I was trying to solve so I could take that solution and apply it to my stored procedure.
Thank you for the repsonse, it's good to know someone is paying attention :)|||oh. . . and my query has a mistake -
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur left join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue = 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID ) or
-- HERES THE CHANGE
(prev.TaskID is null and cur.MonthID = @.CurMonthID) -- has no previuous month
union
select cur.TaskID, cur.MonthID, cur.MonthValue
from MonthData cur inner join MonthData prev on
cur.TaskID = prev.TaskID
where
(
prev.MonthValue <> 0 and
cur.MonthID = @.CurMonthID and
Prev.MonthID = @.PrevMonthID
) and
(
(abs(prev.MonthValue - cur.MonthValue)/ prev.MonthValue) > .1
)
Division operator
I'm trying to perform a simple mathematical operation on an integer. The integer is the result of the COUNT() function, I need to divide this by a number, say 5, and then round up that number.
For example, if the result of the operation was 52.4, it would become 53, if it were 52.6 it would be 53 and If it were 52, it would remain 52.
Is this kind of functionality built in to T-SQL ?
The main problem I'm having is that the result of COUNT(blah)/5 returns an integer, and ignores the remainder.
Thanks,
PaulIf you look up "division" in BOL you will get a better understanding of what is happenig.
you might try "cast(count(blah) as float)/5"
For example, if the result of the operation was 52.4, it would become 53, if it were 52.6 it would be 53 and If it were 52, it would remain 52.
Is this kind of functionality built in to T-SQL ?
The main problem I'm having is that the result of COUNT(blah)/5 returns an integer, and ignores the remainder.
Thanks,
PaulIf you look up "division" in BOL you will get a better understanding of what is happenig.
you might try "cast(count(blah) as float)/5"
Division not working
The following yields strange results
select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
([FieldB]>=1)
sample output is
FieldA FieldB FieldC
4 11 0
3 4 0
3 4 0
5 12 0
5 12 0
4 5 0
Addition, subtraction and multiplication all work properly, but division
isn't. Is there a flag somewhere in SQL that needs to be set for division to
work?
ThanksInteger division yield integer result. try casting one of the columns
involved in the expression to a datatype with greater precedence.
select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
TableA
where ([FieldB]>=1)
AMB
"mike" wrote:
> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
to
> work?
> Thanks|||Hi
Results are fine if the columns are integer datatype. 4/11 = 0 in integer
division
select
FieldA,
FieldB,
(CONVERT(DECIMAL(18,2), FieldA) / CONVERT(DECIMAL(18,2), FieldB)) as
FieldC
from
TableA
where
FieldB>=1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:95F2FAF7-1282-410B-8E00-831EA32921A6@.microsoft.com...
> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA
> where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
> to
> work?
> Thanks|||D'oh!
Thanks, I'd tried casting the entire result, but not each field prior to
division.
"Alejandro Mesa" wrote:
> Integer division yield integer result. try casting one of the columns
> involved in the expression to a datatype with greater precedence.
> select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
> TableA
> where ([FieldB]>=1)
>
> AMB
> "mike" wrote:
>|||Mike,
If you cast the entire result, then you will be casting the integer result
and this will give you the same problem. It is not the same casting the
variables / columns involved in the expression to yield a higher precedence
datatype as casting the result.
Example:
select cast(1 as numeric(5, 3)) / 2, cast(1 / 2 as numeric(5, 3))
AMB
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>|||Yes, unfortunately Casting the result is too late.. The Integer division has
already generated the wrong (zero) result...
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>
select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
([FieldB]>=1)
sample output is
FieldA FieldB FieldC
4 11 0
3 4 0
3 4 0
5 12 0
5 12 0
4 5 0
Addition, subtraction and multiplication all work properly, but division
isn't. Is there a flag somewhere in SQL that needs to be set for division to
work?
ThanksInteger division yield integer result. try casting one of the columns
involved in the expression to a datatype with greater precedence.
select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
TableA
where ([FieldB]>=1)
AMB
"mike" wrote:
> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
to
> work?
> Thanks|||Hi
Results are fine if the columns are integer datatype. 4/11 = 0 in integer
division
select
FieldA,
FieldB,
(CONVERT(DECIMAL(18,2), FieldA) / CONVERT(DECIMAL(18,2), FieldB)) as
FieldC
from
TableA
where
FieldB>=1
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"mike" <mike@.discussions.microsoft.com> wrote in message
news:95F2FAF7-1282-410B-8E00-831EA32921A6@.microsoft.com...
> The following yields strange results
> select [FieldA], [FieldB], ([FieldA]/[FieldB]) as [FieldC] from TableA
> where
> ([FieldB]>=1)
> sample output is
> FieldA FieldB FieldC
> 4 11 0
> 3 4 0
> 3 4 0
> 5 12 0
> 5 12 0
> 4 5 0
> Addition, subtraction and multiplication all work properly, but division
> isn't. Is there a flag somewhere in SQL that needs to be set for division
> to
> work?
> Thanks|||D'oh!
Thanks, I'd tried casting the entire result, but not each field prior to
division.
"Alejandro Mesa" wrote:
> Integer division yield integer result. try casting one of the columns
> involved in the expression to a datatype with greater precedence.
> select [FieldA], [FieldB], (([FieldA] * 1.00) / [FieldB]) as [FieldC] from
> TableA
> where ([FieldB]>=1)
>
> AMB
> "mike" wrote:
>|||Mike,
If you cast the entire result, then you will be casting the integer result
and this will give you the same problem. It is not the same casting the
variables / columns involved in the expression to yield a higher precedence
datatype as casting the result.
Example:
select cast(1 as numeric(5, 3)) / 2, cast(1 / 2 as numeric(5, 3))
AMB
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>|||Yes, unfortunately Casting the result is too late.. The Integer division has
already generated the wrong (zero) result...
"mike" wrote:
> D'oh!
> Thanks, I'd tried casting the entire result, but not each field prior to
> division.
>
> "Alejandro Mesa" wrote:
>
Division not returning whole numbers and not decimal values
I'm trying to divide 2 int columns and am expecting a result in a decimal
value, however, only whole values are being returned. First I tried
returning a calculated value from a select statement like this:
CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
INSERT INTO #tmp (col1, col2)
VALUES (7,5)
SELECT col2 / col1
FROM #tmp
But it returned zero. So then I tried to update the calculated value into a
decimal column but I got the same result.
UPDATE #tmp
SET TEST = col2 / col1
SELECT col1, col2, TEST FROM #tmp
Any help on this? THANKS!
moondaddy@.nospam.nospamYou've got to convert at least 1 of the values to a floating-point or decima
l
type first. e.g:
select 4 / 3 -- returns int
select convert(float, 4) / 3 -- returns float
"moondaddy" wrote:
> I'm trying to divide 2 int columns and am expecting a result in a decimal
> value, however, only whole values are being returned. First I tried
> returning a calculated value from a select statement like this:
>
> CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
> INSERT INTO #tmp (col1, col2)
> VALUES (7,5)
> SELECT col2 / col1
> FROM #tmp
> But it returned zero. So then I tried to update the calculated value into
a
> decimal column but I got the same result.
> UPDATE #tmp
> SET TEST = col2 / col1
> SELECT col1, col2, TEST FROM #tmp
> Any help on this? THANKS!
> --
> moondaddy@.nospam.nospam
>
>
value, however, only whole values are being returned. First I tried
returning a calculated value from a select statement like this:
CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
INSERT INTO #tmp (col1, col2)
VALUES (7,5)
SELECT col2 / col1
FROM #tmp
But it returned zero. So then I tried to update the calculated value into a
decimal column but I got the same result.
UPDATE #tmp
SET TEST = col2 / col1
SELECT col1, col2, TEST FROM #tmp
Any help on this? THANKS!
moondaddy@.nospam.nospamYou've got to convert at least 1 of the values to a floating-point or decima
l
type first. e.g:
select 4 / 3 -- returns int
select convert(float, 4) / 3 -- returns float
"moondaddy" wrote:
> I'm trying to divide 2 int columns and am expecting a result in a decimal
> value, however, only whole values are being returned. First I tried
> returning a calculated value from a select statement like this:
>
> CREATE TABLE #tmp (col1 int, col2 int, TEST decimal(8,7))
> INSERT INTO #tmp (col1, col2)
> VALUES (7,5)
> SELECT col2 / col1
> FROM #tmp
> But it returned zero. So then I tried to update the calculated value into
a
> decimal column but I got the same result.
> UPDATE #tmp
> SET TEST = col2 / col1
> SELECT col1, col2, TEST FROM #tmp
> Any help on this? THANKS!
> --
> moondaddy@.nospam.nospam
>
>
Division in T-SQL
Hi,
hth|||The evaluation is done before the assignment and the engine does not look ahead to figure out what assumptions to make. If I type 12 the engine will assume integer.
Can anyone tell me why this doesn't return any decimals?
declare @.f float
set @.f = 6 / 18
print @.f
All i get is 0... Is there something obvious i've done wrong?
TIAhmmm...okay, it works when I add .0 at the end of one of the numbers.
Like: 18.0 or 6.0
Still doesn't make sense, though... Why does the query engine assume i want an integer answer when the variable is a float?|||you need to xplicitly convert them...
declare @.f float
set @.f = convert(float,6) / convert(float,18)
print @.f
hth|||The evaluation is done before the assignment and the engine does not look ahead to figure out what assumptions to make. If I type 12 the engine will assume integer.
division in SQL results in 0
| division in SQL results in 0 | Posted on: 01/09/2006 09:20:13 |
declare @.x int, @.y int, @.f float set @.x = 100 set @.y = 2000 set @.f = @.x / @.y print @.x print @.y print @.f in the above code @.f prints as 0 instead of 0.05 how would i correct that? | |
Try this:
declare @.x int, @.y int, @.f float
set @.x = 100
set @.y = 2000
set @.f = @.x / (@.y * 1.0)
print @.x
print @.y
print @.f
Division in Query - Simple, but Lost...
Hey everyone,
I am rather new to this, but I'm making way and learning a great deal.
However, after much research I've become lost on a topic and I'm hoping
you can help me out.
I have the following query:
SELECT Title, [Date], Number
FROM (
SELECT
'OptOuts' [Title], CONVERT(varchar, DateOptedOut, 101) [Date] ,
count(contacts.id) [Number]
FROM contacts
WHERE DateOptedOut > '10/06/2005'
GROUP BY CONVERT(varchar, DateOptedOut, 101)
UNION
SELECT
'Signups' [Title], CONVERT(varchar, dateEntered, 101) [Date] ,
count(contacts.id) [Number]
FROM contacts
WHERE dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)
)[Trend]
Order BY [Date]
This query works great in that it shows me how many people signed up
and opted out per day in one nice worksheet. However, I need for it to
do one more thing...
I am trying to get it to divide the two numbers to come up with
something called "Churn Rate" which is basically the OptOuts / Signups.
The one small other problem I'm running into is that if there are no
optouts on a certain day, it doesn't show the date and the number 0...
which would be nice.
If anyone has any ideas as to how I can get this done I would really,
really appreciate it! Thank you very much!By the way, I tried doing something like this:
SELECT ((SELECT count(contacts.id) FROM contacts WHERE WHERE
DateOptedOut > '10/06/2005')/(SELECT count(contacts.id) FROM
contacts WHERE dateEntered > '10/06/2005')) AS Percentage
But became lost when I tried to work that into the current query and
put it below the "Signup" and "Optout"... while trying to achieve this
look:
OptOuts-- Date -- Number
Signups-- Date -- Number
Churn -- Date -- Percentage
Thanks again for all your help!|||Hi, maybe something like
SELECT Title, [Date], [NumberOptOuts] + [NumberSignups] as [Number],
case when [NumberSignups] <> 0 then
[NumberOptOuts] / [NumberSignups] else
0 end
as [Churn Out]
FROM (
SELECT
'OptOuts' [Title], CONVERT(varchar, DateOptedOut, 101) [Date] ,
count(contacts.id) [NumberOptOuts], 0 as [NumberSignups]
FROM contacts
WHERE DateOptedOut > '10/06/2005'
GROUP BY CONVERT(varchar, DateOptedOut, 101)
UNION
SELECT
'Signups' [Title], CONVERT(varchar, dateEntered, 101) [Date] ,
0 as [NumberOptOuts], count(contacts.id) [NumberSignups]
FROM contacts
WHERE dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)
)[Trend]
Order BY [Date]
It looks like it might work :)
Peter|||Thanks for the attempt Peter, but for some reason the Churn Out column
just spits out zeros... :(
Any other ideas? Thanks again for everything!|||Sorry to bump this thread, but I was just curious if anyone else had
ideas...|||On 22 Nov 2005 07:54:39 -0800, andrew.tatum@.gmail.com wrote:
>Thanks for the attempt Peter, but for some reason the Churn Out column
>just spits out zeros... :(
>Any other ideas? Thanks again for everything!
Hi Andrew,
That's because both values used in the division are integers. SQL Server
will also use an integer to store the result.
SELECT 4 / 7
SELECT 4.0 / 7
or
SELECT 4 / 7.0
SELECT CAST(4 AS numeric(5,3)) / 7
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Nah, the problem is that this is union and every row has 0 either in
NumberOptOuts or NumberSignups.
I was too fast with sending the code before :)
wouldnt something like the following work instead?
Peter
SELECT Title, [Date], [NumberOptOuts] + [NumberSignups] as [Number],
case when [NumberSignups] <> 0 then
[NumberOptOuts] / [NumberSignups] else
0 end
as [Churn Out]
FROM
(
SELECT
'OptOuts' [Title], CONVERT(varchar, a.DateOptedOut, 101) [Date] ,
count(a.id) [NumberOptOuts], b.SignUpCount as [NumberSignups]
FROM contacts a,
(select count(id) SignUpCount from contacts where dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)) b
WHERE a.DateOptedOut > '10/06/2005'
GROUP BY CONVERT(varchar, a.DateOptedOut, 101)
UNION
SELECT
'Signups' [Title], CONVERT(varchar, dateEntered, 101) [Date] ,
b.OptOutCount as [NumberOptOuts], count(contacts.id) [NumberSignups]
FROM contacts a,
(select count(id) OptOutCount from contacts where DateOptedOut >
'10/06/2005'
GROUP BY CONVERT(varchar, DateOptedOut, 101)) b
WHERE dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)
)[Trend]
Order BY [Date]|||On Thu, 24 Nov 2005 17:15:05 -0000, Rogas69 wrote:
>Nah, the problem is that this is union and every row has 0 either in
>NumberOptOuts or NumberSignups.
>I was too fast with sending the code before :)
>wouldnt something like the following work instead?
(snip)
Hi Peter,
I had a quick look at it, but I'm afraid you are overcomplicating
things. How aboout trying this one instead:
DECLARE @.CutOffDate datetime
SET @.CutOffDate = '20050610' -- or '20051006'; you used ambiguous format
SELECT Date,
SUM(OptOut) AS NumberOptOuts,
SUM(SignUp) AS NumberSignUps,
SUM(OptOut + SignUp) AS Number,
(1.0 * SUM(OptOut)) / NULLIF(SUM(SignUp), 0) AS ChurnOut
FROM (SELECT DateOptedOut AS Date, 1 AS OptOut, 0 AS SignUp
FROM contacts
WHERE DateOptedOut > @.CutOffDate
UNION ALL
SELECT DateEntered AS Date, 0 AS OptOut, 1 AS SignUp
FROM contacts
WHERE DateEntered > @.CutOffDate) AS x
GROUP BY Date
ORDER BY Date
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||yes Hugo, you're right. that was just first approach as close to the
original query as possible. I supposed that there could have been better way
to implement it :)
as far as date is concerned - this date format was 'derived' from the
original query, i am using always yyyymmdd format :)
peter|||On Mon, 28 Nov 2005 11:42:24 -0000, Rogas69 wrote:
>yes Hugo, you're right. that was just first approach as close to the
>original query as possible. I supposed that there could have been better wa
y
>to implement it :)
>as far as date is concerned - this date format was 'derived' from the
>original query, i am using always yyyymmdd format :)
Hi Peter,
My apologies - when I wrote my reply, I somehow had you
with
Andrew (the original poster).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
I am rather new to this, but I'm making way and learning a great deal.
However, after much research I've become lost on a topic and I'm hoping
you can help me out.
I have the following query:
SELECT Title, [Date], Number
FROM (
SELECT
'OptOuts' [Title], CONVERT(varchar, DateOptedOut, 101) [Date] ,
count(contacts.id) [Number]
FROM contacts
WHERE DateOptedOut > '10/06/2005'
GROUP BY CONVERT(varchar, DateOptedOut, 101)
UNION
SELECT
'Signups' [Title], CONVERT(varchar, dateEntered, 101) [Date] ,
count(contacts.id) [Number]
FROM contacts
WHERE dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)
)[Trend]
Order BY [Date]
This query works great in that it shows me how many people signed up
and opted out per day in one nice worksheet. However, I need for it to
do one more thing...
I am trying to get it to divide the two numbers to come up with
something called "Churn Rate" which is basically the OptOuts / Signups.
The one small other problem I'm running into is that if there are no
optouts on a certain day, it doesn't show the date and the number 0...
which would be nice.
If anyone has any ideas as to how I can get this done I would really,
really appreciate it! Thank you very much!By the way, I tried doing something like this:
SELECT ((SELECT count(contacts.id) FROM contacts WHERE WHERE
DateOptedOut > '10/06/2005')/(SELECT count(contacts.id) FROM
contacts WHERE dateEntered > '10/06/2005')) AS Percentage
But became lost when I tried to work that into the current query and
put it below the "Signup" and "Optout"... while trying to achieve this
look:
OptOuts-- Date -- Number
Signups-- Date -- Number
Churn -- Date -- Percentage
Thanks again for all your help!|||Hi, maybe something like
SELECT Title, [Date], [NumberOptOuts] + [NumberSignups] as [Number],
case when [NumberSignups] <> 0 then
[NumberOptOuts] / [NumberSignups] else
0 end
as [Churn Out]
FROM (
SELECT
'OptOuts' [Title], CONVERT(varchar, DateOptedOut, 101) [Date] ,
count(contacts.id) [NumberOptOuts], 0 as [NumberSignups]
FROM contacts
WHERE DateOptedOut > '10/06/2005'
GROUP BY CONVERT(varchar, DateOptedOut, 101)
UNION
SELECT
'Signups' [Title], CONVERT(varchar, dateEntered, 101) [Date] ,
0 as [NumberOptOuts], count(contacts.id) [NumberSignups]
FROM contacts
WHERE dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)
)[Trend]
Order BY [Date]
It looks like it might work :)
Peter|||Thanks for the attempt Peter, but for some reason the Churn Out column
just spits out zeros... :(
Any other ideas? Thanks again for everything!|||Sorry to bump this thread, but I was just curious if anyone else had
ideas...|||On 22 Nov 2005 07:54:39 -0800, andrew.tatum@.gmail.com wrote:
>Thanks for the attempt Peter, but for some reason the Churn Out column
>just spits out zeros... :(
>Any other ideas? Thanks again for everything!
Hi Andrew,
That's because both values used in the division are integers. SQL Server
will also use an integer to store the result.
SELECT 4 / 7
SELECT 4.0 / 7
or
SELECT 4 / 7.0
SELECT CAST(4 AS numeric(5,3)) / 7
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Nah, the problem is that this is union and every row has 0 either in
NumberOptOuts or NumberSignups.
I was too fast with sending the code before :)
wouldnt something like the following work instead?
Peter
SELECT Title, [Date], [NumberOptOuts] + [NumberSignups] as [Number],
case when [NumberSignups] <> 0 then
[NumberOptOuts] / [NumberSignups] else
0 end
as [Churn Out]
FROM
(
SELECT
'OptOuts' [Title], CONVERT(varchar, a.DateOptedOut, 101) [Date] ,
count(a.id) [NumberOptOuts], b.SignUpCount as [NumberSignups]
FROM contacts a,
(select count(id) SignUpCount from contacts where dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)) b
WHERE a.DateOptedOut > '10/06/2005'
GROUP BY CONVERT(varchar, a.DateOptedOut, 101)
UNION
SELECT
'Signups' [Title], CONVERT(varchar, dateEntered, 101) [Date] ,
b.OptOutCount as [NumberOptOuts], count(contacts.id) [NumberSignups]
FROM contacts a,
(select count(id) OptOutCount from contacts where DateOptedOut >
'10/06/2005'
GROUP BY CONVERT(varchar, DateOptedOut, 101)) b
WHERE dateEntered > '10/06/2005'
GROUP BY CONVERT(varchar, dateEntered, 101)
)[Trend]
Order BY [Date]|||On Thu, 24 Nov 2005 17:15:05 -0000, Rogas69 wrote:
>Nah, the problem is that this is union and every row has 0 either in
>NumberOptOuts or NumberSignups.
>I was too fast with sending the code before :)
>wouldnt something like the following work instead?
(snip)
Hi Peter,
I had a quick look at it, but I'm afraid you are overcomplicating
things. How aboout trying this one instead:
DECLARE @.CutOffDate datetime
SET @.CutOffDate = '20050610' -- or '20051006'; you used ambiguous format
SELECT Date,
SUM(OptOut) AS NumberOptOuts,
SUM(SignUp) AS NumberSignUps,
SUM(OptOut + SignUp) AS Number,
(1.0 * SUM(OptOut)) / NULLIF(SUM(SignUp), 0) AS ChurnOut
FROM (SELECT DateOptedOut AS Date, 1 AS OptOut, 0 AS SignUp
FROM contacts
WHERE DateOptedOut > @.CutOffDate
UNION ALL
SELECT DateEntered AS Date, 0 AS OptOut, 1 AS SignUp
FROM contacts
WHERE DateEntered > @.CutOffDate) AS x
GROUP BY Date
ORDER BY Date
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||yes Hugo, you're right. that was just first approach as close to the
original query as possible. I supposed that there could have been better way
to implement it :)
as far as date is concerned - this date format was 'derived' from the
original query, i am using always yyyymmdd format :)
peter|||On Mon, 28 Nov 2005 11:42:24 -0000, Rogas69 wrote:
>yes Hugo, you're right. that was just first approach as close to the
>original query as possible. I supposed that there could have been better wa
y
>to implement it :)
>as far as date is concerned - this date format was 'derived' from the
>original query, i am using always yyyymmdd format :)
Hi Peter,
My apologies - when I wrote my reply, I somehow had you
withAndrew (the original poster).
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)
division in a view
In trying to simplify an overly complex query [at one time, 1000+ views and
14 queries run in query analyzer!], I am trying to incorporate many of the
different views/queries together but am having troubles with one thing;
trying to divide one of the columns in a view.
SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
Doesn't workPerhaps you can incorporate a CASE statement
select
case MLD1ST when 0 then 0
else
100 * (RCD1ST / MLD1ST)
end
from <your table>
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||Are you encountering a divide by zero error? Make sure that you code the
T-SQL in such a way as to not have any problems with a divide by zero error:
create table #foo (RCD1ST int, MLD1ST decimal(5,2))
insert into #foo values (5, 2)
insert into #foo values (10, 2)
insert into #foo values (15, 0)
insert into #foo values (20, null)
PRINT ''
PRINT '********************* this fails *********************'
SELECT *, 100 * (RCD1ST / MLD1ST) AS POR FROM #foo
go
PRINT ''
PRINT '********************* this works *********************'
SELECT *, CASE WHEN MLD1ST = 0 THEN NULL ELSE 100 * (RCD1ST / MLD1ST) END AS
POR FROM #foo
Keith
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||You can not reference a column alias in the same column list. Try:
SELECT
FIPS,
[MTG TYPE],
COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST,
AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
from ...
AMB
"notme" wrote:
> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Not really, depend if sql server decides to escalate the lock .
Example:
-- connection 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
where orderid = 10250
-- connection 2
use northwind
go
select * from orders
where orderid < 10250 or orderid > 10250
-- connection 1
rollback transaction
AMB
"notme" wrote:
> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Not really, depend if sql server decides to escalate the lock .
> Example:
> -- connection 1
> use northwind
> go
> begin transaction
> update orders
> set orderdate = orderdate
> where orderid = 10250
> -- connection 2
> use northwind
> go
> select * from orders
> where orderid < 10250 or orderid > 10250
> -- connection 1
> rollback transaction
>
> AMB
>
> "notme" wrote:
>|||On 3/23/2005 1:49:09 PM, "examnotes" wrote:
>You can not reference a column alias in the same column list. Try:
>SELECT
> FIPS,
> [MTG TYPE],
> COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST,
> AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
> AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
> 100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
>from ...
>
>AMB
>
>
That was it - thanks
jeff
14 queries run in query analyzer!], I am trying to incorporate many of the
different views/queries together but am having troubles with one thing;
trying to divide one of the columns in a view.
SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
Doesn't workPerhaps you can incorporate a CASE statement
select
case MLD1ST when 0 then 0
else
100 * (RCD1ST / MLD1ST)
end
from <your table>
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||Are you encountering a divide by zero error? Make sure that you code the
T-SQL in such a way as to not have any problems with a divide by zero error:
create table #foo (RCD1ST int, MLD1ST decimal(5,2))
insert into #foo values (5, 2)
insert into #foo values (10, 2)
insert into #foo values (15, 0)
insert into #foo values (20, null)
PRINT ''
PRINT '********************* this fails *********************'
SELECT *, 100 * (RCD1ST / MLD1ST) AS POR FROM #foo
go
PRINT ''
PRINT '********************* this works *********************'
SELECT *, CASE WHEN MLD1ST = 0 THEN NULL ELSE 100 * (RCD1ST / MLD1ST) END AS
POR FROM #foo
Keith
"notme" <not@.me.com> wrote in message
news:uOoxI89LFHA.1144@.TK2MSFTNGP09.phx.gbl...
> In trying to simplify an overly complex query [at one time, 1000+ views
and
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work|||You can not reference a column alias in the same column list. Try:
SELECT
FIPS,
[MTG TYPE],
COUNT(RCVD_1ST) AS RCD1ST,
COUNT(MAILED_1ST) AS MLD1ST,
AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
from ...
AMB
"notme" wrote:
> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Not really, depend if sql server decides to escalate the lock .
Example:
-- connection 1
use northwind
go
begin transaction
update orders
set orderdate = orderdate
where orderid = 10250
-- connection 2
use northwind
go
select * from orders
where orderid < 10250 or orderid > 10250
-- connection 1
rollback transaction
AMB
"notme" wrote:
> In trying to simplify an overly complex query [at one time, 1000+ views an
d
> 14 queries run in query analyzer!], I am trying to incorporate many of the
> different views/queries together but am having troubles with one thing;
> trying to divide one of the columns in a view.
> SELECT FIPS, [MTG TYPE], COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST, AVG(DATEDIFF(day, MAILED_1ST,
> RCVD_1ST)) AS mldgap, AVG(DATEDIFF(day, CLOSING, MAILED_1ST))
> AS closegap, 100 * (RCD1ST / MLD1ST) AS POR
> Doesn't work
>|||Sorry, wrong place.
AMB
"Alejandro Mesa" wrote:
> Not really, depend if sql server decides to escalate the lock .
> Example:
> -- connection 1
> use northwind
> go
> begin transaction
> update orders
> set orderdate = orderdate
> where orderid = 10250
> -- connection 2
> use northwind
> go
> select * from orders
> where orderid < 10250 or orderid > 10250
> -- connection 1
> rollback transaction
>
> AMB
>
> "notme" wrote:
>|||On 3/23/2005 1:49:09 PM, "examnotes" wrote:
>You can not reference a column alias in the same column list. Try:
>SELECT
> FIPS,
> [MTG TYPE],
> COUNT(RCVD_1ST) AS RCD1ST,
> COUNT(MAILED_1ST) AS MLD1ST,
> AVG(DATEDIFF(day, MAILED_1ST, RCVD_1ST)) AS mldgap,
> AVG(DATEDIFF(day, CLOSING, MAILED_1ST)) AS closegap,
> 100 * (COUNT(RCVD_1ST) / nullif(COUNT(MAILED_1ST), 0)) AS POR
>from ...
>
>AMB
>
>
That was it - thanks
jeff
Division in a precedence constraint expression does not work properly - huh?
Greetings,
I have an expression in a precedence constraint that is returning false when it should return true. This is the expression that returns false:
((5500 / 9990) * 100) > 10
The following expression returns true. I did the division (5500 / 9990) myself and substituted the resulting value:
((0.55055055055055055055055055055055) * 100) > 10
Why is the first expression returning false? I'm stuck in the mud up to my axles on this and I know I'll probably feel like a fool when I learn the answer...
Thanks,
BCB
The expression is return false because zero times 100 is less than 10. The first part is performing integer division, not decimal division.Cast one of the operands to a decimal type, and you will get the result you're looking for.
(( (DT_DECIMAL, 0) 5500 / 9990) * 100) > 10|||
Hello jaegd,
Thanks for putting me wise on this one.
Regards,
Black Cat Bone
Division Calc in Column Returns 0's or 1's
I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
calculated in a previous grouped view (View1). The column in View2 that
devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
record... but they should be actual numbers instead.
The divide column in View2 looks like this: Number2 / Number 1
The SQL statement looks like this:
SELECT Number1, Number2, Number2 / Number1 AS Expr1
FROM dbo.View1
What do you think is going on? Is SQL Server getting the DataTypes mixed
up?
Thank You!!
Scott Buerkley
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95If your numbers are INTs then you're getting integer divisions. For
example:
SELECT 1/2
returns 0.
Try casting one of them to NUMERIC:
SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
Expr1
FROM dbo.View1
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Hard to know for sure without DDL and sample data, but the most likely cause
is that Number1 and Number 2 are bot integer types (int or smallint, etc).
Then SQL does an integer divide and always returns an integer result.
Change one of them to a type which can have a decimal part (float, or
decimal, etc) before doing the divide, something like
SELECT Number1, Number2, (Cast Number2 As Float) / Number1 As Expr1
Tom
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Scott Buerkley wrote:
> I am dividing 2 numbers in a view (View2). The 2 numbers we sums of numbe
r
> calculated in a previous grouped view (View1). The column in View2 that
> devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
> record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
>
You're dividing two integers, coming up with a fractional value, and SQL
is rounding it to return it as an integer.|||Yes, the 2 numbers were integers and this worked!!
Thx,
Scott Buerkley
"Mike C#" <xyz@.xyz.com> wrote in message
news:ODvlDeimGHA.464@.TK2MSFTNGP05.phx.gbl...
> If your numbers are INTs then you're getting integer divisions. For
> example:
> SELECT 1/2
> returns 0.
> Try casting one of them to NUMERIC:
> SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
> Expr1
> FROM dbo.View1
> "Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
> news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95|||Yes, you were all correct. The 2 numbers were integers.
It is working now. Thanks for your help!!
Thx,
Scott Buerkley
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95
calculated in a previous grouped view (View1). The column in View2 that
devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
record... but they should be actual numbers instead.
The divide column in View2 looks like this: Number2 / Number 1
The SQL statement looks like this:
SELECT Number1, Number2, Number2 / Number1 AS Expr1
FROM dbo.View1
What do you think is going on? Is SQL Server getting the DataTypes mixed
up?
Thank You!!
Scott Buerkley
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95If your numbers are INTs then you're getting integer divisions. For
example:
SELECT 1/2
returns 0.
Try casting one of them to NUMERIC:
SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
Expr1
FROM dbo.View1
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Hard to know for sure without DDL and sample data, but the most likely cause
is that Number1 and Number 2 are bot integer types (int or smallint, etc).
Then SQL does an integer divide and always returns an integer result.
Change one of them to a type which can have a decimal part (float, or
decimal, etc) before doing the divide, something like
SELECT Number1, Number2, (Cast Number2 As Float) / Number1 As Expr1
Tom
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95|||Scott Buerkley wrote:
> I am dividing 2 numbers in a view (View2). The 2 numbers we sums of numbe
r
> calculated in a previous grouped view (View1). The column in View2 that
> devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
> record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
>
You're dividing two integers, coming up with a fractional value, and SQL
is rounding it to return it as an integer.|||Yes, the 2 numbers were integers and this worked!!
Thx,
Scott Buerkley
"Mike C#" <xyz@.xyz.com> wrote in message
news:ODvlDeimGHA.464@.TK2MSFTNGP05.phx.gbl...
> If your numbers are INTs then you're getting integer divisions. For
> example:
> SELECT 1/2
> returns 0.
> Try casting one of them to NUMERIC:
> SELECT Number1, Number2, CAST(Number2 AS NUMERIC(10, 5)) / Number1 AS
> Expr1
> FROM dbo.View1
> "Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
> news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95|||Yes, you were all correct. The 2 numbers were integers.
It is working now. Thanks for your help!!
Thx,
Scott Buerkley
"Scott Buerkley" <Scott@.ComputerRelief.ws> wrote in message
news:44a190a6$0$3253$a15e20c9@.news.newsgroupdirect.com...
>I am dividing 2 numbers in a view (View2). The 2 numbers we sums of number
>calculated in a previous grouped view (View1). The column in View2 that
>devieds 1 of the numbers by the other, returns either a 0 or a 1 for each
>record... but they should be actual numbers instead.
> The divide column in View2 looks like this: Number2 / Number 1
> The SQL statement looks like this:
> SELECT Number1, Number2, Number2 / Number1 AS Expr1
> FROM dbo.View1
> What do you think is going on? Is SQL Server getting the DataTypes mixed
> up?
> Thank You!!
> Scott Buerkley
> --
> The Source For Premium Newsgroup Access
> Great Speed, Great Retention
> 1 GB/Day for only $8.95
The Source For Premium Newsgroup Access
Great Speed, Great Retention
1 GB/Day for only $8.95
Division by zero problem
I've got the following SQL
SELECT count(*) FROM tablea A
JOIN tableb B ON ..etc..
WHERE a.string_val = 'test' AND
b.divider 0 AND
a.number 0 AND
(a.number / b.divider 0)
The b.divider value can be 0, but still I get the division by zero
error message.
I guess the reason is that the database performs the where statements
one at the time?
So when performing the division a.number / b.divider it could get 0 in
the divider even if b.divider 0 is also part of the where
statement??
My question is then.. How can I work around this problem? Changing the
database to set b.divider always 0 is not an optionMaybe this?
SELECT count(*) FROM tablea A
JOIN tableb B ON ..etc..
WHERE a.string_val = 'test' AND
b.divider 0 AND
a.number 0 AND
(a.number / NULLIF(b.divider,0) 0)|||Thank you :)
Division by Zero Error
I am getting a division by zero error from the SQL below, I believe I need to utilize Cast because it could be a negative number and I need to be able to view 8 positions to the right of the dec. (0000.00000000). Any help would be greatly appreciated.
SELECT FromDate AS MonthOneDate,State,Description,SUM(Flowthru) AS CELCSumFT,SUM(TotalCount) AS CELCsumCNT,
(SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct,
RetailNum AS ARZSumFT,RetailDen AS
ARZSumCnt,(RetailNum/RetailDen) * 100 AS ARZFtPct,
((RetailNum/RetailDen) - SUM(Flowthru) /SUM(TotalCount)) / SQRT(((SUM(Flowthru) + RetailNum) / (SUM(TotalCount) +RetailDen)) * (1-(SUM(Flowthru)+RetailNum)/(SUM(TotalCount) + RetailDen)) * (1 / SUM(TotalCount)+1/RetailDen)) AS ZScoreMonth1
FROM pmMidwest33_BlueRed WHERE TotalCount <> 0 AND CLEC <>
'' AND Acna NOT IN ('TRE','TRD') AND STATE IN ('IL','IZ','TR','MI','RE')
AND MONTH(FromDate) = MONTH(DATEADD(m,-2,GETDATE()))
AND YEAR(FROMDATE) = YEAR(DATEADD(m,-2,GETDATE()))
GROUP BY FromDate,State,Description,RetailNum,RetailDenDid you figure what part of your SQL query returns a divide by zero ? It could be :
sum(TotalCount),
RetailDen,
SQRT(((SUM(Flowthru) + RetailNum)
...
Before casting anything, I would look for the origin of the problem.|||for example you have
select (SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct from table
select CELCFtPct = case when SUM(TotalCount)) = 0 then 0
else (SUM(Flowthru)/SUM(TotalCount)) * 100 end
from table
You can do that for all your divisions so you do not get division by zero error. Not sure what field you needed this on but its a good ideal to do this anytime that you are dividing because at some point you are bound to divide by zero.
Hope that helps
KG|||Thank you for your help.. I figured out that my data types needed to be changed to float. I had it a varchar.
Joe
SELECT FromDate AS MonthOneDate,State,Description,SUM(Flowthru) AS CELCSumFT,SUM(TotalCount) AS CELCsumCNT,
(SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct,
RetailNum AS ARZSumFT,RetailDen AS
ARZSumCnt,(RetailNum/RetailDen) * 100 AS ARZFtPct,
((RetailNum/RetailDen) - SUM(Flowthru) /SUM(TotalCount)) / SQRT(((SUM(Flowthru) + RetailNum) / (SUM(TotalCount) +RetailDen)) * (1-(SUM(Flowthru)+RetailNum)/(SUM(TotalCount) + RetailDen)) * (1 / SUM(TotalCount)+1/RetailDen)) AS ZScoreMonth1
FROM pmMidwest33_BlueRed WHERE TotalCount <> 0 AND CLEC <>
'' AND Acna NOT IN ('TRE','TRD') AND STATE IN ('IL','IZ','TR','MI','RE')
AND MONTH(FromDate) = MONTH(DATEADD(m,-2,GETDATE()))
AND YEAR(FROMDATE) = YEAR(DATEADD(m,-2,GETDATE()))
GROUP BY FromDate,State,Description,RetailNum,RetailDenDid you figure what part of your SQL query returns a divide by zero ? It could be :
sum(TotalCount),
RetailDen,
SQRT(((SUM(Flowthru) + RetailNum)
...
Before casting anything, I would look for the origin of the problem.|||for example you have
select (SUM(Flowthru)/SUM(TotalCount)) * 100 AS CELCFtPct from table
select CELCFtPct = case when SUM(TotalCount)) = 0 then 0
else (SUM(Flowthru)/SUM(TotalCount)) * 100 end
from table
You can do that for all your divisions so you do not get division by zero error. Not sure what field you needed this on but its a good ideal to do this anytime that you are dividing because at some point you are bound to divide by zero.
Hope that helps
KG|||Thank you for your help.. I figured out that my data types needed to be changed to float. I had it a varchar.
Joe
Subscribe to:
Posts (Atom)