Rotate fields: February 2012

Saturday, February 25, 2012

Rounding and Grouping

Perhaps someone can settle an arguement for me ?

I have a set of data that I need to group together. SQL Script below.

CREATE TABLE [dbo].[CommTransactions] (
[ID] [id_type] NOT NULL ,
[TransactionID] [id_type] NULL ,
[ClientID] [id_type] NULL ,
[AccountCode] [varchar] (10) NULL ,
[Amount] [float] NULL ,
[CreateDateTime] [datetime] NULL

For the records I want to group the following applies.

The ID is unique and distinct.
The TransactionId is the same.
The ClientId is the same.
The AccountCode is different.
The Amount will be the same.
The CreateDateTime field is different by a few milliseconds.

I want to create a single line showing two account codes in different
fields. i.e. Staff and Manager (where their ID is the account code).

These can be entered in any order in the table mentioned.

The problem I have is I need to link two records together (that's the
problem in it's most simplistic terms). However, there may be
additional records with the same TransactionId, ClientId, AccountCode
and Amount, but happened at a slightly different time. It could be
done on the same day.

Now, the arguement is that we can group using the CreateDateTime
field. I argue that we can't as it will show down to the millisecond
and any rounding will not always allow for a match. If we added the
matching records once per day, then I can extract the date and group
on it, but if more than one group is added per day, then this would
cause the logic to fail.

So, are there any reliable methods for grouping date/time fields
reliably if there is a small difference (I suspect not)?

Is there anything I have missed ?

Any help or suggestions would be appreciated.

Thanks

RyanRyan,
Forgive me if I am not understanding the question correctly.
But I think the answer is that you don't have to group on a field; you
can group on an expression in most cases.
In this case, you can probably group by
convert(varchar,CreateDateTime,101), which is the date portion of
CreateDateTime.
I hate to suggest this because the performance will probably be terrible
unless your WHERE clause if very specific, but it may be the quick fix you
are looking for.

Best regards,
Chuck Conover
www.TechnicalVideos.net

"Ryan" <ryanofford@.hotmail.com> wrote in message
news:7802b79d.0402020100.41141655@.posting.google.c om...
> Perhaps someone can settle an arguement for me ?
> I have a set of data that I need to group together. SQL Script below.
> CREATE TABLE [dbo].[CommTransactions] (
> [ID] [id_type] NOT NULL ,
> [TransactionID] [id_type] NULL ,
> [ClientID] [id_type] NULL ,
> [AccountCode] [varchar] (10) NULL ,
> [Amount] [float] NULL ,
> [CreateDateTime] [datetime] NULL
> For the records I want to group the following applies.
> The ID is unique and distinct.
> The TransactionId is the same.
> The ClientId is the same.
> The AccountCode is different.
> The Amount will be the same.
> The CreateDateTime field is different by a few milliseconds.
> I want to create a single line showing two account codes in different
> fields. i.e. Staff and Manager (where their ID is the account code).
> These can be entered in any order in the table mentioned.
> The problem I have is I need to link two records together (that's the
> problem in it's most simplistic terms). However, there may be
> additional records with the same TransactionId, ClientId, AccountCode
> and Amount, but happened at a slightly different time. It could be
> done on the same day.
> Now, the arguement is that we can group using the CreateDateTime
> field. I argue that we can't as it will show down to the millisecond
> and any rounding will not always allow for a match. If we added the
> matching records once per day, then I can extract the date and group
> on it, but if more than one group is added per day, then this would
> cause the logic to fail.
> So, are there any reliable methods for grouping date/time fields
> reliably if there is a small difference (I suspect not)?
> Is there anything I have missed ?
> Any help or suggestions would be appreciated.
> Thanks
> Ryan|||Ryan (ryanofford@.hotmail.com) writes:
> Now, the arguement is that we can group using the CreateDateTime
> field. I argue that we can't as it will show down to the millisecond
> and any rounding will not always allow for a match. If we added the
> matching records once per day, then I can extract the date and group
> on it, but if more than one group is added per day, then this would
> cause the logic to fail.
> So, are there any reliable methods for grouping date/time fields
> reliably if there is a small difference (I suspect not)?

I'm not sure that I follow, but it sounds to me more like a business
problem.

You can group by the hour for instance:

SELECT yadadada, d, COUNT(*)
FROM (SELECT yadayada,
d = convert(char(8), CreateDateTime, 112) +
convert(char(5), CreateDateTime, 108)
FROM ...) AS a
GROUP BY yadayada, d

Of course, is a group is inserted so that some rows are inserted before
one o'clock, and others after you lose. Likewise, if two groups are
inserted the same hour.

A more complicated scheme may be devised where you compute the time
between two inserted rows, and if the difference is > some value,
those are two groups.

But you probably get a lot more robust application, by introducing a
marker which is unique for every batch you insert. This could still
be a datetime value, you just need to make sure that all rows in the
same batch gets the the same value.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Yep, pretty much as I suspected. Unfortunately this table is one
supplied by another company so I can't change it as easily as I want
without affecting their app. Our users expectation differs from what
this package does hence the problem.

I want the other company to change this slightly and there will be a
cost (fair enough), only problem is our company doesn't want to pay
for it. So, I'm trying to provide them with everything to prove they
either pay for the change or accept it won't work. They would rather
my team spend several days (at God knows what cost) examining
something I know won't work instead of paying for a days worth of
development.

Daft.

As you have guessed, I'm trying to steer them down the route of a
marker that I can group on.

Thanks for the help.

Ryan

Erland Sommarskog <sommar@.algonet.se> wrote in message news:<Xns94843E9F3EA3Yazorman@.127.0.0.1>...
> Ryan (ryanofford@.hotmail.com) writes:
> > Now, the arguement is that we can group using the CreateDateTime
> > field. I argue that we can't as it will show down to the millisecond
> > and any rounding will not always allow for a match. If we added the
> > matching records once per day, then I can extract the date and group
> > on it, but if more than one group is added per day, then this would
> > cause the logic to fail.
> > So, are there any reliable methods for grouping date/time fields
> > reliably if there is a small difference (I suspect not)?
> I'm not sure that I follow, but it sounds to me more like a business
> problem.
> You can group by the hour for instance:
> SELECT yadadada, d, COUNT(*)
> FROM (SELECT yadayada,
> d = convert(char(8), CreateDateTime, 112) +
> convert(char(5), CreateDateTime, 108)
> FROM ...) AS a
> GROUP BY yadayada, d
> Of course, is a group is inserted so that some rows are inserted before
> one o'clock, and others after you lose. Likewise, if two groups are
> inserted the same hour.
> A more complicated scheme may be devised where you compute the time
> between two inserted rows, and if the difference is > some value,
> those are two groups.
> But you probably get a lot more robust application, by introducing a
> marker which is unique for every batch you insert. This could still
> be a datetime value, you just need to make sure that all rows in the
> same batch gets the the same value.|||I have another thought that is worth a go. A slightly unusual approach
I must admit, but I think it may work.

I can establish the initial line that I want and take the
CreateDateTime from that. If I then add 1 minute to give me a start
time. Then subtract 1 minute to give me an end time, I can create a
table which holds the various ID fields, the accountcode I need and
the start and end times of a group.

I then use another query to pull out the second accountcode I want and
use a left join to the table I created previously, joining where the
createdatetime is between the start and end date. I add the
accountcode from the first table as a new field on the end of the
results of this query.

It means that the system will have a 2 minute window to commit the
transactions. Normally this is a few seconds, but I can adjust my
window.

I'll have to do some work checking where this can fail though, but
it's worth a little time doing this.

Feel free to pull this apart so I can check how well it will work.

Rounding a real number

Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald
REAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
David Portas
SQL Server MVP
|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald
|||The ROUND() function returns a number in the data type of the input expression. If NUMERIC, returns NUMERIC; if FLOAT, returns FLOAT. A NUMERIC is an exact data type where FLOAT is inexact. Since your input was implicitly FLOAT, the function returned a FLOAT; however, notice that the results did ROUND to two significant digits. The trailing digits were due to the nature of FLOAT not ROUND. Had you CAST your results to NUMERIC, I think you would have gained what you were searching for. The question is whether or not you also needed to ROUND.
SELECT CAST(4 * 1.1 AS NUMERIC(2, 1))
This does no rounding but returns the result 4.4 exactly.
Sincerely,
Anthony Thomas

"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OCSkGp80EHA.3588@.TK2MSFTNGP14.phx.gbl...
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald
|||Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald
|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the point
with a REAL. If this is a problem for you then you shouldn't be using REAL.
David Portas
SQL Server MVP
|||The problem is that the data type you are using is REAL (i.e., FLOAT); it is a FLOATING DECIMAL POINT data type. This means you store a mantissa and an exponent to a significant number of digits. This does not mean you don't carry around additional digits, just that for your precision and scale, you will have the correct result, but anything beyond these bounds and you will end up with extra stuff.
You will get the same behavior in your programming API if you store a value in a SINGLE FLOAT (4 bytes) variable and then covert that variable to a DOUBLE FLOAT (8 bytes).
This is the nature of using floating point arithmetic.
If you need the precision, change your DEPTH variable to a NUMERIC instead of FLOAT.
So you know, this problem has been around far longer than SQL Server, or any RDBMS'. This is the nature of the computer and finite mathematics.
Sincerely,
Anthony Thomas

"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OzR2E090EHA.3364@.TK2MSFTNGP12.phx.gbl...
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>

Rounding a real number

Hello,
it works in a SELECT statement, but not =in an UPDATE statement.
Update dims Set depth=3DCAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald

--=_NextPart_000_0053_01C4D3E5.B0DEA7D0--|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the point
with a REAL. If this is a problem for you then you shouldn't be using REAL.
--
David Portas
SQL Server MVP
--|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>

Rounding a real number

Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
HaraldREAL is an inexact numeric type. If you want more precise results then you
should use a precise type such as NUMERIC. Either change the column or CAST
it. On the other hand if you just want to format the value to a certain
number of decimals that's probably easier to do in your client-side app.
David Portas
SQL Server MVP
--|||Hello,
I understand, that REAL is not an exact numeric type.
But I think this should work (depth is REAL type):
Query Analyzer:
update dims set depth = 4.4
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
Harald|||The ROUND() function returns a number in the data type of the input expressi
on. If NUMERIC, returns NUMERIC; if FLOAT, returns FLOAT. A NUMERIC is an
exact data type where FLOAT is inexact. Since your input was implicitly FLO
AT, the function returned a FLOAT; however, notice that the results did ROUN
D to two significant digits. The trailing digits were due to the nature of
FLOAT not ROUND. Had you CAST your results to NUMERIC, I think you would ha
ve gained what you were searching for. The question is whether or not you a
lso needed to ROUND.
SELECT CAST(4 * 1.1 AS NUMERIC(2, 1))
This does no rounding but returns the result 4.4 exactly.
Sincerely,
Anthony Thomas
--
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OCSkGp80EHA.35
88@.TK2MSFTNGP14.phx.gbl...
Hello,
it seems the ROUND() function does not work on REAL fields.
Example:
update dims
set depth = Round(4*1.1,2)
Query Analyzer:
Select depth from dims
Result: 4.4000001
It works with Decimal(9,2).
But is there no other solution?
Thanks in advance,
Harald|||Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||> it works in a SELECT statement, but not in an UPDATE statement.
If you put CAST(4.4 AS NUMERIC(9,2)) in a SELECT statement you are
outputting a NUMERIC column not a REAL. Again, REAL is NOT exact. You CANNOT
expect to control precisely the value of the digits to the right of the poin
t
with a REAL. If this is a problem for you then you shouldn't be using REAL.
David Portas
SQL Server MVP
--|||The problem is that the data type you are using is REAL (i.e., FLOAT); it is
a FLOATING DECIMAL POINT data type. This means you store a mantissa and an
exponent to a significant number of digits. This does not mean you don't c
arry around additional digits, just that for your precision and scale, you w
ill have the correct result, but anything beyond these bounds and you will e
nd up with extra stuff.
You will get the same behavior in your programming API if you store a value
in a SINGLE FLOAT (4 bytes) variable and then covert that variable to a DOUB
LE FLOAT (8 bytes).
This is the nature of using floating point arithmetic.
If you need the precision, change your DEPTH variable to a NUMERIC instead o
f FLOAT.
So you know, this problem has been around far longer than SQL Server, or any
RDBMS'. This is the nature of the computer and finite mathematics.
Sincerely,
Anthony Thomas
--
"Harald Witt" <hwitt@.novasoftware.de> wrote in message news:OzR2E090EHA.33
64@.TK2MSFTNGP12.phx.gbl...
Hello,
it works in a SELECT statement, but not in an UPDATE statement.
Update dims Set depth=CAST(4.4 AS NUMERIC(9,2))
Select depth from dims
Result: 4.4000001
Any advise?
Thanks in advance,
harald|||Harald,
The data type REAL cannot store the value 4.4. The closest REAL value to
4.4 is what you are seeing. You wouldn't expect an INTEGER column to hold
the value 1.3, and it's the same story here. REAL can only hold certain
numbers, and 4.4 is not one of them.
Steve Kass
Drew University
"Harald Witt" <hwitt@.novasoftware.de> wrote in message
news:OUlBBh90EHA.3416@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I understand, that REAL is not an exact numeric type.
> But I think this should work (depth is REAL type):
> Query Analyzer:
> update dims set depth = 4.4
> Select depth from dims
> Result: 4.4000001
> Any advise?
> Thanks in advance,
> Harald
>

Rounding a number

Hi,

I need to round a number in a table to either 2 or 4 decimals:

=((Sum(Fields!Total.Value) -Sum( Fields!Total_used.Value))*100)/(Sum(Fields!Total.Value)) & " %"

This results in a number like 91,1243656438345%

I would like it to be rounded to 91,1244% or 91,12%

Is there any builtin round-function, that does not just turn the number into an integer?

Thanks for your help

You should be able to do this using the Format function

=format(Sum(Fields!value.Value),"#,###.0000%")

=format(Sum(Fields!value.Value),"#,###.00%")

|||

Thanx - I wasn't familiar with that function. It works perfectly :-)

-- Heidi --

|||You might just need to change your format settings to conform to your use of a comma (,) as opposed to a UK full stop (.) for the decimal separator...|||Yup.... but my reports are running in various contries, so I already use different formatting depending on the users language settings. Not a problem :-)|||I've discovered that the use of comma or dot as seperator is automatic depending og language settings... So in this case I don't need to do anything... finally something is simple and straightforward ;-)

Rounding

I am trying to round values to the nearest $5.
I work in the casino industry and we offer patrons cashback on thier play.
Let's say someone earns $42 cash back. I need to send them an extra check
for 25% rounded to the nearest $5.
$42 * 25% = 10.5 which I need to round to $10
$53 * 25% = 13.25 which I need to round to $15
Similiar to basic rounding...2.49 rounds to 2 and 2.5 rounds to 3 I need
12.49 to round to 10 and 12.50 to round to 15.
Does anyone know how to mathamatically program SQL to do so? Is there a
function that can help out?
Thanks."Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
news:eSEbcu1TGHA.4384@.tk2msftngp13.phx.gbl...
>I am trying to round values to the nearest $5.
> I work in the casino industry and we offer patrons cashback on thier play.
> Let's say someone earns $42 cash back. I need to send them an extra check
> for 25% rounded to the nearest $5.
> $42 * 25% = 10.5 which I need to round to $10
> $53 * 25% = 13.25 which I need to round to $15
> Similiar to basic rounding...2.49 rounds to 2 and 2.5 rounds to 3 I need
> 12.49 to round to 10 and 12.50 to round to 15.
> Does anyone know how to mathamatically program SQL to do so? Is there a
> function that can help out?
> Thanks.
declare @.num1 int
declare @.num2 int
set @.num1 = 42
set @.num2 = 53
select cast((@.num1 * .25)/5 as decimal(5,0)) * 5
select cast((@.num2 * .25)/5 as decimal(5,0)) * 5|||Thanks...I tried multiple scenerios and it worked in all cases.
"Raymond D'Anjou" <rdanjou@.canatradeNOSPAM.com> wrote in message
news:%23e4oa11TGHA.4520@.TK2MSFTNGP10.phx.gbl...
> "Brian Shannon" <brian.shannon@.diamondjo.com> wrote in message
> news:eSEbcu1TGHA.4384@.tk2msftngp13.phx.gbl...
> declare @.num1 int
> declare @.num2 int
> set @.num1 = 42
> set @.num2 = 53
> select cast((@.num1 * .25)/5 as decimal(5,0)) * 5
> select cast((@.num2 * .25)/5 as decimal(5,0)) * 5
>

Rounding

I have a number in a sql db that reads 315000, it is really 3,150.00 and when
I add the field in SSRS it reads 315,000.00.
What function or operator can I use to get the number to read 3,150.00 in
SSRS?
Thanks,
RyanNevermind, this was a dumb question. Hadn't had my coffee yet.
"Ryan Mcbee" wrote:
> I have a number in a sql db that reads 315000, it is really 3,150.00 and when
> I add the field in SSRS it reads 315,000.00.
> What function or operator can I use to get the number to read 3,150.00 in
> SSRS?
> Thanks,
> Ryan

rounding

How would I round the result of col1to the nearest 10th? The value of col1
(which is an average) is 0.423 in this case but I'm getting the resulting
value of ".0" rather than .4. If I use 0.00 as the second argument, I get
".00".
SELECT
avg(COALESCE(col1*1.0,0.0)) as col1,
FROM table1
_____
DC GSomething like this?
select cast(round(0.423, 1) as decimal(12,1))
ML

Rounded Edge for the Tables

I am designing a report in which the edges of the tables should be curved. Is there any way to make the edge of the tables rounded? Could any one suggest for this. I am using MS Reporting service 2003You can try the HTML hack -- putting rounded images in the table corners.|||

Have you tried using images or extended ASCII characters?

cheers,

Andrew

Round() function doesn't seem to work properly in SQL 2k5

Hi,
I have been using the round function in SQL 2000 for quite a while, and when doing sums of percentages have used the Round(99.99,0) function to great avail to get a value of 100.00.

But, when I migrated to SQL 2K5, i used the same function and it gave me an arithmetic overflow error; more specifically:
"An error occurred while executing batch. Error message is: Arithmetic Overflow."

Is anyone aware of any bugs / changes to this function in 2k5 that I am not aware of?

Thanks,
Saurabh

You might also try using CEILING() in conjuction with ROUND(). But be careful if you do...

|||

Yeah, ceiling will work for 99.99, but for the likes of 100.01, then floor is better.

Anyway, does anyone know why the round doesnt work properly in sql 2k5?

|||

I'm not aware of any changes in the underlaying functionality of ROUND().

Could it be that something about the data is causing the overflow?

|||

Hmm...not sure

It was just a simple select round(99.99,-2) that yielded 100.00 in sql 2k and arithmetic overflow error in 2k5

|||

You got me on that one. I definitely see that the results are the same for SLQ 2000 and SQL 2005.

Hopefully, someone from MS will chime in and let us know what is happening...

|||That seems to be a problem with implicit casting.

This works:
select round(CAST(99.99 AS DECIMAL(10,2)),0)

This errors with "An error occurred while executing batch. Error message is: Arithmetic Overflow":

select round(99.99,0)

It looks like a bug to me.

Running 2005 9.0.2153. Anyone running SP2 to see if this fails under it also?|||

Cool, mate!

That works.

I am running 2005 9.0.2047 at the moment, and have that error.

ROUND()

In t-SQL for sql server 2000, the ROUND function has two options
0) normal math rounding (if its less than 5, round down, if its 5 or
greater, round up)
1) round down, reguardless of the number (aka truncate)
Is there any function that will round UP instead of round down? My only
other alternative is to write a UDF, which, in my expierence would kill the
performance of the query.
Thanks,
-- JasonCeiling
select ceiling(12.22)
http://sqlservercode.blogspot.com/|||And it's got a counterpart: FLOOR.
ML
http://milambda.blogspot.com/|||If you care about getting an integer only, use CEILING as suggested by
others. If you care about finer granularities, you can use the following
expression:
ceiling(<value>*power(10, <accuracy> ))/power(10, <accuracy> )
BG, SQL Server MVP
www.SolidQualityLearning.com
www.insidetsql.com
"Jason" <jason@.acd.net> wrote in message
news:ftadnapv46CT5l_eRVn-uA@.giganews.com...
> In t-SQL for sql server 2000, the ROUND function has two options
> 0) normal math rounding (if its less than 5, round down, if its 5 or
> greater, round up)
> 1) round down, reguardless of the number (aka truncate)
> Is there any function that will round UP instead of round down? My only
> other alternative is to write a UDF, which, in my expierence would kill
> the performance of the query.
> Thanks,
> -- Jason
>

