How to Format the Number for Total

Hai Friends,

I am using Crystal Report 9.2.
In my project, i am in need of finding the Subtotal, totals.

While doing so, its assigning the value as 2,234,456.00

i am in need of that display to be 22,34,456.00.

i tried for Formula Field & also Selection Formula's Group in Crystal Report.
In the Format Field, i am not able to customize for thousand.

Can any one help me to solve my problem for finding the total.Interesting question. I don't think you can do this easily. You might have to create a new formula field and use string manipulation.|||Hai Babu - Moderator,

Are u there? Why no reply from u?

Regards
Geetha|||hai Moderator,

What happened to u?
why u r not responding.
in crystal report as well as in access, we have this type of display for the totals.

rectify this.

Regards
geetha|||Convert the money to text and seperate the digits according to the format you want and combine them by ","

How to format KPI values in KPI definition?

Hi, experts,

How can we format KPI values in the KPI value expression? (e.g. format KPI_Name with format of 2 decimal places?).

Hope it is clear for your help. I am looking forward to hearing from you shortly.

Thanks a lot in advance.

With kindest regards,

Yours sincerely,

Hi Helen! I do not think you can format KPI. You can use a calculated member as the value expression /source for the KPI and format it instead.

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks for your help. I also found a strange problem that when I build the report with KPI values displayed in SSRS 2005, the values are with many decimal places instead of the formats I have already set up in the Analysis services cubes? (e.g, I formated the KPI values by using a calculated member in calculations with 2 decimal places, but the KPI values displayed on SSRS2005 are with many decimal places? e.g. 2.455879..?)

I have no idea what is going on. I am looking forward to hearing from you.

With kindest regards,

Yours sincerely,

|||

Hi Helen! I have seen the same behaviour in SSRS2005. You are aware of that Reporting Services do not show SSAS2005 KPI status and trends graphically? You will only see numbers.

SSRS2005 do not seem to use the formats from relational sources nore SSAS2005. I think you will have to apply formats once again in SSRS2005 like "### ####,##"

HTH

Thomas Ivarsson

|||

Hi, Thomas,

Thanks very much for your patient advices and help.

Yes, I am aware of this problem that SSRS2005 is not able to show KPI status and trend graphically.

With kindest regards,

Yours sincerely,

How to format a part of a textbox

Hey all,

Does anyone know a way to format part of a textbox ? I need just the column value to be bold.

Like this:

"Just a static text Some BOLD Text other static text..."

My expression:

="Just a static text " + Fields!FieldName.Value + " other static text..."

Is there a way to apply formatting just to Fields!FieldName.Value ?

Thanks!

myLabel.Text ="hello " +"<b>" + Fields!FieldName.Value + "</b>" +" not bold";

the above will work just fine for you (in VB.net)

|||

U can also apply CSSClass via <SPAN></SPAN>

|||

The above suggestions can't work because it's aReporting Services textbox and not just a regular asp.net textbox

|||

Well, you may not like this suggestion...

how about having three text boxes. Set the middle textbox to be bold.

|||

looks like thats the only option... i know u can type in alt+89 will give u Y but i couldnt find something that will make something bold... if u can put latin characters in then u can some those keystrokes.

Hope this Helps.

Regards

Karen

|||

Hi,

Thanks for the suggesion :)

I have tried it. It's also not an option, when I place 3 textboxes I have an annoying spaces between them and an alignment problem.

I suppose I'll have to leave it as normal text and to give up the "bold idea" :(

|||

Hi,

From your description, you want to format a part of contents in textbox of reporting service, right?

Unfortunately, Currently, Reporting Services doesn't support rendering "HTML as HTML" in order to avoid the HTML injection attack.

Now I just find two possible workarounds for your from russch's blog.

Post-processing: After a report has been fully rendered, intercept the document and re-process it, turning the HTML (displayed as a string) into HTML which is "really" displayed.

Custom Control (2005 only): One could theoretically build a custom control in 2005 which takes the HTML/RTF, saves the rendered output as an image, and then displays the image inside the custom report item. This looks really hard as the managed GDI namespace doesn't give us anything to easily approach this sort of scenario.

Quoted from Russch's blog.
http://blogs.msdn.com/bimusings/archive/2005/12/14/503648.aspx

Thanks.

|||

Thank you very much!!!

I'll try this.

sql

How to format a number field..

i have a number field but i can't seem to make a format out of it..
e.g.
when i have a value of 1, i want to make it appear in the report like 0000000001 or if i have 11, i want to make it appear like 0000000011
Hope someone could help!
thanksHello DarylAps,

You have two options :

1. While selecting records from table itself use replicate function (assuming you are using mssql server ) and return formatted number.
or
else

2. Create a formula as shown below : assuming your numeric field name is col :

ReplicateString('0', 10-Length ({SqlCommand.col} ) )+{SqlCommand.col}

Here are the details of ReplicateString functions of CR :

