rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero after
a index rebuild in sql 2000 ?
Thanks,
Ranga
Ranga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:

> why rowmodctr in sysindexes table shows negative values instead of zero after
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga
|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know what
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For the
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero afte
r
a index rebuild in sql 2000 ?
Thanks,
RangaRanga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:

> why rowmodctr in sysindexes table shows negative values instead of zero af
ter
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know wha
t
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
[vbcol=seagreen]
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For t
he
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:
>sql

rowmodctr

why rowmodctr in sysindexes table shows negative values instead of zero after
a index rebuild in sql 2000 ?
Thanks,
RangaRanga,
The value of [rowmodctr] is increased just for index ID 0 or 1. For the
rest of indexes and statistics, it shows a relative value that has to be
added to the [rowmodctr] of the index 0 or 1 to get the true number of
changed rows for this index.
Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
AMB
"Ranga" wrote:
> why rowmodctr in sysindexes table shows negative values instead of zero after
> a index rebuild in sql 2000 ?
> Thanks,
> Ranga|||Thanks...
I have two tables each has several non clustered indexes...for one of them
I see negative values in the rowmodctr, for the other table i see zero for
rowmodctr...though it is not causing any problems, just curious to know what
is behind this.
I did reindex first, and the value got set to zero, a nightly update
statistics job changed the zero value to a negative number ? Is this what
happenned ?
Ranga
"Alejandro Mesa" wrote:
> Ranga,
> The value of [rowmodctr] is increased just for index ID 0 or 1. For the
> rest of indexes and statistics, it shows a relative value that has to be
> added to the [rowmodctr] of the index 0 or 1 to get the true number of
> changed rows for this index.
> Statistics Used by the Query Optimizer in Microsoft SQL Server 2000
> http://msdn2.microsoft.com/en-us/library/aa902688(SQL.80).aspx
>
> AMB
> "Ranga" wrote:
> > why rowmodctr in sysindexes table shows negative values instead of zero after
> > a index rebuild in sql 2000 ?
> >
> > Thanks,
> > Ranga

Row-Limit per Group

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

:)

Row values to string

Hi,
How can I create a string containing all values from a row in a
table(SELECT).
sample:
ID | NAME
--
1 | John -> "1,John"
2 | Alexander -> "2,Alexander"
Thank you,
Roby Eisenbraun MartinsConvert all the column values to a character compatible type and use the
concatenation operator like:
SELECT CAST( id AS VARCHAR ) + ',' +
CAST( name AS VARCHAR ( 30 ) ) + ',' +
..
FROM tbl ;
Anith|||What do you want to do with the string? You can use DTS to export a table or
view to a comma delimited text file.
"Roby Eisenbraun Martins" <RobyEisenbraunMartins@.discussions.microsoft.com>
wrote in message news:BDF4B30A-70E8-4D3C-8D35-7C095D2C167F@.microsoft.com...
> Hi,
> How can I create a string containing all values from a row in a
> table(SELECT).
> sample:
> ID | NAME
> --
> 1 | John -> "1,John"
> 2 | Alexander -> "2,Alexander"
> Thank you,
> Roby Eisenbraun Martins
>sql

Row offset values

What is the fastest way to select a value offset by n rows from the start row? I used to use a cursor with FETCH ABSOLUTE in Sybase SQLAnywhere, but this is incredibly slow in SQL Server. Here's the current function I'm using:

FUNCTION dbo.TradingDaysBack ( @.ItemID int, @.FromDate smalldatetime, @.DaysBack int )
RETURNS smalldatetime
AS
BEGIN
declare @.BackDay int
declare @.OADay int
set @.OADay = dbo.GetOADate(@.FromDate)
declare curDaysBack cursor scroll for
select OADate
from Data_Daily
where ItemID = @.ItemID and OADate <= @.OADay
order by OADate desc

open curDaysBack
fetch absolute @.DaysBack
from curDaysBack
into @.BackDay

close curDaysBack
deallocate curDaysBack

if @.BackDay is null
begin
set @.BackDay= ( select Min(OADate) from Data_Daily where ItemID = @.ItemID and OADate <= @.OADay )
end

RETURN convert(smalldatetime, @.BackDay)

END

The idea is to get the date n rows of data back from the starting date (i.e. 30 trading days back from 12/1/2003). Any ideas?DATEDIFF?

You know your example is only selecting 1 row....|||It can't be DateDiff, because not every day is a trading day, obviously. I need to go back n trading days, meaning entries for the given ticker between two dates. And yes, it is only selecting one row, which is the idea.sql

Row cannot be located for updating

Hi,

I am getting "Row cannot be located for updating. Some values may have
been changed" error when I try to update from visual basic with ado.
This happens only when set as default locale on the pc other language
than English.
Anybody can help on this

Thanks

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!A M (antmios@.yahoo.com) writes:
> I am getting "Row cannot be located for updating. Some values may have
> been changed" error when I try to update from visual basic with ado.
> This happens only when set as default locale on the pc other language
> than English.
> Anybody can help on this

I can vaguely guess what is going on, but with out a reproducible case
I find it difficult to say anything useful. Can you provide a sample?
That would need to include both the CREATE TABLE statement for the table
and the VB code.

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

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

Rounding Values

Hi,

How can I round a value to the next int number like all values > 1 and < 2 I need to round to 2 and on and on...to all numbers

So If I have 2.1 it's 3 if I have 2.9 it's 3 ...and so on...

Thanks

There is a built-in function: CEILING().|||Thanks.. It worked

Rounding problem with data conversion