Round Up to next integer

Is there a way to round up a value to the next integer?

For example 2.1 needs to be 3 and 4.9 need to be 5.

use ceiling

SELECT CEILING(2.1) AS CeilsTo3, CEILING(4.9) AS CeilsTo5|||Thanks.

round up and round down in sql

I want to do a simple thing but it seems to be behaving not as i am expecting

I want to round number either up or down...

e.g: 4.3 should round to 4

4.7 should round to 5

when i use the round function like this:

83/17=4.88

round( 83/17 , 0 ) it gives the answer as 4...when i was expecting it to be 5...

i know there is a ceiling function...but depending on the value of the division sometimes i want it to round up and sometimes round down.

how can i do this?

hope this makes sense.

thanks

Think ive found the problem is nothing to do with round...

i think83/17 returns a truncated value of 4 not 4.88 as i wanted...

in that case how do i make it return a decimal value?

|||

this did the trick

round(Cast(83 as float)/17)

|||

Assign the value to a variable of type decimal(5,2) or numeric(5,2) not to integer.

Good luck.

round up

X is a float and I need to perform x/10 and round the result up to the integer. So if result is 0.4 -> 1, if 1.1 -> 2.

How can I do this with SQL?

? Use the CIELING function: SELECT CIELING(x/10) FROM YourTable -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <JIM.H.@.discussions.microsoft.com> wrote in message news:4165a8e6-3d64-4db1-9c4b-1cec7040f35a@.discussions.microsoft.com... X is a float and I need to perform x/10 and round the result up to the integer. So if result is 0.4 -> 1, if 1.1 -> 2. How can I do this with SQL?|||