ReplicateString (str, #copies)
Basic and Crystal syntax.

Arguments
str is the text string to be replicated.
#copies is a whole number indicating the number of times str is to be replicated.

I hope you like this resolution.

Thanks
Dilemma

How to format a datetime field?

Hi,
I have a datetime field SDate with a value '1/1/02'. I want to display it
as 01/01/2002 in a view. So I use this statement:
SELECT CONVERT(datetime, SDate,101) from Table1.
But it still display it as 1/1/02. The Help says 101 will display yyyy if I
use it with CONVERT.
Thanks.declare @.t datetime
set @.t=getdate()
select convert(char(10),@.t,101)
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:OYpFl5yUFHA.548@.tk2msftngp13.phx.gbl...
> Hi,
> I have a datetime field SDate with a value '1/1/02'. I want to display it
> as 01/01/2002 in a view. So I use this statement:
> SELECT CONVERT(datetime, SDate,101) from Table1.
> But it still display it as 1/1/02. The Help says 101 will display yyyy if
> I use it with CONVERT.
> Thanks.
>
>|||Thanks a lot. I replaced datetime with char(10) and it works.
"Farmer" <someone@.somewhere.com> wrote in message
news:eRMn27yUFHA.548@.tk2msftngp13.phx.gbl...
> declare @.t datetime
> set @.t=getdate()
> select convert(char(10),@.t,101)
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:OYpFl5yUFHA.548@.tk2msftngp13.phx.gbl...
>|||Note of course that when you format it this way that it is no longer a date
value, it is a character value. May not be a problem for you, but it could
be confusing in how it gets used by a client program. and if you want to
sort by it, since it will sort on month first (assuming you are American!)
then day, then year.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
news:%23XAycCzUFHA.1552@.TK2MSFTNGP10.phx.gbl...
> Thanks a lot. I replaced datetime with char(10) and it works.
> "Farmer" <someone@.somewhere.com> wrote in message
> news:eRMn27yUFHA.548@.tk2msftngp13.phx.gbl...
>|||I think the best way, on the backend and on the client application is to
store the value in ISO format, so you need not mess up with cutting the
time, formatting from one pattern to another.
Just my two pence and experience within projects.
Jens Suessmeyer.
http://www.sqlserver2005.de
--
"Louis Davidson" <dr_dontspamme_sql@.hotmail.com> schrieb im Newsbeitrag
news:OihPLOzUFHA.3176@.TK2MSFTNGP12.phx.gbl...
> Note of course that when you format it this way that it is no longer a
> date value, it is a character value. May not be a problem for you, but it
> could be confusing in how it gets used by a client program. and if you
> want to sort by it, since it will sort on month first (assuming you are
> American!) then day, then year.
> --
> ----
--
> Louis Davidson - http://spaces.msn.com/members/drsql/
> SQL Server MVP
>
> "Chrissi" <anubisofthydeath@.hotmail.com> wrote in message
> news:%23XAycCzUFHA.1552@.TK2MSFTNGP10.phx.gbl...
>

How to for date is not null or nothing condition

How can i write a condition for like isdate(Fields!DueDate.value)

I tried isdate but i am getting an error message. what is the equivalent for isdate in sql server reporting services formula fields or calculated fields expression.

Thank you very much for the information.

*********************************************

=IIF(isdate(Fields!DueDate.Value), ................

********************************************

Reddy,

Try

=IIf(IsDate(Fields!DueDate.value),"True","false")

This should work for you.

Ham

|||

Sorry should have read it all.

=IIf(Fields!DueDate.value is nothing,"False",IsDate(Fields!DueDate.value),"True","false"))

Ham

|||

There was a program error, I left out the next IIF statement

IIf(Fields!DueDate.value is nothing,"False",IIf(IsDate(Fields!DueDate.value),"True","false"))

How to find transposed data and near misses

I would like some advice on a data and query problem I face. I have a
data table with a "raw key" value which is not guaranteed to be valid
at its source. Normally, this value will be 9 numeric digits and map to
a "names" table where the entity is given assigned an "official name".

My problem is that I'd like to be able to identify data values that are
"close" to being "correct". For example, in the case of a
nine digit number such as 077467881, I'd like to be able to identify
rows with values close to this raw string. That is, if
there were a row with a value for this column that was "off" by say, a
transposed single digit (such as 077647881 in this example)
I would like to find a query to locate the "close candidates" in a
result set. If I can find rows having a raw key
value that is close to a "good key" then I can allow my user to use
other criteria to possibly assign the "close key" as
an alternate or alias of the official key. Here is part of my schema:

CREATE TABLE MYData (
StateCD char (2) NOT NULL ,
CountyCD char (3) NOT NULL ,
MYID int NULL ,
RawNumString varchar(9) NULL ,
SaleMnYear datetime NOT NULL ,
NumberWidgets int NOT NULL ,
)

CREATE TABLE MYNames (
MYID int IDENTITY (1, 1) NOT NULL ,
OfficialName varchar (70) NOT NULL ,
CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED
(
MYID
)
)
CREATE TABLE MYAltID (
RawNumString varchar (9) NOT NULL ,
MYID int NOT NULL ,
CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED
(
RawNumString
) ,
CONSTRAINT FK_HasName FOREIGN KEY
(
MYID
) REFERENCES MYNames (
MYID
)
)
So, how to generalize something like:
SELECT * FROM MYData WHERE RawNumString = '077467881'
OR RawNumString = '077647881'For what it's worth...

The LIKE operator can perform several forms of wildcard comparisons against
2 strings. For example:

if '90120' like '9_120' print 'Yes' else print 'No'
if '90120' like '9012[0..9]' print 'Yes' else print 'No'
if '90120' like '*0120' print 'Yes' else print 'No'

Yes
Yes
Yes

The SoundEx function returns a checksum for a character string, but not
numbers. It basically disregards vowels and double letters and returns a 4
char result. For example:

print soundex('Robert')
print soundex('Roberto')
print soundex('Rabertie')
print soundex('Rabbit')
print soundex('Rob')

R163
R163
R163
R130
R100

These can be included in a where clause. For example:
SELECT * FROM MYData WHERE RawNumString like '*7746*'
SELECT * FROM MYData WHERE SoundEx(RawName) = SoundEx('Francesco')

Keep in mind that performing like or soundex comparisons do not take
advantage of indexes, so performance could be a problem on a large table.

"JJA" <johna@.cbmiweb.com> wrote in message
news:1117641848.807351.148700@.g47g2000cwa.googlegr oups.com...
> I would like some advice on a data and query problem I face. I have a
> data table with a "raw key" value which is not guaranteed to be valid
> at its source. Normally, this value will be 9 numeric digits and map to
> a "names" table where the entity is given assigned an "official name".
> My problem is that I'd like to be able to identify data values that are
> "close" to being "correct". For example, in the case of a
> nine digit number such as 077467881, I'd like to be able to identify
> rows with values close to this raw string. That is, if
> there were a row with a value for this column that was "off" by say, a
> transposed single digit (such as 077647881 in this example)
> I would like to find a query to locate the "close candidates" in a
> result set. If I can find rows having a raw key
> value that is close to a "good key" then I can allow my user to use
> other criteria to possibly assign the "close key" as
> an alternate or alias of the official key. Here is part of my schema:
> CREATE TABLE MYData (
> StateCD char (2) NOT NULL ,
> CountyCD char (3) NOT NULL ,
> MYID int NULL ,
> RawNumString varchar(9) NULL ,
> SaleMnYear datetime NOT NULL ,
> NumberWidgets int NOT NULL ,
> )
> CREATE TABLE MYNames (
> MYID int IDENTITY (1, 1) NOT NULL ,
> OfficialName varchar (70) NOT NULL ,
> CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED
> (
> MYID
> )
> )
> CREATE TABLE MYAltID (
> RawNumString varchar (9) NOT NULL ,
> MYID int NOT NULL ,
> CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED
> (
> RawNumString
> ) ,
> CONSTRAINT FK_HasName FOREIGN KEY
> (
> MYID
> ) REFERENCES MYNames (
> MYID
> )
> )
> So, how to generalize something like:
> SELECT * FROM MYData WHERE RawNumString = '077467881'
> OR RawNumString = '077647881'|||[posted and mailed, please reply in ews]

JJA (johna@.cbmiweb.com) writes:
> I would like some advice on a data and query problem I face. I have a
> data table with a "raw key" value which is not guaranteed to be valid
> at its source. Normally, this value will be 9 numeric digits and map to
> a "names" table where the entity is given assigned an "official name".
> My problem is that I'd like to be able to identify data values that are
> "close" to being "correct". For example, in the case of a
> nine digit number such as 077467881, I'd like to be able to identify
> rows with values close to this raw string. That is, if
> there were a row with a value for this column that was "off" by say, a
> transposed single digit (such as 077647881 in this example)
> I would like to find a query to locate the "close candidates" in a
> result set. If I can find rows having a raw key
> value that is close to a "good key" then I can allow my user to use
> other criteria to possibly assign the "close key" as
> an alternate or alias of the official key. Here is part of my schema:

Fuzzy logic is not for the faint of heart, and it's definitely not my
area of expertise.

Assuming that you always have nine digits, one approach is compare
character by character and if 7 or more match, count this as a possible
match:

SELECT *
FROM tbl
WHERE CASE WHEN substring(col, 1, 1) = substring(@.val, 1, 1)
THEN 1 ELSE 0
END +
CASE WHEN substring(col, 2, 1) = substring(@.val, 2, 1)
THEN 1 ELSE 0
END +
...
CASE WHEN substring(col, 9, 1) = substring(@.val, 9, 1)
THEN 1 ELSE 0
END >= 7

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Have you ever worked with check digits before? They can prevent errors
in data entry instead of trying to patch them after the fact. The idea
of keeping an invalid key does not sound like a good design.|||Yes, I know this is not good design but we are getting a raw data file
from another organization and we have no control over their practices.
Most occurrences of this number are "valid" but it is clear from
looking at the data that there is no validation at the source. The
nature of the data is such that if we can identify 7 or 8 bytes of data
as being the same as another 9 byte and valid "key", we could assume
the key could be improved to point at the same 9 byte valid entity. So,
I thought I'd run this notion past the world of experts for some ideas.|||Thanks very much for this neat suggestion. It is exactly what I hoped
for and I can implement this a stored procedure with a couple of
parameters. I will provide a little interface where the analyst can
launch the sproc and see if there are any "near-misses". Very cool
application of the CASE facility. Thanks again.|||JJA (johna@.cbmiweb.com) writes:
> Thanks very much for this neat suggestion. It is exactly what I hoped
> for and I can implement this a stored procedure with a couple of
> parameters. I will provide a little interface where the analyst can
> launch the sproc and see if there are any "near-misses". Very cool
> application of the CASE facility. Thanks again.

Glad to hear that the idea was useful to use. Whether it suffices remains
to see. As I said that fuzzy-logic stuff is horrible.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

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

How to find transposed data and near misses

I would like some advice on a data and query problem I face. I have a
data table with a "raw key" value which is not guaranteed to be valid
at its source. Normally, this value will be 9 numeric digits and map to
a "names" table where the entity is given assigned an "official name".
My problem is that I'd like to be able to identify data values that are
"close" to being "correct". For example, in the case of a
nine digit number such as 077467881, I'd like to be able to identify
rows with values close to this raw string. That is, if
there were a row with a value for this column that was "off" by say, a
transposed single digit (such as 077647881 in this example)
I would like to find a query to locate the "close candidates" in a
result set. If I can find rows having a raw key
value that is close to a "good key" then I can allow my user to use
other criteria to possibly assign the "close key" as
an alternate or alias of the official key. Here is part of my schema:
CREATE TABLE MYData (
StateCD char (2) NOT NULL ,
CountyCD char (3) NOT NULL ,
MYID int NULL ,
RawNumString varchar(9) NULL ,
SaleMnYear datetime NOT NULL ,
NumberWidgets int NOT NULL ,
)
CREATE TABLE MYNames (
MYID int IDENTITY (1, 1) NOT NULL ,
OfficialName varchar (70) NOT NULL ,
CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED
(
MYID
)
)
CREATE TABLE MYAltID (
RawNumString varchar (9) NOT NULL ,
MYID int NOT NULL ,
CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED
(
RawNumString
) ,
CONSTRAINT FK_HasName FOREIGN KEY
(
MYID
) REFERENCES MYNames (
MYID
)
)
So, how to generalize something like:
SELECT * FROM MYData WHERE RawNumString = '077467881'
OR RawNumString = '077647881'For what it's worth...
The LIKE operator can perform several forms of wildcard comparisons against
2 strings. For example:
if '90120' like '9_120' print 'Yes' else print 'No'
if '90120' like '9012[0..9]' print 'Yes' else print 'No'
if '90120' like '*0120' print 'Yes' else print 'No'
Yes
Yes
Yes
The SoundEx function returns a checksum for a character string, but not
numbers. It basically disregards vowels and double letters and returns a 4
char result. For example:
print soundex('Robert')
print soundex('Roberto')
print soundex('Rabertie')
print soundex('Rabbit')
print soundex('Rob')
R163
R163
R163
R130
R100
These can be included in a where clause. For example:
SELECT * FROM MYData WHERE RawNumString like '*7746*'
SELECT * FROM MYData WHERE SoundEx(RawName) = SoundEx('Francesco')
Keep in mind that performing like or soundex comparisons do not take
advantage of indexes, so performance could be a problem on a large table.
"JJA" <johna@.cbmiweb.com> wrote in message
news:1117641848.807351.148700@.g47g2000cwa.googlegroups.com...
> I would like some advice on a data and query problem I face. I have a
> data table with a "raw key" value which is not guaranteed to be valid
> at its source. Normally, this value will be 9 numeric digits and map to
> a "names" table where the entity is given assigned an "official name".
> My problem is that I'd like to be able to identify data values that are
> "close" to being "correct". For example, in the case of a
> nine digit number such as 077467881, I'd like to be able to identify
> rows with values close to this raw string. That is, if
> there were a row with a value for this column that was "off" by say, a
> transposed single digit (such as 077647881 in this example)
> I would like to find a query to locate the "close candidates" in a
> result set. If I can find rows having a raw key
> value that is close to a "good key" then I can allow my user to use
> other criteria to possibly assign the "close key" as
> an alternate or alias of the official key. Here is part of my schema:
> CREATE TABLE MYData (
> StateCD char (2) NOT NULL ,
> CountyCD char (3) NOT NULL ,
> MYID int NULL ,
> RawNumString varchar(9) NULL ,
> SaleMnYear datetime NOT NULL ,
> NumberWidgets int NOT NULL ,
> )
> CREATE TABLE MYNames (
> MYID int IDENTITY (1, 1) NOT NULL ,
> OfficialName varchar (70) NOT NULL ,
> CONSTRAINT PK_MYNames PRIMARY KEY CLUSTERED
> (
> MYID
> )
> )
> CREATE TABLE MYAltID (
> RawNumString varchar (9) NOT NULL ,
> MYID int NOT NULL ,
> CONSTRAINT PK_MYALTID PRIMARY KEY CLUSTERED
> (
> RawNumString
> ) ,
> CONSTRAINT FK_HasName FOREIGN KEY
> (
> MYID
> ) REFERENCES MYNames (
> MYID
> )
> )
> So, how to generalize something like:
> SELECT * FROM MYData WHERE RawNumString = '077467881'
> OR RawNumString = '077647881'
>|||[posted and mailed, please reply in ews]
JJA (johna@.cbmiweb.com) writes:
> I would like some advice on a data and query problem I face. I have a
> data table with a "raw key" value which is not guaranteed to be valid
> at its source. Normally, this value will be 9 numeric digits and map to
> a "names" table where the entity is given assigned an "official name".
> My problem is that I'd like to be able to identify data values that are
> "close" to being "correct". For example, in the case of a
> nine digit number such as 077467881, I'd like to be able to identify
> rows with values close to this raw string. That is, if
> there were a row with a value for this column that was "off" by say, a
> transposed single digit (such as 077647881 in this example)
> I would like to find a query to locate the "close candidates" in a
> result set. If I can find rows having a raw key
> value that is close to a "good key" then I can allow my user to use
> other criteria to possibly assign the "close key" as
> an alternate or alias of the official key. Here is part of my schema:
Fuzzy logic is not for the faint of heart, and it's definitely not my
area of expertise.
Assuming that you always have nine digits, one approach is compare
character by character and if 7 or more match, count this as a possible
match:
SELECT *
FROM tbl
WHERE CASE WHEN substring(col, 1, 1) = substring(@.val, 1, 1)
THEN 1 ELSE 0
END +
CASE WHEN substring(col, 2, 1) = substring(@.val, 2, 1)
THEN 1 ELSE 0
END +
..
CASE WHEN substring(col, 9, 1) = substring(@.val, 9, 1)
THEN 1 ELSE 0
END >= 7
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Have you ever worked with check digits before? They can prevent errors
in data entry instead of trying to patch them after the fact. The idea
of keeping an invalid key does not sound like a good design.|||Yes, I know this is not good design but we are getting a raw data file
from another organization and we have no control over their practices.
Most occurrences of this number are "valid" but it is clear from
looking at the data that there is no validation at the source. The
nature of the data is such that if we can identify 7 or 8 bytes of data
as being the same as another 9 byte and valid "key", we could assume
the key could be improved to point at the same 9 byte valid entity. So,
I thought I'd run this notion past the world of experts for some ideas.|||Thanks very much for this neat suggestion. It is exactly what I hoped
for and I can implement this a stored procedure with a couple of
parameters. I will provide a little interface where the analyst can
launch the sproc and see if there are any "near-misses". Very
application of the CASE facility. Thanks again.|||JJA (johna@.cbmiweb.com) writes:
> Thanks very much for this neat suggestion. It is exactly what I hoped
> for and I can implement this a stored procedure with a couple of
> parameters. I will provide a little interface where the analyst can
> launch the sproc and see if there are any "near-misses". Very
> application of the CASE facility. Thanks again.
Glad to hear that the idea was useful to use. Whether it suffices remains
to see. As I said that fuzzy-logic stuff is horrible.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

How to find the second largest value in a field !

Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.Originally posted by chock
Hi,

i am taking the values from four tables ,

I am showing the Salesman in the descending order. according to their Sale Amount. by displaying in Descending, user can able to view the
salesman who sold for the highest amount.

Now I want to find the second highest Amount in the field.

for the highest and lowest we can use the Max and Min funtion.

for the second highest value, How can I write the query.

I already check the previous forums. But i couldn't get the idea.

Kindly reply me

Thank you very much,
Chock.
Well one way would be to say: what is the highest value after the highest value has been excluded (if you follow me):

SELECT MAX(amount)
FROM mytab
WHERE amount != (SELECT MAX(amount) FROM mytab);

Of course, you wouldn't want to use this recursive approach to get the 5th highest amount! For that, you could do:

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);