Hi,
I am trying to convert a char column so that I can subtract the values from
another column. I have tried cast and convert, to change it to decimal, but
have found that both methods round values to the nearest whole number.
The column contains money, so this is causing me to lose the pence.
Any help would be greatly appreciated.
Many thanks
PaulI've managed to solve this now, so will close the thread.
The convert worked in the end.
Thanks anyway.
"PaulGodfrey" wrote:

> Hi,
> I am trying to convert a char column so that I can subtract the values fro
m
> another column. I have tried cast and convert, to change it to decimal, b
ut
> have found that both methods round values to the nearest whole number.
> The column contains money, so this is causing me to lose the pence.
> Any help would be greatly appreciated.
> Many thanks
> Paul

Rounding datetime values down

Hi,
I have a task that needs to filter large datasets by date, ignoring the
time. However, we do need to store times alongside the dates for other
uses.
The way that had been implemented was to convert to a varchar and then back
again e.g.
convert(datetime, convert(varchar(10), col_name, 103), 103)
However, this results in an unacceptable performance hit.
Is there any better way of achieving this, or am I stuck with having an
extra column which can be populated at the same time (e.g. by a
insert/update trigger)?
John McLuskyJust query the DATETIME column as a range. For example, for today's date:
SELECT ...
FROM YourTable
WHERE col_name >= '20050608'
AND col_name < '20050609' ;
David Portas
SQL Server MVP
--|||David Portas wrote:
> Just query the DATETIME column as a range. For example, for today's
> date:
> SELECT ...
> FROM YourTable
> WHERE col_name >= '20050608'
> AND col_name < '20050609' ;
Hi David,
Thanks for the suggestion. It may be possible to implement inside the
stored procedures that we're using at the moment and I'll look into that
tomorrow when I'm back in the office.
However, if there's a possibility of converting the dates directly (so they
can be used in a view, for example) that would be the best solution.
At the moment, the new column looks like the easiest option!
John.|||Try,
declare @.sd datetime
declare @.ed datetime
set @.sd = '20050101'
set @.ed = '20050131'
select c1, ..., cn
from dbo.t1
where c2 >= @.sd and c2 < dateadd(day, 1, @.ed)
AMB
"JM" wrote:

> Hi,
> I have a task that needs to filter large datasets by date, ignoring the
> time. However, we do need to store times alongside the dates for other
> uses.
> The way that had been implemented was to convert to a varchar and then bac
k
> again e.g.
> convert(datetime, convert(varchar(10), col_name, 103), 103)
> However, this results in an unacceptable performance hit.
> Is there any better way of achieving this, or am I stuck with having an
> extra column which can be populated at the same time (e.g. by a
> insert/update trigger)?
> John McLusky
>
>|||> However, if there's a possibility of converting the dates directly (so
> they can be used in a view, for example) that would be the best solution.
You already have that solution. One possible improvement is to cast to INT
and then back to DATETIME. However, the range query method is better because
it can make full use of an index on the column and avoids unnecessary type
conversions. This can make a BIG difference to performance. Of course you
could consider an indexed view or indexed computed column but that seems
redundant to me if your data can be accessed via an SP.
David Portas
SQL Server MVP
--|||I don't understand. You first post asked for code suggestions how to do this
in a efficient way.
Then you imply that you will have difficulties to implement that. Can you ch
ange your code or not?
If you can, do it the right way.
I can imagine adding a computed column to the table and index that column. W
ould I do that? No. You
would still have to adapt your code for the dirty solution, so better to do
it right up front. :-)
Some elaborations on the subject (showing some options, explaining why IMO D
avid's suggestion is the
ay to go):
http://www.karaszi.com/SQLServer/info_datetime.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"JM" <john@.demon.invalid> wrote in message news:3gontsFdkcgoU1@.individual.net...red">
> David Portas wrote:
> Hi David,
> Thanks for the suggestion. It may be possible to implement inside the sto
red procedures that
> we're using at the moment and I'll look into that tomorrow when I'm back i
n the office.
> However, if there's a possibility of converting the dates directly (so the
y can be used in a view,
> for example) that would be the best solution.
> At the moment, the new column looks like the easiest option!
> John.
>|||The easiest way is rarely the best way.
In this case though, the easiest is the best.
Try David's solution.
Maybe you didn't give enough information on what you are trying to do for
the contributers here to find the best solution.
"JM" <john@.demon.invalid> wrote in message
news:3gontsFdkcgoU1@.individual.net...
> David Portas wrote:
> Hi David,
> Thanks for the suggestion. It may be possible to implement inside the
> stored procedures that we're using at the moment and I'll look into that
> tomorrow when I'm back in the office.
> However, if there's a possibility of converting the dates directly (so
> they can be used in a view, for example) that would be the best solution.
> At the moment, the new column looks like the easiest option!
> John.
>|||Hi all,
I agree, the (truly) best solution would appear to be David's, and I will
see what I can do with our SPs tomorrow.
When I said 'best' before, I really meant easiest from an implementation
point of view - messy, but easy.
Thanks for all the suggestions. I guess I'm surprised that there isn't a
simple 'round to midnight' function that can be used!
John.
Raymond D'Anjou wrote:
> The easiest way is rarely the best way.
> In this case though, the easiest is the best.
> Try David's solution.
> Maybe you didn't give enough information on what you are trying to do
> for the contributers here to find the best solution.
> "JM" <john@.demon.invalid> wrote in message
> news:3gontsFdkcgoU1@.individual.net...

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
>

Rotation of Column Headings in a Grid Report

Is there any way to specify that you want a column heading rotated to 45 or 90 degrees? I have a grid report with single character values in the grid but the column headings are very lengthy so the resulting report is several pages wide. I'd like to be able to rotate the column headings 90 degrees (this is easily done in Excel) to get the report to fit on a single page.Change the textbox WritingMode property to tb-rl. Sorry, no tb-lr yet.