use CEILING

select ceiling(0.4),ceiling(1.1)

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

you can use the round function

For Example:

SELECT round(4.9, 0)

Check out BOL for more details

AWAL

|||

Not really, OP wanted 2 for 1.1

round gives 1

Denis the SQL Menace

http://sqlservercode.blogspot.com/

round up

X is a float and I need to perform x/10 and round the result up to the integer. So if result is 0.4 -> 1, if 1.1 -> 2.

How can I do this with SQL?

SELECTCEILING(X/10)AS numRoundup

FROM yourTable

ROUND unexpected result

/*********** Script 1 **************/
declare @.nr_1 as decimal (10,2)
declare @.nr_2 as decimal (10,2)
set @.nr_1=5
set @.nr_2=3
select round(@.nr_1/@.nr_2,0)

RESULT = 2

/*********** Script 2 **************/

select round(5/3,0)

RESULT = 1

What it is the explication for these difference ?select round(5/3,0), 5/3, 5.0/3.0

Floating point vs integer math.

Round Trips on Drill Down

Can anyone confirm what's happening when a user click the drill down (+)
button to expand or collapse data.
It appears to be doing a round trip, but does not appear to be re-querying
the data. The round trip process is too fast.
I am using OnDemand reports (no cache or snapshot), and the theory says the
OnDemand dataset is only available for the initial rendering.
Is IIS, .net or RS caching the dataset for the expand process to work.
Does anyone have any good doco on what happens where?
Thanks
JasonI can't give you a good document on what is happening, however I can tell
you that it IS making a round trip.
I have a report with sub-reports, the sub-reports are hidden initially. The
user SHOULD click the + and the div tag that hids the sub report would
change status from invisible to visible. That's not what happens.
I know this because I use SOAP to render my reports from a web-server, not
the SQL server. When the user clicks the + it tries to take them to the SQL
server, not back to my site with my code that launched the thing in the
first place. (RATS!)
"Jason Buck" <jason@.REMOVE_THE_CAPScustombizsolutions.com> wrote in message
news:u9hseEVdEHA.3308@.TK2MSFTNGP11.phx.gbl...
> Can anyone confirm what's happening when a user click the drill down (+)
> button to expand or collapse data.
> It appears to be doing a round trip, but does not appear to be re-querying
> the data. The round trip process is too fast.
> I am using OnDemand reports (no cache or snapshot), and the theory says
the
> OnDemand dataset is only available for the initial rendering.
> Is IIS, .net or RS caching the dataset for the expand process to work.
> Does anyone have any good doco on what happens where?
> Thanks
> Jason
>|||Drilldown would indeed cause a post back to the report server but not a new
report execution. On the initial report execution, the report server creates
a session for you and stores a copy of the data in it. Further navigation in
that report, as well as images, drill-downs will work off that session.
--
Tudor Trufinescu
Dev Lead
Sql Server Reporting Services
This posting is provided "AS IS" with no warranties, and confers no rights.
"Jason Buck" <jason@.REMOVE_THE_CAPScustombizsolutions.com> wrote in message
news:u9hseEVdEHA.3308@.TK2MSFTNGP11.phx.gbl...
> Can anyone confirm what's happening when a user click the drill down (+)
> button to expand or collapse data.
> It appears to be doing a round trip, but does not appear to be re-querying
> the data. The round trip process is too fast.
> I am using OnDemand reports (no cache or snapshot), and the theory says
the
> OnDemand dataset is only available for the initial rendering.
> Is IIS, .net or RS caching the dataset for the expand process to work.
> Does anyone have any good doco on what happens where?
> Thanks
> Jason
>|||Thanks,
So is this correct.
1. I view an ondemand report, and it creates the Int. Rep (the data) in the
session variable.
2. This data is marked as available only to my session, with some indicator
that it was for this 'run' of the report
3. When I drill down, it re-renders using the data from above.
4. I hit the refresh button on the report, so (being ondemand) it
regenerates the data and creates a new item in the session object for this
run of the report.
5. Now I have 2 Int Reps, in the 1 session object, and the session object
is not going to be destroy until session timeout.
do the individual data sets get destroyed earlier at some time? Where do I
configure this and the session timeout?
thanks
Jason
"Tudor Trufinescu (MSFT)" <tudortr@.ms.com> wrote in message
news:ut4scTddEHA.2696@.TK2MSFTNGP09.phx.gbl...
> Drilldown would indeed cause a post back to the report server but not a
new
> report execution. On the initial report execution, the report server
creates
> a session for you and stores a copy of the data in it. Further navigation
in
> that report, as well as images, drill-downs will work off that session.
> --
> Tudor Trufinescu
> Dev Lead
> Sql Server Reporting Services
> This posting is provided "AS IS" with no warranties, and confers no
rights.
>
> "Jason Buck" <jason@.REMOVE_THE_CAPScustombizsolutions.com> wrote in
message
> news:u9hseEVdEHA.3308@.TK2MSFTNGP11.phx.gbl...
> > Can anyone confirm what's happening when a user click the drill down (+)
> > button to expand or collapse data.
> >
> > It appears to be doing a round trip, but does not appear to be
re-querying
> > the data. The round trip process is too fast.
> >
> > I am using OnDemand reports (no cache or snapshot), and the theory says
> the
> > OnDemand dataset is only available for the initial rendering.
> >
> > Is IIS, .net or RS caching the dataset for the expand process to work.
> >
> > Does anyone have any good doco on what happens where?
> >
> > Thanks
> >
> > Jason
> >
> >
>