i.e. get the amount for which there are exactly 4 higher amounts in the table.|||Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.|||Originally posted by chock
Hi,

You send me two queries, the first query I understand it. But in the second query

SELECT amount FROM mytab m1
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab m2
WHERE m2.amount > m1.amount
);
what's m1 and what's m2. In the previous query you didn't use the m1.

Actually Amount is the Field name we are going to compare and select.
and mytab is the Table Name.
I am new to this so I think i need some more o understand. can you please tell about the m1 and m2.

Thank you very much,
Chock.
m1 and m2 are "aliases". I made them up, because I wanted to use the same table "mytab" twice in the same query and compare values. Without aliases the query would be:

SELECT amount FROM mytab
WHERE 4 =
(SELECT COUNT(DISTINCT amount) FROM mytab
WHERE mytab.amount > mytab.amount
);

... which will return no data, because the condition "WHERE mytab.amount > mytab.amount" is nonsense. What I want to say is "WHERE mytab.amount (in this subquery) > mytab.amount (in the main query)". Aliases allow you to do that.|||tony, you may have confused the issue by jumping from the second highest to the fifth

here's another way to get the row with the second highest value:select Salesman, SaleAmount
from SalesTable
where SaleAmount =
( select max(SaleAmount)
from SalesTable
where SaleAmount <
( select max(SaleAmount)
from SalesTable
)
)in english, "get the row where the SaleAmount is the highest SaleAmount that is less than the highest overall SaleAmount"

