I need to sum() the 3 biggest values from a specific field of each group.
Using
SELECT sum(numbers) FROM table GROUP BY field
operates on every row and LIMIT only restricts the final result. What I need is a way to limit the rows per group.select sum(numbers)
from daTable as X
where ( select count(*)
from daTable
where numbers > X.numbers) < 3|||This only gives a single amount, i.e., the sum of the biggest three from the whole table.
To obtain the sums of the three biggest values from each group:SELECT field, sum(numbers)
FROM daTable as X
WHERE (SELECT count(*)
FROM daTable
WHERE field = X.field AND numbers > X.numbers) < 3
GROUP BY field|||well spotted, peter, you are quite right, i misunderstood the question
:)
Showing posts with label field. Show all posts
Showing posts with label field. Show all posts
Wednesday, March 28, 2012
Rowcount reporting incorrectly...
Why when I double click my table, does the "Rows"
field show 10 records, but when I do a SELECT COUNT(id)
it show me the right amount?
SQL 2000 Enterprise, Windows 2003
I've run DBCC CHECKALLOC, UPDATEUSAGE, DBREPAIR, SHOWCONTIG,
INDEXDEFRAG...etc.
I've never heard of this before.EM caches alot of information. Did you try refreshing the data? Exit
and restart EM and see if the problem still appears.|||DBCC UPDATEUSAGE may improve the accuracy of this number, but it would have
to be executed each time you want to look at the number. Read up on
statistics; how they are used and when they are updated.
"isideveloper" <isideveloper@.newsgroups.nospam> wrote in message
news:uwKyfr4RGHA.776@.TK2MSFTNGP09.phx.gbl...
> Why when I double click my table, does the "Rows"
> field show 10 records, but when I do a SELECT COUNT(id)
> it show me the right amount?
> SQL 2000 Enterprise, Windows 2003
> I've run DBCC CHECKALLOC, UPDATEUSAGE, DBREPAIR, SHOWCONTIG,
> INDEXDEFRAG...etc.
> I've never heard of this before.
>|||... and it has to be executed using the COUNT_ROWS option (which mean it wi
ll take a longer
time...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <someone@.microsoft.com> wrote in message news:e6vBNy4RGHA.5036@.TK2MSFTNGP12.phx.gbl...
> DBCC UPDATEUSAGE may improve the accuracy of this number, but it would hav
e to be executed each
> time you want to look at the number. Read up on statistics; how they are u
sed and when they are
> updated.
>
> "isideveloper" <isideveloper@.newsgroups.nospam> wrote in message
> news:uwKyfr4RGHA.776@.TK2MSFTNGP09.phx.gbl...
>sql
field show 10 records, but when I do a SELECT COUNT(id)
it show me the right amount?
SQL 2000 Enterprise, Windows 2003
I've run DBCC CHECKALLOC, UPDATEUSAGE, DBREPAIR, SHOWCONTIG,
INDEXDEFRAG...etc.
I've never heard of this before.EM caches alot of information. Did you try refreshing the data? Exit
and restart EM and see if the problem still appears.|||DBCC UPDATEUSAGE may improve the accuracy of this number, but it would have
to be executed each time you want to look at the number. Read up on
statistics; how they are used and when they are updated.
"isideveloper" <isideveloper@.newsgroups.nospam> wrote in message
news:uwKyfr4RGHA.776@.TK2MSFTNGP09.phx.gbl...
> Why when I double click my table, does the "Rows"
> field show 10 records, but when I do a SELECT COUNT(id)
> it show me the right amount?
> SQL 2000 Enterprise, Windows 2003
> I've run DBCC CHECKALLOC, UPDATEUSAGE, DBREPAIR, SHOWCONTIG,
> INDEXDEFRAG...etc.
> I've never heard of this before.
>|||... and it has to be executed using the COUNT_ROWS option (which mean it wi
ll take a longer
time...).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JT" <someone@.microsoft.com> wrote in message news:e6vBNy4RGHA.5036@.TK2MSFTNGP12.phx.gbl...
> DBCC UPDATEUSAGE may improve the accuracy of this number, but it would hav
e to be executed each
> time you want to look at the number. Read up on statistics; how they are u
sed and when they are
> updated.
>
> "isideveloper" <isideveloper@.newsgroups.nospam> wrote in message
> news:uwKyfr4RGHA.776@.TK2MSFTNGP09.phx.gbl...
>sql
Monday, March 26, 2012
row with highest column A/B/C/D (was "SQL: is this possible?")
Hi i am wondering if this is possible with SQL:
Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/50/300/250/500
3/50/100/1000/500
Is there any possibility with SQL to select all data from this table where the following criteria are used:
Field A highest ? Yes: take this record
No: Field B highest ? Yes: take this record
No: Field C highest ? Yes: take this record
No: Field D highest ? Yes: take this record
This will result in record 1 to be taken (Field A is highest)
Other data:
Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/100/200/750/500
3/50/100/1000/500
This will result in record 2 to be taken (Field C is higest)
Any help is appreciated, Thanks :)If I understand you correctly, this SQL will return all the rows with the one you want at the top:
select * from mytable order by a desc,b desc,c desc,d desc;
All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.|||if a row (not a record) has 4 columns (not fields), then one of them is going to have the highest value in the row, and so you want that row (not record) to be returned?
sounds like you want all rows returned, because each row will have a highest column value amongst its 4 column values
or do you want values compared amongst all rows at the same time? in that case column C in row 2 does not have the highest value
confused|||If I understand you correctly, this SQL will return all the rows with the one you want at the top:
select * from mytable order by a desc,b desc,c desc,d desc;
All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.
I just started to experiment with this :) This MUST be the solution i was looking for. Thanks :)
Edit: I had to select the LAST record to get the correct record (MS Access). Strange, but it works.
Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/50/300/250/500
3/50/100/1000/500
Is there any possibility with SQL to select all data from this table where the following criteria are used:
Field A highest ? Yes: take this record
No: Field B highest ? Yes: take this record
No: Field C highest ? Yes: take this record
No: Field D highest ? Yes: take this record
This will result in record 1 to be taken (Field A is highest)
Other data:
Record Nr/Field A/Field B/Field C/Field D
1/100/200/500/1000
2/100/200/750/500
3/50/100/1000/500
This will result in record 2 to be taken (Field C is higest)
Any help is appreciated, Thanks :)If I understand you correctly, this SQL will return all the rows with the one you want at the top:
select * from mytable order by a desc,b desc,c desc,d desc;
All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.|||if a row (not a record) has 4 columns (not fields), then one of them is going to have the highest value in the row, and so you want that row (not record) to be returned?
sounds like you want all rows returned, because each row will have a highest column value amongst its 4 column values
or do you want values compared amongst all rows at the same time? in that case column C in row 2 does not have the highest value
confused|||If I understand you correctly, this SQL will return all the rows with the one you want at the top:
select * from mytable order by a desc,b desc,c desc,d desc;
All you then need to do is take just the first record from that result set. How you do that varies from one DBMS to another.
I just started to experiment with this :) This MUST be the solution i was looking for. Thanks :)
Edit: I had to select the LAST record to get the correct record (MS Access). Strange, but it works.
Wednesday, March 21, 2012
row number.
All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. I think I have done this in
the past but am a bit rusty after not using SQL for a while. If
anyone could help with a code snippet this would be gretly
appreciated.Here's an example from the Pubs database:
with each row returned from an SQL query. I think I have done this in
the past but am a bit rusty after not using SQL for a while. If
anyone could help with a code snippet this would be gretly
appreciated.Here's an example from the Pubs database:
SELECT
(SELECT COUNT(*)
FROM Authors
WHERE au_id <= A.au_id) AS rownum
,*
FROM Authors AS A
--
David Portas
SQL Server MVP
--|||
David Mackintosh wrote:
> All I am trying to do is return the row number (calculated field?)
> with each row returned from an SQL query. I think I have done this in
> the past but am a bit rusty after not using SQL for a while. If
> anyone could help with a code snippet this would be gretly
> appreciated.
There are now row numbers.
Zach|||>> All I am trying to do is return the row number (calculated field?)
with each row returned from an SQL query. <<
Since this would have to be for display purposes in the front end, you
ought to be doing in the front, not the database.
--CELKO--
===========================
Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Friday, March 9, 2012
row character-width limit
I recently noticed that SQL Server 2000 has an 8060-character limit on
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!
For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!
|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!
For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!
|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
row character-width limit
I recently noticed that SQL Server 2000 has an 8060-character limit on
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
row character-width limit
I recently noticed that SQL Server 2000 has an 8060-character limit on
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
the total row width, excluding certain field types.
I am curious if there are any newer versions of SQL server that have a
higher or no limit.
I have read this already:
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
I am not interested on how to fix the problem by changing field types.
My co-workers mention an enterprise version of SQL server, but I only
notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
of an "Enterprise" version.
Please let me know if you have any information that might help me.
Thanks!For SQL Server 2005: "The maximum number of bytes per row is 8,060.
This restriction is relaxed for tables with varchar, nvarchar,
varbinary, or sql_variant columns that cause the total defined table
width to exceed 8,060 bytes. The lengths of each one of these columns
must still fall within the limit of 8,000 bytes, but their combined
widths may exceed the 8,060 byte limit in a table."
For a more detailed explanation, see
http://msdn2.microsoft.com/en-us/library/ms186981.aspx
Roy Harvey
Beacon Falls, CT
On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>I recently noticed that SQL Server 2000 has an 8060-character limit on
>the total row width, excluding certain field types.
>I am curious if there are any newer versions of SQL server that have a
>higher or no limit.
>I have read this already:
>http://msdn2.microsoft.com/en-us/library/ms186981.aspx
>I am not interested on how to fix the problem by changing field types.
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
>Please let me know if you have any information that might help me.
>Thanks!|||On 15 Jan 2007 13:36:59 -0800, "Noble Shore" <nobleshore@.gmail.com>
wrote:
>My co-workers mention an enterprise version of SQL server, but I only
>notice 7.0, 2000, and 2005 mentioned on Microsoft's site and no mention
>of an "Enterprise" version.
The Enterprise edition has the same row length limit. For more
information on the different editions see
http://www.microsoft.com/sql/editions/default.mspx
Roy Harvey
Beacon Falls, CT
Row appearance based on field data
I have a simple report that outputs rows of data to a table. The data is real
estate data and shows a bunch of homes in a community. One of the fields is
status. I'd like to have each row in the report with a value of 'Closed' in
the status field to appear with a bold font, while the rest of the rows
appear with a normal font.
Is this possible?
--
ArkayClick on the textbox.
Find teh Font->Weight property in the property sheet.
Set its weight to an expression.
In the expression dialog, enter (replace "myColumn" with your field name)
=IIF(Fields!myColumn.Value = "Closed", "Bold", "Normal")
"Arkay" <Arkay@.discussions.microsoft.com> wrote in message
news:D257735D-CE46-4A17-AD47-94D7CFE5F4C9@.microsoft.com...
>I have a simple report that outputs rows of data to a table. The data is
>real
> estate data and shows a bunch of homes in a community. One of the fields
> is
> status. I'd like to have each row in the report with a value of 'Closed'
> in
> the status field to appear with a bold font, while the rest of the rows
> appear with a normal font.
> Is this possible?
> --
> Arkay
estate data and shows a bunch of homes in a community. One of the fields is
status. I'd like to have each row in the report with a value of 'Closed' in
the status field to appear with a bold font, while the rest of the rows
appear with a normal font.
Is this possible?
--
ArkayClick on the textbox.
Find teh Font->Weight property in the property sheet.
Set its weight to an expression.
In the expression dialog, enter (replace "myColumn" with your field name)
=IIF(Fields!myColumn.Value = "Closed", "Bold", "Normal")
"Arkay" <Arkay@.discussions.microsoft.com> wrote in message
news:D257735D-CE46-4A17-AD47-94D7CFE5F4C9@.microsoft.com...
>I have a simple report that outputs rows of data to a table. The data is
>real
> estate data and shows a bunch of homes in a community. One of the fields
> is
> status. I'd like to have each row in the report with a value of 'Closed'
> in
> the status field to appear with a bold font, while the rest of the rows
> appear with a normal font.
> Is this possible?
> --
> Arkay
Wednesday, March 7, 2012
Rounding seconds up to the nearest 15 minutes
I have a field with seconds in it and I need to disply it in hours which I can do by dividing it by 3600, but I am trying to figure out how to round it up to the nearest 15 minutes. I have tried a couple of things with ROUND and CEILING, but am not getting the right numbers back. Any help would be greatly appreciated.
Hi,You need to divide the seconds by 15 first, round that (up or down as your logic dictates) and then divide by 4 to get hours.
Try this as example:
DECLARE @.secondsint
SET @.seconds = 1632DECLARE @.hoursdecimal(18,2)
SET @.hours = CEILING(convert(decimal,@.seconds)/15 )SELECT @.hours, @.hours/4
The result should be 109, 27.25 (27 and a quarter hours).
I included the two results so you can see what is happening.
Change the seconds to 1640 and the figures are 110, 27.50 (27 and half hours)
Okay, you totally lost me. I only get to dabble with mssql every few months so please excuse my ignorance. I am trying to work what you said into my query, but I when I put in the decimal it throws an error.
SELECT dbo.SLPTRANS.ClientID,SUM(dbo.SLPTRANS.TransValue)AS Expr1,SUM(CEILING(dbo.SLPTRANS.TimeSpent / 15) * dbo.SLPTRANS.RateValue)AS BillableFeesFROM dbo.SLPTRANSINNERJOIN dbo.INVOICEON dbo.SLPTRANS.InvoiceID = dbo.INVOICE.RecordIDGROUP BY dbo.SLPTRANS.ClientIDHAVING (dbo.SLPTRANS.ClientID = 405)|||
Hi,
Apologies for the late reply!
I think the SUM should be:
SUM( (CEILING(db.SLPTRANS.TimeSpent /15)/4 ) * dbo.SLPTRANS.RateValue)
What error message do you get?
Rounding problem
I have a table with a field called "Sell" which is:
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Rounding problem
I have a table with a field called "Sell" which is:
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!
I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!
I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Rounding problem
I have a table with a field called "Sell" which is:
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Decimal 9, precision=10, scale=2
When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
Needless to say, this drives accountants nutty. Changing the data type isn't
an option since this a commercial software product we're using (the
developers know about the problem, but choose to let their reports be off by
pennies). We'd like an accurate Sum() amount, and we've tried doing
Round(@.SumSell,2) but that doesn't work.
Any ideas? THanks!I've been messing with this a little to try to duplicate your problem, but
can not..
Can you post a create table, and insert statements, and a select statement
which repros the problem?
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Dean J Garrett" <info@.amuletc.com> wrote in message
news:%23GaANOiXEHA.2844@.TK2MSFTNGP11.phx.gbl...
> I have a table with a field called "Sell" which is:
> Decimal 9, precision=10, scale=2
> When I do: Select sum(sell) from Table, I get the answer: 2,568.78, but
> when I add up the numbers on a calculator I get: 2,568.72, off by 6 cents.
> Needless to say, this drives accountants nutty. Changing the data type
isn't
> an option since this a commercial software product we're using (the
> developers know about the problem, but choose to let their reports be off
by
> pennies). We'd like an accurate Sum() amount, and we've tried doing
> Round(@.SumSell,2) but that doesn't work.
> Any ideas? THanks!
>
Rounding issue in calculated field using round (,1) function
I have a couple of calculated fields in a BI Dev Studio Report as follows:
1. round(Fields!CM_Perf_1st_Mth.Value, 1)
2. round(Fields!CM_Perf_1st_Mth.Value, 1) - round(Fields!BM_Perf_1st_Mth.Value, 1)
The first calc field above is returning wrong results i.e. for a value of 2.25, instead of returning 2.3, it is returning 2.2. Similarly for -0.05, it is returning 0.0, instead of -0.1.
Since the results from the first function are wrong, the second function is also returning off values.
Has anyone faced this issue? How does one get around this? I have SQL 2005 Reporting Services with SP1. The result was the same without SP1 also. Seems like a big bug in the round function...
TIA.
Had similiar issues and went with doing rounding functions in a custom code function.
Try using the functions there, you will see a difference.
Daryl
|||The dataset is coming from a SQL Server 2000 database stored proc.|||doesn't matter where the dataset is coming from. use the =Code.myroundfunction in the field, and pass the field to the code function.
IE:
field1
if you had "=round(field!myfield.value)"
change it to =code.myroundfunction(field!myfield.value)
|||what does the raw sql data look like?|||Wrote a custom code fx as:
Public Function MyRound(byVal x as decimal, byval y as int16) as decimal
return round(x,y)
end function
Calling this from the calculated field expression does not make a difference at all.
And here's the raw data:
Composite_Code LU_Weight_Type_Code Perf_Date LU_Data_Stage Perf_Gross
Composite_Code LU_Weight_Type_Code Perf_Date LU_Data_Stage Perf_Gross
Any other ideas would be greatly appreciated as I am stuck on this and the deliverable is long due and this is the only issue left... TIA.
|||Here is a link to another forum which should help you out.
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=315706&SiteId=1
Daryl
|||This function works for me.
Public Function MyRound(ByVal x As Decimal, ByVal y As Integer) As Decimal
Return Round(x, y, MidpointRounding.AwayFromZero)
End Function
the definition for round is really confusing.
Daryl
Saturday, February 25, 2012
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
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
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
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
Tuesday, February 21, 2012
Rotate fields
Hi folks,
Is there a way to rotate a field 90 degrees?
Thank's in advance,
Staffanyes check out the properties of the field there ll b a property like
DIRECTION (exactly name is not remebered) but that property helps
adjusting direction of the text in the textbox like left to right,right
to left, top to bottom etc check it ou|||Is there any documentation on the syntax for this?
I only see two options:
lr-tb
tb-lr
It seems that I am rotating the text Ok, but I just need it to be turned
180% degree the other way. Are there any docs on this? I've scoured the
forum but can't find any topics already on this.
Thank!
"** Spirits **" wrote:
> yes check out the properties of the field there ll b a property like
> DIRECTION (exactly name is not remebered) but that property helps
> adjusting direction of the text in the textbox like left to right,right
> to left, top to bottom etc check it ou
>|||as per my knowledge only these two possibilities lr-tb n tb-lr r
possible if u get anything else plz let me know as well
Is there a way to rotate a field 90 degrees?
Thank's in advance,
Staffanyes check out the properties of the field there ll b a property like
DIRECTION (exactly name is not remebered) but that property helps
adjusting direction of the text in the textbox like left to right,right
to left, top to bottom etc check it ou|||Is there any documentation on the syntax for this?
I only see two options:
lr-tb
tb-lr
It seems that I am rotating the text Ok, but I just need it to be turned
180% degree the other way. Are there any docs on this? I've scoured the
forum but can't find any topics already on this.
Thank!
"** Spirits **" wrote:
> yes check out the properties of the field there ll b a property like
> DIRECTION (exactly name is not remebered) but that property helps
> adjusting direction of the text in the textbox like left to right,right
> to left, top to bottom etc check it ou
>|||as per my knowledge only these two possibilities lr-tb n tb-lr r
possible if u get anything else plz let me know as well
Subscribe to:
Posts (Atom)