round to nearest WHOLE number

T-SQL:
How to round to the nearest WHOLE number ?
so
from --> to
170 --> 170
96.58 --> 97
thanks
Here are a couple of ways
SELECT ROUND(170,0)
SELECT ROUND(96.58,0)
SELECT CAST(170 AS DECIMAL(10,0))
SELECT CAST(96.58 AS DECIMAL(10,0))

round to nearest WHOLE number

T-SQL:
How to round to the nearest WHOLE number ?

so
from -- to
----
170 --170
96.58 --97

thanksHere are a couple of ways

SELECT ROUND(170,0)
SELECT ROUND(96.58,0)

SELECT CAST(170 AS DECIMAL(10,0))
SELECT CAST(96.58 AS DECIMAL(10,0))

round to nearest WHOLE number

T-SQL:
How to round to the nearest WHOLE number ?
so
from --> to
--
170 --> 170
96.58 --> 97
thanksHere are a couple of ways
SELECT ROUND(170,0)
SELECT ROUND(96.58,0)
SELECT CAST(170 AS DECIMAL(10,0))
SELECT CAST(96.58 AS DECIMAL(10,0))

round to nearest WHOLE number

Round Time To Nearest 15 Miniutes

I wrote a function to round time to the nearest 15 miniute interval.
This functions works fine, I'm just wondering if anyone has a more
efficient\better method of doing this.
CREATE FUNCTION RoundToNearest15
(
@.Date SMALLDATETIME
)
RETURNS SMALLDATETIME
AS
BEGIN
DECLARE @.Miniute TINYINT
DECLARE @.Mult SMALLINT
DECLARE @.Value TINYINT
SET @.Miniute = DATEPART(MI,@.Date)
SET @.Value = 0
SET @.Mult = 1
IF @.Miniute < 8
BEGIN
SET @.Mult = -1
SET @.Value = @.Miniute
END
IF @.Miniute BETWEEN 16 AND 22
BEGIN
SET @.Mult = -1
SET @.Value = @.Miniute - 15
END
IF @.Miniute BETWEEN 31 AND 37
BEGIN
SET @.Mult = -1
SET @.Value = @.Miniute - 30
END
IF @.Miniute BETWEEN 46 AND 52
BEGIN
SET @.Mult = -1
SET @.Value = @.Miniute - 45
END
IF @.Miniute BETWEEN 8 AND 14
SET @.Value = 15 - @.Miniute
IF @.Miniute BETWEEN 23 AND 29
SET @.Value = 30 - @.Miniute
IF @.Miniute BETWEEN 38 AND 44
SET @.Value = 45 - @.Miniute
IF @.Miniute BETWEEN 53 AND 59
SET @.Value = 60 - @.Miniute
RETURN DATEADD(MI,@.Value * @.Mult,@.Date)
ENDOn Feb 21, 9:15 am, "Izzy" <israel.rich...@.gmail.com> wrote:
> I wrote a function to round time to the nearest 15 miniute interval.
> This functions works fine, I'm just wondering if anyone has a more
> efficient\better method of doing this.
> CREATE FUNCTION RoundToNearest15
> (
> @.Date SMALLDATETIME
> )
> RETURNS SMALLDATETIME
> AS
> BEGIN
> DECLARE @.Miniute TINYINT
> DECLARE @.Mult SMALLINT
> DECLARE @.Value TINYINT
> SET @.Miniute = DATEPART(MI,@.Date)
> SET @.Value = 0
> SET @.Mult = 1
> IF @.Miniute < 8
> BEGIN
> SET @.Mult = -1
> SET @.Value = @.Miniute
> END
> IF @.Miniute BETWEEN 16 AND 22
> BEGIN
> SET @.Mult = -1
> SET @.Value = @.Miniute - 15
> END
> IF @.Miniute BETWEEN 31 AND 37
> BEGIN
> SET @.Mult = -1
> SET @.Value = @.Miniute - 30
> END
> IF @.Miniute BETWEEN 46 AND 52
> BEGIN
> SET @.Mult = -1
> SET @.Value = @.Miniute - 45
> END
> IF @.Miniute BETWEEN 8 AND 14
> SET @.Value = 15 - @.Miniute
> IF @.Miniute BETWEEN 23 AND 29
> SET @.Value = 30 - @.Miniute
> IF @.Miniute BETWEEN 38 AND 44
> SET @.Value = 45 - @.Miniute
> IF @.Miniute BETWEEN 53 AND 59
> SET @.Value = 60 - @.Miniute
> RETURN DATEADD(MI,@.Value * @.Mult,@.Date)
> END
How about this:
SELECT DATEADD(mi, ROUND(DATEDIFF(mi, 0, GETDATE()) / 15.0, 0) * 15,
0)|||On Feb 21, 10:18 am, "Tracy McKibben" <tracy.mckib...@.gmail.com>
wrote:
> On Feb 21, 9:15 am, "Izzy" <israel.rich...@.gmail.com> wrote:
>
>
> > I wrote a function to round time to the nearest 15 miniute interval.
> > This functions works fine, I'm just wondering if anyone has a more
> > efficient\better method of doing this.
> > CREATE FUNCTION RoundToNearest15
> > (
> > @.Date SMALLDATETIME
> > )
> > RETURNS SMALLDATETIME
> > AS
> > BEGIN
> > DECLARE @.Miniute TINYINT
> > DECLARE @.Mult SMALLINT
> > DECLARE @.Value TINYINT
> > SET @.Miniute = DATEPART(MI,@.Date)
> > SET @.Value = 0
> > SET @.Mult = 1
> > IF @.Miniute < 8
> > BEGIN
> > SET @.Mult = -1
> > SET @.Value = @.Miniute
> > END
> > IF @.Miniute BETWEEN 16 AND 22
> > BEGIN
> > SET @.Mult = -1
> > SET @.Value = @.Miniute - 15
> > END
> > IF @.Miniute BETWEEN 31 AND 37
> > BEGIN
> > SET @.Mult = -1
> > SET @.Value = @.Miniute - 30
> > END
> > IF @.Miniute BETWEEN 46 AND 52
> > BEGIN
> > SET @.Mult = -1
> > SET @.Value = @.Miniute - 45
> > END
> > IF @.Miniute BETWEEN 8 AND 14
> > SET @.Value = 15 - @.Miniute
> > IF @.Miniute BETWEEN 23 AND 29
> > SET @.Value = 30 - @.Miniute
> > IF @.Miniute BETWEEN 38 AND 44
> > SET @.Value = 45 - @.Miniute
> > IF @.Miniute BETWEEN 53 AND 59
> > SET @.Value = 60 - @.Miniute
> > RETURN DATEADD(MI,@.Value * @.Mult,@.Date)
> > END
> How about this:
> SELECT DATEADD(mi, ROUND(DATEDIFF(mi, 0, GETDATE()) / 15.0, 0) * 15,
> 0)- Hide quoted text -
> - Show quoted text -
Excellent, I knew there had to be an easier way. Thanks!|||"Tracy McKibben" <tracy.mckibben@.gmail.com> wrote in message
news:1172074684.107644.231970@.q2g2000cwa.googlegroups.com...
> On Feb 21, 9:15 am, "Izzy" <israel.rich...@.gmail.com> wrote:
>> I wrote a function to round time to the nearest 15 miniute interval.
>> This functions works fine, I'm just wondering if anyone has a more
>> efficient\better method of doing this.
>> CREATE FUNCTION RoundToNearest15
>> (
>> @.Date SMALLDATETIME
>> )
>> RETURNS SMALLDATETIME
>> AS
>> BEGIN
>> DECLARE @.Miniute TINYINT
>> DECLARE @.Mult SMALLINT
>> DECLARE @.Value TINYINT
>> SET @.Miniute = DATEPART(MI,@.Date)
>> SET @.Value = 0
>> SET @.Mult = 1
>> IF @.Miniute < 8
>> BEGIN
>> SET @.Mult = -1
>> SET @.Value = @.Miniute
>> END
>> IF @.Miniute BETWEEN 16 AND 22
>> BEGIN
>> SET @.Mult = -1
>> SET @.Value = @.Miniute - 15
>> END
>> IF @.Miniute BETWEEN 31 AND 37
>> BEGIN
>> SET @.Mult = -1
>> SET @.Value = @.Miniute - 30
>> END
>> IF @.Miniute BETWEEN 46 AND 52
>> BEGIN
>> SET @.Mult = -1
>> SET @.Value = @.Miniute - 45
>> END
>> IF @.Miniute BETWEEN 8 AND 14
>> SET @.Value = 15 - @.Miniute
>> IF @.Miniute BETWEEN 23 AND 29
>> SET @.Value = 30 - @.Miniute
>> IF @.Miniute BETWEEN 38 AND 44
>> SET @.Value = 45 - @.Miniute
>> IF @.Miniute BETWEEN 53 AND 59
>> SET @.Value = 60 - @.Miniute
>> RETURN DATEADD(MI,@.Value * @.Mult,@.Date)
>> END
> How about this:
> SELECT DATEADD(mi, ROUND(DATEDIFF(mi, 0, GETDATE()) / 15.0, 0) * 15,
> 0)
I tried this on 7:07:45 and it incorrectly returned 7:00 not 7:15.
PS|||On Feb 21, 4:41 pm, "PS" <ecneserpeg...@.hotmail.com> wrote:
> "Tracy McKibben" <tracy.mckib...@.gmail.com> wrote in message
> news:1172074684.107644.231970@.q2g2000cwa.googlegroups.com...
>
> > On Feb 21, 9:15 am, "Izzy" <israel.rich...@.gmail.com> wrote:
> >> I wrote a function to round time to the nearest 15 miniute interval.
> >> This functions works fine, I'm just wondering if anyone has a more
> >> efficient\better method of doing this.
> >> CREATE FUNCTION RoundToNearest15
> >> (
> >> @.Date SMALLDATETIME
> >> )
> >> RETURNS SMALLDATETIME
> >> AS
> >> BEGIN
> >> DECLARE @.Miniute TINYINT
> >> DECLARE @.Mult SMALLINT
> >> DECLARE @.Value TINYINT
> >> SET @.Miniute = DATEPART(MI,@.Date)
> >> SET @.Value = 0
> >> SET @.Mult = 1
> >> IF @.Miniute < 8
> >> BEGIN
> >> SET @.Mult = -1
> >> SET @.Value = @.Miniute
> >> END
> >> IF @.Miniute BETWEEN 16 AND 22
> >> BEGIN
> >> SET @.Mult = -1
> >> SET @.Value = @.Miniute - 15
> >> END
> >> IF @.Miniute BETWEEN 31 AND 37
> >> BEGIN
> >> SET @.Mult = -1
> >> SET @.Value = @.Miniute - 30
> >> END
> >> IF @.Miniute BETWEEN 46 AND 52
> >> BEGIN
> >> SET @.Mult = -1
> >> SET @.Value = @.Miniute - 45
> >> END
> >> IF @.Miniute BETWEEN 8 AND 14
> >> SET @.Value = 15 - @.Miniute
> >> IF @.Miniute BETWEEN 23 AND 29
> >> SET @.Value = 30 - @.Miniute
> >> IF @.Miniute BETWEEN 38 AND 44
> >> SET @.Value = 45 - @.Miniute
> >> IF @.Miniute BETWEEN 53 AND 59
> >> SET @.Value = 60 - @.Miniute
> >> RETURN DATEADD(MI,@.Value * @.Mult,@.Date)
> >> END
> > How about this:
> > SELECT DATEADD(mi, ROUND(DATEDIFF(mi, 0, GETDATE()) / 15.0, 0) * 15,
> > 0)
> I tried this on 7:07:45 and it incorrectly returned 7:00 not 7:15.
> PS
That's because the seconds part of the time value is ignored, I'm only
working with full minutes. You could do something similar with
seconds, but you'll need to DATEDIFF against something other than "0"
or you'll get an overflow error.