wouldn't want to nest that too deeply, eh

i believe a good optimiser will evaluate the innermost first (it is not correlated), then the next inner, then do a straight retrieval -- i could be wrong, though (it has happened, and optimizer performance is not my long suit)

rudy
http://r937.com

How to find the maximum possible value of a given datatype

Does anyone know of a built-in function to return the maximum possible
value of a given datatype? I have to return the biggest value for a
smalldatetime or datetime in a view if the field is null, but can't
find such a function. The closest I've come is:

select datalength(cast(getdate() as smalldatetime))

...but that only return the number of bytes, not the value itself,
which is '6-6-2079 11:59'

I know I could create my own lookup table and function, but I was
hoping that Transact-SQL would have a built-in solution

--John Hunter>One alternative is to write your own function that encapsulates the

Quote:

Originally Posted by

>hardcode. Pass the table and column names, query the system tables
>for the type and related information such as max length of a varchar,
>and then return it. Clumsy, no doubt.

My apologies, it is nowhere near as simple as I described as, at a
minimum, one function for each datatype group (dates, character
strings, etc) would be required.

Roy Harvey
Beacon Falls, CT|||jshunter@.waikato.ac.nz wrote:

Quote:

Originally Posted by

Does anyone know of a built-in function to return the maximum possible
value of a given datatype? I have to return the biggest value for a
smalldatetime or datetime in a view if the field is null,