Round Statement Incorrect Value

Hello,

I am having trouble getting the correct calculation with the statement below. The error is that QTR4 is being divided by Select SUM instead of all 4 quarters. I have tried closing the addition statements but get errors on all scenarios that Ive tried. How can I format this to correctly to add up all 4 quarters then do the division?

SELECT campus.campus,
ROUND(QTR1+QTR2+QTR3+QTR4/(SELECT SUM(QTR1+qtr2+qtr3+qtr4) FROM campus),2) "% CONT"
FROM campus;

CAMPUS % CONT
-- ----
Main 1300.16
East 700.08
West 300.04
North 350.04What do you mean by "closing the addition statement". Shouldn't you to put parenthesis over QTR1+QTR2+QTR3+QTR4 ?

SELECT campus.campus,
ROUND((QTR1+QTR2+QTR3+QTR4)/(SELECT SUM(QTR1+qtr2+qtr3+qtr4) FROM campus),2) "% CONT"
FROM campus;

Round producing no value

why is this producing nothing when zero?

=Round(((Fields!FeeGoal_IL.Value) / IIf(Fields!FeeSchedule.Value = 0, 1, Fields!FeeSchedule.Value)) * 100)

actually ever since I changed my format to #,### I think that's the problem

figured it out. It is not obvious in BOL (at least what I read on the lousy BOL for SSRS 2005 - lack of thorough content in SSRS 2005 BOL pages) that you can combine N and 0 so N0 did the trick for the format to format numbers with a comma less decimals (e.g. 1,200) and still render 0 for zero values

Round problems in SQL 2000

declare @.valor float
set @.valor = 3695.6300000195000

select @.valor -- prints value without changes
select ROUND(@.valor,2) -- prints 3695.6300000000001

tested in different machines, instances ... etc

its giving me problems in queries where i want to specify
smaller/equal/bigger than zero.

one solution could be : select CAST(@.valor*100 as bigint)
and control the value *100 ...

but i would prefer another method.

anyone...instead of select CAST(@.valor*100 as bigint)
try select CAST(@.valor as decimal(16,2))

Round problem

Hi,
I am using sql statement to save data in SQL SERVER but even i did not apply any round function it is automatically rounding up. e.g. 3.56 when i see it in database it is 4 how can i avoid this rounding? I am using MS Access as front end.
Any help will be highly appreciated.What's the datatype in sql server?

Can you post the DDL of the table?

It'll need to be something like

decimal(15,2), float or real, numeric, money or smallmoney

I would use decimal

Did you ever fix your trigger?

Damn another fluff post...|||Hi Brett,
Here is table. Regarding Trigger , I could not solve it. Can you help me regarding that too?
CREATE TABLE [dbo].[VALID_ITEM] (
[DB_CONTRACT] [decimal](10, 0) NOT NULL ,
[PC_CODE] [int] NOT NULL ,
[ITEM_NO] [varchar] (7) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[NEW_ITEM] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DESCPT] [varchar] (60) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[UNITS] [varchar] (3) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[TYPE_ITEM] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RET_PERC] [decimal](3, 0) NULL ,
[PROJQ] [decimal](11, 3) NULL ,
[CONTQ] [decimal](11, 3) NULL ,
[QTD] [decimal](11, 3) NULL ,
[TAMT_RET_ITEM] [decimal](12, 2) NULL ,
[TAMT_PAID_ITEM] [decimal](12, 2) NULL ,
[CONTR_PRICE] [decimal](12, 3) NULL ,
[IND_OVR_UND] [varchar] (1) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]|||Which column is rounding...but looks like you need precision...

gotta split...I'll check it in the am

Damn...another fluff post on my part|||Hi Brett,

The column [QTD] [decimal](11, 3) NULL is giving problem.

thanks|||You may want to look at how you are saving the data to the table. If you have access to Profiler you may want to run the SQLProfilerTSQL.tdf template and check the SQL statement passed to SQL Server. Chances are that access is rounding the value before passing it to SQL Server.|||hi,

I checked on MS Access side, access is not rounding it. I have profiler how can i run SQLProfilerTSQL.tdf ?

regards|||hi,

any one help me about my problem?

plzzzzzz|||Probably.

Post your SQL procedure code.|||Hi,
Here is the portion which is giving the problem

declare
@.var_vendor integer,
@.var_used_amt integer

set @.var_vendor = (select gen_contr from ae_contract where db_contract=@.var_db_contract)

BEGIN
update vendor
set used_amt = isnull(used_amt,0) + @.var_amt_result + @.var_amt_ret_result where db_vendor = @.var_vendor|||declare
@.var_vendor integer,
@.var_used_amt integer

This portion is doing the rounding. The value that is being rounded should have a data type of decimal with precision.

Also if you open Profiler, there is a list of templates that can show you the activity being sent to the SQL Server.|||hi,

Thank you very much..it worked...

round money data type 2 decimal

How can I execute a query that will round a money data
type field to 2 decimal places. I have:
SELECT
AmountReceipt
FROM
InvoiceReceipt
WHERE
InvoiceID = 'xxxxxx'
I need to return 3099.93 instead of 3099.9300 because I
need to compare it to something else that has already
been rounded out.
TIA,
Vicyou're example may not be the best. The two numbers you use are in fact the
same. How do you want rounding to happen and how did you round the other
number? You could cast the value as decimal with a scale of 2. See Books
Online...
Brian Moran
Principal Mentor
Solid Quality Learning
SQL Server MVP
http://www.solidqualitylearning.com
"Vic" <vduran@.specpro-inc.com> wrote in message
news:12f0201c411fd$547ddfb0$a401280a@.phx
.gbl...
> How can I execute a query that will round a money data
> type field to 2 decimal places. I have:
> SELECT
> AmountReceipt
> FROM
> InvoiceReceipt
> WHERE
> InvoiceID = 'xxxxxx'
> I need to return 3099.93 instead of 3099.9300 because I
> need to compare it to something else that has already
> been rounded out.
> TIA,
> Vic

Round funtion on entire columns in MSSQL?

Hi,

I'd like to round all amounts in a certain column to 2 decimals.
I tried the following query, but eventhough the syntax is correct, it
doesn't give any result:

update gbkmut
set bdr_hfl = round(bdr_hfl,2)

can anyone help me?

cheers,

steveOn 5 May 2004 02:40:15 -0700, steve wrote:

>Hi,
>I'd like to round all amounts in a certain column to 2 decimals.
>I tried the following query, but eventhough the syntax is correct, it
>doesn't give any result:
>update gbkmut
>set bdr_hfl = round(bdr_hfl,2)
>can anyone help me?
>cheers,
>steve

Hi Steve,

What do you mean with "doesn't give any result"?

If you mean that no rows were returned by the update statement, then
this is expected behaviour. An UPDATE-statement will update the data,
nothing more nothing less. Use SELECT if you want to see anything.

If you mean that after executing the above UPDATE, you still have data
with more than two non-zero digits after the decimal point, I'd ask
you to post more details (table definition in the form of CREATE TABLE
statements, sample data in the form of INSERT statements, expected
output and the output you got) so that others can try if they can
reproduce this apparantly erroneous behaviour.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||stefan.van.den.steen@.exact.be (steve) wrote in message news:<79d8a1b0.0405050140.6f442694@.posting.google.com>...
> Hi,
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)
> can anyone help me?
> cheers,
> steve

At first glance, your UPDATE statement seems to be OK. Can you give
some more information? In particular, what is the data type of the
bdr_hfl column, and can you give some sample values, as well as your
expected result? And what does "doesn't give any result" mean?

Simon|||steve (stefan.van.den.steen@.exact.be) writes:
> I'd like to round all amounts in a certain column to 2 decimals.
> I tried the following query, but eventhough the syntax is correct, it
> doesn't give any result:
> update gbkmut
> set bdr_hfl = round(bdr_hfl,2)

There is very little information, but I would guess that your column is
of datatype float. Float is an approxamite datatype, which means that
far from all values can be stored exactly in a float. For instance,
try this:

select convert(float, 1.89)

In Query Analyzer this displays as 1.8899999999999999.

In many situations, it is possible to cope with these extra decimals at
the end; you only need some care. If you need exact numbers, you must
use the decimal type instad. The drawback is that with decimal, you
must decide from the beginning which range you handle.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

round function in sql statement not executing

Hi , I'm new to t-sql so bear with me cos this might be a stupid question.

The code below

SELECT [CODE], [PERIOD], [OPERATOR], [RATE], Round(IIF([OPERATOR]='/',[RATE],1/[RATE]),3) AS EXCHRATE
INTO tbl_currency_test
FROM [SUNDB].[dbo].[SSRFCNV]
WHERE [CODE]='NGN' AND [PERIOD]<>0

always gives me the following error message

Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near '='.

However when i remove the round() code from the statement it executes fine.

Can someone please please let me know what i'm doing wrong here....

Cheers

Hi,

The IIF is for MDX statements. For SQL statements, you need to use the CASE keyword.

SELECT [CODE], [PERIOD], [OPERATOR], [RATE], Round(CASE WHEN [OPERATOR]='/' THEN [RATE] ELSE 1/[RATE] END,3) AS EXCHRATE
INTO tbl_currency_test
FROM [SUNDB].[dbo].[SSRFCNV]
WHERE [CODE]='NGN' AND [PERIOD]<>0

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Here is the definition for the CASE keyword:

http://msdn2.microsoft.com/en-us/library/ms181765.aspx

Greetz,

Geert

Geert Verhoeven
Consultant @. Ausy Belgium

My Personal Blog

|||

Dude,

That was pure ....

Thanks a mil

round function

Can anyone reproduce this with sql 2005?
SELECT ROUND(95.99, -1)
returns:
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
if you try it with anything less than 95 it works. It errors out any
time it is supposed to round up to a number with 1 more digit (i.e. 95
to 100, 996 to 1000).
This worked in sql 2000.
The fix is "select convert(float,round(95.99, -1)"
Very strange.returns 100 for me on
Microsoft SQL Server 2005 - 9.00.2040.00 (Intel X86)
Mar 13 2006 11:20:51
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Returns 100.00 for me on:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Even I get an error
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
My @.@.version returns
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
"clemlau@.yahoo.com" wrote:
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Anything from ROUND(95.99, -1) to ROUND(99.99, -1) gives an error. Strange!
"clemlau@.yahoo.com" wrote:
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||I get the error
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
Here is the version number
Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
So it works on 32 bit, but does not work 64 bit.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||On Thu, 7 Sep 2006 09:11:22 -0400, Hilary Cotter wrote:
>I get the error
>An error occurred while executing batch. Error message is: Arithmetic
>Overflow.
>
>Here is the version number
>Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
>Apr 14 2006 01:11:53
>Copyright (c) 1988-2005 Microsoft Corporation
>Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
>
>So it works on 32 bit, but does not work 64 bit.
Hi Hilary,
It doesn't work on all 32-bit systems, though.
SELECT @.@.VERSION
SELECT ROUND(95.99, -1)
On SQL-2005:
Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86)
May 8 2006 22:41:28
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
An error occurred while executing batch. Error message is: Rekenkundige
overloop.
On SQL-2000:
Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)
Mar 9 2006 11:38:51
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
An error occurred while executing batch. Error message is: Rekenkundige
overloop.
Hugo Kornelis, SQL Server MVP

ROUND function