If you (and the recipient of this return value, if it isn't another
piece of T-SQL) can wrap your head around ternary logic, then you
can use the null value as is:

where not (some_date end_date)

Or, depending on the expected longevity of the system, you can (a)
hard-code June 6, 2079 (max smalldatetime) or (b) avoid smalldatetime
and hard-code December 31, 9999 (max datetime). I would personally
go with (b), as I can't imagine actually designing a system that had
a good reason to use smalldatetime for anything.|||No there isn't.

But there are solutions. You can read more at
http://groups.google.nl/group/micro...743f4aea485c6d1
(url may wrap)

HTH,
Gert-Jan

"jshunter@.waikato.ac.nz" wrote:

Quote:

Originally Posted by

>
Does anyone know of a built-in function to return the maximum possible
value of a given datatype? I have to return the biggest value for a
smalldatetime or datetime in a view if the field is null, but can't
find such a function. The closest I've come is:
>
select datalength(cast(getdate() as smalldatetime))
>
...but that only return the number of bytes, not the value itself,
which is '6-6-2079 11:59'
>
I know I could create my own lookup table and function, but I was
hoping that Transact-SQL would have a built-in solution
>
--John Hunter

How to find the duplicate value

I want to set a filed to primary key.
But there duplicate value in it.
How can I find all rows with the duplicate value of that field?
ad
Itzik Ben-Gan written a greate examples about that
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
> I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>
|||Hi
If the whole row is duplicated then you can create a temporary table with
the same structure that is populated with
INSERT INTO #tmp SELECT DISTINCT * FROM MyTable
you can then
TRUNCATE MyTable
and re-insert the value back
INSERT INTO MyTable SELECT * FROM #tmp
If this is not the case, then you will need to differentiate the records
somehow and then choose one to keep e.g. If there say a datetime column
called date_created and you wish to keep the earliest and you primary key is
a column(s) called PK then (assuming date_created is unique for each pk)
DELETE FROM MyTable
FROM MyTable t
WHERE t.date_created > ( SELECT MIN(date_created) FROM MyTable M where m.pk
= t.pk )
John
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>
|||On Mon, 28 Mar 2005 16:19:13 +0800, ad wrote:

>I want to set a filed to primary key.
>But there duplicate value in it.
>How can I find all rows with the duplicate value of that field?
>
Hi ad,
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*)
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Hugo left out a little part on his query... on the having clause
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*) > 1
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
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>
|||On Mon, 28 Mar 2005 08:26:42 -0500, Wayne Snyder wrote:

>Hugo left out a little part on his query... on the having clause
Ouch! Thanks for catching that, Wayne!
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to find the duplicate value

I want to set a filed to primary key.
But there duplicate value in it.
How can I find all rows with the duplicate value of that field?ad
Itzik Ben-Gan written a greate examples about that
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
> I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||Hi
If the whole row is duplicated then you can create a temporary table with
the same structure that is populated with
INSERT INTO #tmp SELECT DISTINCT * FROM MyTable
you can then
TRUNCATE MyTable
and re-insert the value back
INSERT INTO MyTable SELECT * FROM #tmp
If this is not the case, then you will need to differentiate the records
somehow and then choose one to keep e.g. If there say a datetime column
called date_created and you wish to keep the earliest and you primary key is
a column(s) called PK then (assuming date_created is unique for each pk)
DELETE FROM MyTable
FROM MyTable t
WHERE t.date_created > ( SELECT MIN(date_created) FROM MyTable M where m.pk
= t.pk )
John
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||On Mon, 28 Mar 2005 16:19:13 +0800, ad wrote:

>I want to set a filed to primary key.
>But there duplicate value in it.
>How can I find all rows with the duplicate value of that field?
>
Hi ad,
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo left out a little part on his query... on the having clause
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*) > 1
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
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||On Mon, 28 Mar 2005 08:26:42 -0500, Wayne Snyder wrote:

>Hugo left out a little part on his query... on the having clause
Ouch! Thanks for catching that, Wayne!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

How to find the duplicate value

I want to set a filed to primary key.
But there duplicate value in it.
How can I find all rows with the duplicate value of that field?ad
Itzik Ben-Gan written a greate examples about that
CREATE TABLE #Demo (
idNo int identity(1,1),
colA int,
colB int
)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (1,6)
INSERT INTO #Demo(colA,colB) VALUES (2,4)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (4,2)
INSERT INTO #Demo(colA,colB) VALUES (3,3)
INSERT INTO #Demo(colA,colB) VALUES (5,1)
INSERT INTO #Demo(colA,colB) VALUES (8,1)
PRINT 'Table'
SELECT * FROM #Demo
PRINT 'Duplicates in Table'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo <> B.idNo
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Duplicates to Delete'
SELECT * FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
DELETE FROM #Demo
WHERE idNo IN
(SELECT B.idNo
FROM #Demo A JOIN #Demo B
ON A.idNo < B.idNo -- < this time, not <>
AND A.colA = B.colA
AND A.colB = B.colB)
PRINT 'Cleaned-up Table'
SELECT * FROM #Demo
DROP TABLE #Demo
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
> I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||Hi
If the whole row is duplicated then you can create a temporary table with
the same structure that is populated with
INSERT INTO #tmp SELECT DISTINCT * FROM MyTable
you can then
TRUNCATE MyTable
and re-insert the value back
INSERT INTO MyTable SELECT * FROM #tmp
If this is not the case, then you will need to differentiate the records
somehow and then choose one to keep e.g. If there say a datetime column
called date_created and you wish to keep the earliest and you primary key is
a column(s) called PK then (assuming date_created is unique for each pk)
DELETE FROM MyTable
FROM MyTable t
WHERE t.date_created > ( SELECT MIN(date_created) FROM MyTable M where m.pk
= t.pk )
John
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||On Mon, 28 Mar 2005 16:19:13 +0800, ad wrote:
>I want to set a filed to primary key.
>But there duplicate value in it.
>How can I find all rows with the duplicate value of that field?
>
Hi ad,
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||Hugo left out a little part on his query... on the having clause
For just finding the duplicate key values:
SELECT KeyColumn, COUNT(*)
FROM MyTable
GROUP BY KeyColumn
HAVING COUNT(*) > 1
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
"ad" <ad@.wfes.tcc.edu.tw> wrote in message
news:%23AKUV72MFHA.1176@.TK2MSFTNGP15.phx.gbl...
>I want to set a filed to primary key.
> But there duplicate value in it.
> How can I find all rows with the duplicate value of that field?
>|||On Mon, 28 Mar 2005 08:26:42 -0500, Wayne Snyder wrote:
>Hugo left out a little part on his query... on the having clause
Ouch! Thanks for catching that, Wayne!
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)