ROUND(12.5, 0) return 12
How implement in T-SQL (SQL Server 2000) to return 13?SELECT ROUND(12.5, 0)
Returns 13 !
Roji. P. Thomas
Net Asset Management
http://toponewithties.blogspot.com
"Microsoft" <vali_albastroiu@.hotmail.com> wrote in message
news:OhLQppmmFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ROUND(12.5, 0) return 12
> How implement in T-SQL (SQL Server 2000) to return 13?
>|||ROUND(12.5, 0, 1) returns 12
ROUND(12.5, 0, 0) returns 13
"Microsoft" <vali_albastroiu@.hotmail.com> wrote in message
news:OhLQppmmFHA.1416@.TK2MSFTNGP09.phx.gbl...
> ROUND(12.5, 0) return 12
> How implement in T-SQL (SQL Server 2000) to return 13?
>

round function

Can anyone reproduce this with sql 2005?
SELECT ROUND(95.99, -1)
returns:
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
if you try it with anything less than 95 it works. It errors out any
time it is supposed to round up to a number with 1 more digit (i.e. 95
to 100, 996 to 1000).
This worked in sql 2000.
The fix is "select convert(float,round(95.99, -1)"
Very strange.returns 100 for me on
Microsoft SQL Server 2005 - 9.00.2040.00 (Intel X86)
Mar 13 2006 11:20:51
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Returns 100.00 for me on:
Microsoft SQL Server 2000 - 8.00.2039 (Intel X86)
May 3 2005 23:18:38
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
Arnie Rowland, Ph.D.
Westwood Consulting, Inc
Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Even I get an error
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
My @.@.version returns
Microsoft SQL Server 2005 - 9.00.2047.00 (Intel X86)
Apr 14 2006 01:12:25
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Evaluation Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
"clemlau@.yahoo.com" wrote:

> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||Anything from ROUND(95.99, -1) to ROUND(99.99, -1) gives an error. Strange!
"clemlau@.yahoo.com" wrote:

> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||I get the error
An error occurred while executing batch. Error message is: Arithmetic
Overflow.
Here is the version number
Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
Apr 14 2006 01:11:53
Copyright (c) 1988-2005 Microsoft Corporation
Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
So it works on 32 bit, but does not work 64 bit.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
<clemlau@.yahoo.com> wrote in message
news:1157591318.990113.257650@.h48g2000cwc.googlegroups.com...
> Can anyone reproduce this with sql 2005?
> SELECT ROUND(95.99, -1)
> returns:
> An error occurred while executing batch. Error message is: Arithmetic
> Overflow.
> if you try it with anything less than 95 it works. It errors out any
> time it is supposed to round up to a number with 1 more digit (i.e. 95
> to 100, 996 to 1000).
> This worked in sql 2000.
> The fix is "select convert(float,round(95.99, -1)"
> Very strange.
>|||On Thu, 7 Sep 2006 09:11:22 -0400, Hilary Cotter wrote:

>I get the error
>An error occurred while executing batch. Error message is: Arithmetic
>Overflow.
>
>Here is the version number
>Microsoft SQL Server 2005 - 9.00.2047.00 (X64)
>Apr 14 2006 01:11:53
>Copyright (c) 1988-2005 Microsoft Corporation
>Enterprise Edition (64-bit) on Windows NT 5.2 (Build 3790: Service Pack 1)
>
>So it works on 32 bit, but does not work 64 bit.
Hi Hilary,
It doesn't work on all 32-bit systems, though.
SELECT @.@.VERSION
SELECT ROUND(95.99, -1)
On SQL-2005:
Microsoft SQL Server 2005 - 9.00.2153.00 (Intel X86)
May 8 2006 22:41:28
Copyright (c) 1988-2005 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
An error occurred while executing batch. Error message is: Rekenkundige
overloop.
On SQL-2000:
Microsoft SQL Server 2000 - 8.00.2187 (Intel X86)
Mar 9 2006 11:38:51
Copyright (c) 1988-2003 Microsoft Corporation
Developer Edition on Windows NT 5.1 (Build 2600: Service Pack 2)
An error occurred while executing batch. Error message is: Rekenkundige
overloop.
Hugo Kornelis, SQL Server MVP

Round date on a chart

Hello!
I'm collecting performance counters from few servers.
They are collecting every 10 minutes. After that i need to create
day-report.But when i put "processor use" on Y and time on X, on X i've got
a forest of small dates.
I need only fuul hours (8, 10, 12 etc). How can i do this?
I;m thinking about round it, but what syntax is for it?
And where to type it?
LeszekUse Date.Parse
Andy Potter|||I think that there is a problem with only showing that dates.
When i put (under chart) a Computername, then i have information about
processor for each computer (labels with computername exists).
But when i put date there, i see only my text (without dates).
Where can be a problem?
Type conversion?
I just need to display hours...
Uzytkownik "Potter" <drewpotter@.gmail.com> napisal w wiadomosci
news:1137425277.468665.280170@.g14g2000cwa.googlegroups.com...
> Use Date.Parse
> Andy Potter
>|||I'm not really following you. You're concatenating text and a datetime
value? Have you tried doing a .ToString on your date value?
Andy Potter|||If I get you right, you have too many datas for a day and you only want to
"print" a label in the chart every hour, not every 10 minutes. I had a
similar situation way back, and the date/time labels at the bottom of the
charts got very small and unreadable.
My first try was to show only every 6th label by leaving 5 blank and only
show 1, but Reporting Services fully drawn the blank labels over my real ones
which were unreadable as well. Finally I create the average of the 6 values
on the SQL server and used this aggregate for displaying (To catch suspicious
values I also added a min/max value as well).
The SQL looked like:
select min(Value) as minValue, max(Value) as maxValue, avg(Value) as avgValue
, min ( cast(floor(cast(tmDate as float) * 24) / 24 as smalldatetime) ) as
tmDate
from
(
select Value, tmDate from tabMeasures where tmDate between @.tmStart and
@.tmEnd
)
group by datepart(hour, tmDate)
To hide the seconds in the chart I used to modify the label field from
=Fields!tmDate.Value
to
=Fields.tmDate.Value.ToShortTimeString()
"Leszek" wrote:
> Hello!
> I'm collecting performance counters from few servers.
> They are collecting every 10 minutes. After that i need to create
> day-report.But when i put "processor use" on Y and time on X, on X i've got
> a forest of small dates.
> I need only fuul hours (8, 10, 12 etc). How can i do this?
> I;m thinking about round it, but what syntax is for it?
> And where to type it?
> Leszek
>
>|||Now i have different problem:
When i see my report, i see only one date. Other are like 38748, 37568 etc.
It looks like converted date to other type of values?
Sometimes i see two dates and other date values are 38732, 38768 :/
Where could be a problem?
And my other problem - how can i dynamically generate report, where i will
get as much charts, as i have comupters in my database.column?
U¿ytkownik "GreyMana" <GreyMana@.discussions.microsoft.com> napisa³ w
wiadomo¶ci news:D4821781-CE04-4520-9070-4CA661F877AE@.microsoft.com...
> If I get you right, you have too many datas for a day and you only want to
> "print" a label in the chart every hour, not every 10 minutes. I had a
> similar situation way back, and the date/time labels at the bottom of the
> charts got very small and unreadable.
> My first try was to show only every 6th label by leaving 5 blank and only
> show 1, but Reporting Services fully drawn the blank labels over my real
> ones
> which were unreadable as well. Finally I create the average of the 6
> values
> on the SQL server and used this aggregate for displaying (To catch
> suspicious
> values I also added a min/max value as well).
> The SQL looked like:
> select min(Value) as minValue, max(Value) as maxValue, avg(Value) as
> avgValue
> , min ( cast(floor(cast(tmDate as float) * 24) / 24 as smalldatetime) ) as
> tmDate
> from
> (
> select Value, tmDate from tabMeasures where tmDate between @.tmStart and
> @.tmEnd
> )
> group by datepart(hour, tmDate)
> To hide the seconds in the chart I used to modify the label field from
> =Fields!tmDate.Value
> to
> =Fields.tmDate.Value.ToShortTimeString()
>
> "Leszek" wrote:
>> Hello!
>> I'm collecting performance counters from few servers.
>> They are collecting every 10 minutes. After that i need to create
>> day-report.But when i put "processor use" on Y and time on X, on X i've
>> got
>> a forest of small dates.
>> I need only fuul hours (8, 10, 12 etc). How can i do this?
>> I;m thinking about round it, but what syntax is for it?
>> And where to type it?
>> Leszek
>>

round date

Hi All,
Maybe this is easy but I can't find the way to fix this.
I want to get total and number of orders for each date. I use this query...
SELECT cast(o.orderdate as smalldatetime) , sum(od.unitprice), count(*) as
counter
FROM Orders o
INNER JOIN orderdetails od on
o.orderid = od.orderid
group by o.orderdate
ORDER BY o.orderdate desc
Is not working because my orderdate column contains also the time. I try to
round the date but not luck.
how can I fix this?
Any ideas...
Tks in advance
JFBFirst, to fix yr problem, Strip the time off in the expressions in your que
ry
SELECT convert (varchar(8), o.orderdate , 112) OrderDate,
sum(od.unitprice) Total, count(*) counter
FROM Orders o
JOIN orderdetails od
on o.orderid = od.orderid
Group by convert (varchar(8), o.orderdate , 112)
ORDER BY convert (varchar(8), o.orderdate , 112) desc
Next, if you don't have any need for the time portion in this field, (And
never will) consider strippinmg it off and only storing the date in the firs
t
place... or, Storing the date and time portions in separate columns...
"JFB" wrote:

> Hi All,
> Maybe this is easy but I can't find the way to fix this.
> I want to get total and number of orders for each date. I use this query..
.
> SELECT cast(o.orderdate as smalldatetime) , sum(od.unitprice), count(*) as
> counter
> FROM Orders o
> INNER JOIN orderdetails od on
> o.orderid = od.orderid
> group by o.orderdate
> ORDER BY o.orderdate desc
> Is not working because my orderdate column contains also the time. I try t
o
> round the date but not luck.
> how can I fix this?
> Any ideas...
> Tks in advance
> JFB
>
>|||One easy way to drop the time portion from a datetime field is to do a
FLOOR function but only after casting to FLOAT first:
declare @.dtNow datetime
declare @.dtToday datetime
set @.dtNow = GetDate()
set @.dtToday = floor(cast(@.dtNow as float))
select @.dtNow
select @.dtToday
You see that the @.dtNow has the time included but the @.dtToday has been
truncated to midnight.
If you apply this "function" to your data you can compare all datetimes as
the equvilent same day/date
Message posted via http://www.webservertalk.com|||Great... i try almost the same but with 101 format and it didn't give me the
right order.
I appreciate this ... Tks for you help.
JFB
"CBretana" <cbretana@.areteIndNOSPAM.com> wrote in message
news:C862E5FA-B9DB-4F0C-830A-B79F4222BA07@.microsoft.com...
> First, to fix yr problem, Strip the time off in the expressions in your
query
> SELECT convert (varchar(8), o.orderdate , 112) OrderDate,
> sum(od.unitprice) Total, count(*) counter
> FROM Orders o
> JOIN orderdetails od
> on o.orderid = od.orderid
> Group by convert (varchar(8), o.orderdate , 112)
> ORDER BY convert (varchar(8), o.orderdate , 112) desc
> Next, if you don't have any need for the time portion in this field, (And
> never will) consider strippinmg it off and only storing the date in the
first
> place... or, Storing the date and time portions in separate columns...
>
> "JFB" wrote:
>
query...
as
to|||Much faster, and simpler is to cast to Integer, as can be seen by
Select Cast(Cast(getdate() as integer) as dateTime)
"Geoffrey Kahan via webservertalk.com" wrote:

> One easy way to drop the time portion from a datetime field is to do a
> FLOOR function but only after casting to FLOAT first:
> declare @.dtNow datetime
> declare @.dtToday datetime
> set @.dtNow = GetDate()
> set @.dtToday = floor(cast(@.dtNow as float))
> select @.dtNow
> select @.dtToday
> You see that the @.dtNow has the time included but the @.dtToday has been
> truncated to midnight.
> If you apply this "function" to your data you can compare all datetimes as
> the equvilent same day/date
> --
> Message posted via http://www.webservertalk.com
>

round a value to the nearest integer

how do i round a value to the nearest integer?

Quote:

Originally Posted by poopsy

how do i round a value to the nearest integer?

Make use of ROUND()
Check here for detail

Round 2...

I am VERY new to reporting services, so I think this should be an easy one.
I created a simple report that uses a SPROC as the dataset. There are 2
input parameters for the SPROC...
1) a Dealer number
2) a Inventory number.
The dealers are uploading pictures of the inventory items onto my server
into a folder.
The structure of the folder is as follows.
http://mywebsite.com/dealers/DEALER NUMBER/invetorypics/INVENTORY NUMBER.jpg
I am reposting - nobody answered the first time...
I have code so when the dealer uploads a picture of the piece of invetory,
the inventory number becomes the name of the image.
Example... dealer number is 1234 and the inventory number is 5678
The image is saved to this location, with this file name...
http://mywebsite.com/dealers/1234/autopics/5678.jpg
so here is my question... on a report that I run to show a specific piece of
inventory (including the image), how do I dynamically populate the "value"
property of my image control? I say dynamically, because the path of the
'value' property is built using the 2 input parameters (the user is
supplying the parameters under the covers by clicking a row in a grid).
so in the above case, the "value" property needs to be set to
http://mywebsite.com/dealers/1234/autopics/5678.jpg
Thanks in advance!
BrianSet the Image.Value property to this expression:
=http://mywebsite.com/dealers/ & Parameters!DealerNumber.Value &
"/autopics/" & Parameters!InventoryNumber.Value & ".jpg"
(the "http...dealers/" should be in quotes, but OE likes to remove quotes
and make a hyperlink)
--
Floyd
"Brian Cesafsky" <Brian.Cesafsky@.AutoTrackerPlus.com> wrote in message
news:%23d5f7QDnFHA.3544@.TK2MSFTNGP15.phx.gbl...
>I am VERY new to reporting services, so I think this should be an easy one.
> I created a simple report that uses a SPROC as the dataset. There are 2
> input parameters for the SPROC...
> 1) a Dealer number
> 2) a Inventory number.
> The dealers are uploading pictures of the inventory items onto my server
> into a folder.
> The structure of the folder is as follows.
> http://mywebsite.com/dealers/DEALER NUMBER/invetorypics/INVENTORY
> NUMBER.jpg
> I am reposting - nobody answered the first time...
>
> I have code so when the dealer uploads a picture of the piece of invetory,
> the inventory number becomes the name of the image.
> Example... dealer number is 1234 and the inventory number is 5678
> The image is saved to this location, with this file name...
> http://mywebsite.com/dealers/1234/autopics/5678.jpg
>
> so here is my question... on a report that I run to show a specific piece
> of
> inventory (including the image), how do I dynamically populate the "value"
> property of my image control? I say dynamically, because the path of the
> 'value' property is built using the 2 input parameters (the user is
> supplying the parameters under the covers by clicking a row in a grid).
> so in the above case, the "value" property needs to be set to
> http://mywebsite.com/dealers/1234/autopics/5678.jpg
> Thanks in advance!
> Brian
>
>|||I tried this, but it is not working, I don't get the image...
"Floyd Burger" <mrlements@.community.nospam> wrote in message
news:u6SnWhEnFHA.1948@.TK2MSFTNGP12.phx.gbl...
> Set the Image.Value property to this expression:
> =http://mywebsite.com/dealers/ & Parameters!DealerNumber.Value &
> "/autopics/" & Parameters!InventoryNumber.Value & ".jpg"
> (the "http...dealers/" should be in quotes, but OE likes to remove quotes
> and make a hyperlink)
> --
> Floyd
> "Brian Cesafsky" <Brian.Cesafsky@.AutoTrackerPlus.com> wrote in message
> news:%23d5f7QDnFHA.3544@.TK2MSFTNGP15.phx.gbl...
>>I am VERY new to reporting services, so I think this should be an easy
>>one.
>> I created a simple report that uses a SPROC as the dataset. There are 2
>> input parameters for the SPROC...
>> 1) a Dealer number
>> 2) a Inventory number.
>> The dealers are uploading pictures of the inventory items onto my server
>> into a folder.
>> The structure of the folder is as follows.
>> http://mywebsite.com/dealers/DEALER NUMBER/invetorypics/INVENTORY
>> NUMBER.jpg
>> I am reposting - nobody answered the first time...
>>
>> I have code so when the dealer uploads a picture of the piece of
>> invetory,
>> the inventory number becomes the name of the image.
>> Example... dealer number is 1234 and the inventory number is 5678
>> The image is saved to this location, with this file name...
>> http://mywebsite.com/dealers/1234/autopics/5678.jpg
>>
>> so here is my question... on a report that I run to show a specific piece
>> of
>> inventory (including the image), how do I dynamically populate the
>> "value"
>> property of my image control? I say dynamically, because the path of the
>> 'value' property is built using the 2 input parameters (the user is
>> supplying the parameters under the covers by clicking a row in a grid).
>> so in the above case, the "value" property needs to be set to
>> http://mywebsite.com/dealers/1234/autopics/5678.jpg
>> Thanks in advance!
>> Brian
>>
>

Saturday, February 25, 2012

Blog Archive

About Me