how to find that the table column exists through program

i need to check that the column exist in the table if yes the update/insert value in the column else i need to add the new column like new browser name in the table.

after the search i found some thing like and make the procedure like

Dim daAs SqlDataAdapter, dsAs DataSet, dcAs DataColumn, foundsAsBoolean

Try

Conn.Open()

cmd =New SqlCommand(str, Conn)

da =New SqlDataAdapter(cmd)

ds =New DataSet

da.Fill(ds,"tbls")

ForEach dcIn ds.Tables(0).Columns

If UCase(colnames) = UCase(dc.ColumnName)Then

founds =True

ExitFor

Else

founds =False

EndIf

Next

Catch exAs Exception

Finally

Conn.Close()

EndTry

Return founds

sugesstions on this is required.................

How to find Server Collation from SQL 2005 tables/views?

Does anyone know what table, view, etc., contains the SQL Server 2005 server
collation (not database collation) value? When I use SQLDMO it returns an
empty string, yet when I view the SQL Server 2005 instance properties in SQL
Server Management Studio, it correctly shows as SQL_Latin... etc.
I have tried querying the master.sys.sysservers view and msdb.sys.sysservers
view but they both report null as well.
Where can I find the Server Collation property programmatically in SQL 2005?
Thanks experts!Perhaps you are looking for
serverproperty('collation')
Ben Nevarez, MCDBA, OCP
Database Administrator
"Mark Findlay" wrote:

> Does anyone know what table, view, etc., contains the SQL Server 2005 serv
er
> collation (not database collation) value? When I use SQLDMO it returns an
> empty string, yet when I view the SQL Server 2005 instance properties in S
QL
> Server Management Studio, it correctly shows as SQL_Latin... etc.
> I have tried querying the master.sys.sysservers view and msdb.sys.sysserve
rs
> view but they both report null as well.
> Where can I find the Server Collation property programmatically in SQL 200
5?
> Thanks experts!
>|||Perfect! Thanks!
Mark
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:715E7532-7B21-4015-94F7-72237EF02FFA@.microsoft.com...
> Perhaps you are looking for
> serverproperty('collation')
> Ben Nevarez, MCDBA, OCP
> Database Administrator
>
> "Mark Findlay" wrote:
>

How to find row value not present

Hi All,
I have table with 2 cols 1)ObjectName 2)Owner (omlitted other cols for
simplicity)
ObjectName Owner
obj1 own1
obj1 own2
obj2 own1
obj2 own2
obj3 own1
obj4 own2
if ObjectName is present for own1 It should also be there for own2 &
other way around if ObjectName is present for own2 it should also be
there for own1. I want to find out rows which does not meet such
criteria. like in above example last 2 rows for obj3 & obj4.
As obj3 is present for only own1 & obj4 is present for own2 only.
Could any one guide me how to go about it?
Thanks in advance for help
VinodVinod
CREATE TABLE dbo.Stuffs (
obj_name VARCHAR(4) NOT NULL
,owner VARCHAR(4) NOT NULL
CHECK (owner IN ('own1', 'own2'))
,PRIMARY KEY (obj_name, owner)
);
INSERT INTO Stuffs VALUES('obj1', 'own1');
INSERT INTO Stuffs VALUES('obj1', 'own2');
INSERT INTO Stuffs VALUES('obj2', 'own1');
INSERT INTO Stuffs VALUES('obj2', 'own2');
INSERT INTO Stuffs VALUES('obj3', 'own1');
INSERT INTO Stuffs VALUES('obj4', 'own2');
SELECT obj_name FROM Stuffs
GROUP BY obj_name
HAVING COUNT(owner) <> 2
;
Joe|||create table foo (objectname varchar(5), owner varchar(5))
insert into foo values ('obj1','own1')
insert into foo values ('obj1','own2')
insert into foo values ('obj2','own1')
insert into foo values ('obj2','own2')
insert into foo values ('obj3','own1')
insert into foo values ('obj4','own2')
insert into foo values ('obj4','own3')
insert into foo values ('obj4','own3')
select every.*
from
(select obj.objectname, own.owner from (select distinct objectname from foo)
obj
cross join (select distinct owner from foo) own ) every
where objectname+owner not in (select objectname+owner from foo)
"vinod" <vinod.patil1@.gmail.com> wrote in message
news:1132180115.463350.278900@.g44g2000cwa.googlegroups.com...
> Hi All,
> I have table with 2 cols 1)ObjectName 2)Owner (omlitted other cols for
> simplicity)
> ObjectName Owner
> obj1 own1
> obj1 own2
> obj2 own1
> obj2 own2
> obj3 own1
> obj4 own2
> if ObjectName is present for own1 It should also be there for own2 &
> other way around if ObjectName is present for own2 it should also be
> there for own1. I want to find out rows which does not meet such
> criteria. like in above example last 2 rows for obj3 & obj4.
> As obj3 is present for only own1 & obj4 is present for own2 only.
> Could any one guide me how to go about it?
> Thanks in advance for help
> Vinod
>|||Why do you think that EVERY is a keyword in SQL?|||"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1132196174.489954.44330@.g14g2000cwa.googlegroups.com...
> Why do you think that EVERY is a keyword in SQL?
>
He doesn't think that. He thinks it's a table alias, because that
is what it is being used for.
Sincerely,
Chris O.

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
YaroOK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

How to find record with xml data containing value...

Hello all,
SQL2005
I am newbe in XPath and XQuery
I have to find in table with xml field all records where value of attribute
contains string (e.g person name)
drop table dbo.bbb;
create table dbo.bbb(
p1 int not null identity,
p2 xml,
primary key(p1) );
insert into dbo.bbb values
('<root><item werte="d1"/><item werte="a2"/></root>');
insert into dbo.bbb values
('<root><item werte="b1"/><item werte="b2"/></root>');
select *
from dbo.bbb
where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
this query give me records with @.werte containing "a" but only if it is in
first item,
I have to find records with @.werte containing "a" regardless of item
position.
Can anybody help me write this query?
Regards
Yaro
OK, I know
select *
from dbo.bbb
where
p2.exist('/root/item/@.werte[contains(.,"a")]') = 1
Yaro
Uytkownik "Yaro" <yarok_delthisdes_@.op.pl> napisa w wiadomoci
news:e12vcm$nq4$1@.83.238.170.160...
> Hello all,
> SQL2005
> I am newbe in XPath and XQuery
> I have to find in table with xml field all records where value of
> attribute contains string (e.g person name)
> drop table dbo.bbb;
> create table dbo.bbb(
> p1 int not null identity,
> p2 xml,
> primary key(p1) );
> insert into dbo.bbb values
> ('<root><item werte="d1"/><item werte="a2"/></root>');
> insert into dbo.bbb values
> ('<root><item werte="b1"/><item werte="b2"/></root>');
> select *
> from dbo.bbb
> where p2.value('contains((/root/item/@.werte)[1],"a")','bit') = 1
> this query give me records with @.werte containing "a" but only if it is
> in first item,
> I have to find records with @.werte containing "a" regardless of item
> position.
> Can anybody help me write this query?
> Regards
> Yaro

How to find nth lowest value in a row

Morning all,
I have a table with 500+ fields of numeric data (a time series of values) I
import from an external system. I need to find the 5th and 6th lowest values
in a given row to perform some calculations.
I currently do this by transposing the data row ie turning the row into a
column sorting it and then using row_num to get the 5th and 6th lowest
values. This transpose is a real bottleneck on my process.
Does anyone have any alternatives to my method ? All suggestions gratefully
received.
Thanks
WOn Thu, 16 Aug 2007 17:56:35 -0700, willcas wrote:
>Morning all,
>I have a table with 500+ fields of numeric data (a time series of values) I
>import from an external system. I need to find the 5th and 6th lowest values
>in a given row to perform some calculations.
>I currently do this by transposing the data row ie turning the row into a
>column sorting it and then using row_num to get the 5th and 6th lowest
>values. This transpose is a real bottleneck on my process.
>Does anyone have any alternatives to my method ? All suggestions gratefully
>received.
Hi W,
Perhaps you could transpose the data during the import and store it in a
more relational way?
The only other alternative would be a really very nasty (and long!) CASE
epxression that I won't even begin to think about <shudder>.
--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis|||"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W
I would treat this as a staging table and transform the data to a properly
normalized model. Then write your query against that new model.
Perhaps better still, do the transformation before the load (using
Integration Services for example).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Can't you use unpivot for the transposition?
"willcas" <willcas@.discussions.microsoft.com> wrote in message
news:6658592C-0F0A-45C6-BB9C-DFDDF0263FF2@.microsoft.com...
> Morning all,
> I have a table with 500+ fields of numeric data (a time series of values)
> I
> import from an external system. I need to find the 5th and 6th lowest
> values
> in a given row to perform some calculations.
> I currently do this by transposing the data row ie turning the row into a
> column sorting it and then using row_num to get the 5th and 6th lowest
> values. This transpose is a real bottleneck on my process.
> Does anyone have any alternatives to my method ? All suggestions
> gratefully
> received.
> Thanks
> W

How to find Median Values in SQL

Hello,
Is there any way or function to find Median value in Sql
Server 2000?.
Thanks,
NilayYes, refer to
http://groups.google.com/groups?dq=&hl=nl&lr=&ie=UTF-8&threadm=3BC75285.8A38CC6%40toomuchspamalready.nl&rnum=1&prev=/groups%3Fq%3Dg:thl2156744539d%26dq%3D%26hl%3Dnl%26lr%3D%26ie%3DUTF-8%26selm%3D3BC75285.8A38CC6%2540toomuchspamalready.nl
(url may wrap)
This thread gives many solutions.
Gert-Jan
Nilay wrote:
> Hello,
> Is there any way or function to find Median value in Sql
> Server 2000?.
> Thanks,
> Nilay