getting a set of values from xml

i have imported xml into an xml datatype variable. here is a tiny version of my xml file.

<Root>

<TOP>

<USERS>

<USER>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>'

I need to pullout just the username, so the query method is out as it will return it in xml format. how do i just get a column containing all of the usernames?

You could use nodes table-value function:

create table #xml_table

(

xml_col xml

)

go

insert into #xml_table values('<Root>

<TOP>

<USERS>

<USER>

<USERNAME>jukkaw</USERNAME>

</USER>

<USER>

<USERNAME>v-derekn</USERNAME>

</USER>

</USERS>

</TOP>

</Root>

')

select x.value('.[1]','varchar(100)')

from #xml_table t cross apply xml_col.nodes('/Root/TOP/USERS/USER/USERNAME/text()') as tab(x)

getting 100 rows with values from 1 - 100

I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
KarlCheck out:
http://msdn.microsoft.com/library/d...r />
p03k1.asp
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
<klumsy@.xtra.co.nz> wrote in message
news:1115860445.064283.61420@.f14g2000cwb.googlegroups.com...
I am trying to right a query that will return 100 rows, of one column,
and the data being 1 to 100
i can do this with a cursor ok, i can also do it with a select INTO a
tempoary table with IDENTITY
however is there any way i can do this without a temporary table or
cursor
Karl

Getting 0 padded values in the columns.

Getting 0 padded values in the columns.

Hi All,

I have a requirement to convert a integer to string and display it in
Sql server with fixed length say 3 chars. (in c, we wud use %03d in
printf)

If the number is small say, 9 then it has to be displayed as 009,
56 -> 056, 897-> 897, 6786 -> xxx

Checked through STR and CAST functions, couldn't find any relevant
paramters.

if you have any ideas, please mail me.

Thanks & Regards,
Chandra MohanDo:

SELECT CASE WHEN LEN(@.n) <= 3
THEN RIGHT('000' + CAST(@.n AS VARCHAR), 3)
ELSE 'xxx'
END ;

--
- Anith
( Please reply to newsgroups only )|||bschandramohan@.yahoo.com (Chandra Mohan) wrote in message news:<bb0ef6.0308200036.236c3321@.posting.google.com>...
> Getting 0 padded values in the columns.
> Hi All,
> I have a requirement to convert a integer to string and display it in
> Sql server with fixed length say 3 chars. (in c, we wud use %03d in
> printf)
> If the number is small say, 9 then it has to be displayed as 009,
> 56 -> 056, 897-> 897, 6786 -> xxx
> Checked through STR and CAST functions, couldn't find any relevant
> paramters.
> if you have any ideas, please mail me.
> Thanks & Regards,
> Chandra Mohan

Hi ,

You could use this :

select replicate('0', 3-datalength(cast(column as varchar(10)))) +
cast (column as varchar(10)) from table

Replace the column and table with the right values and here the
assumption is the column is of int datatype.

Regards,
-Manoj Rajshekar

Getting #Error details from deployed report

I have a report that displays #Error for some field values when deployed on the server. Is there any way to get details on the error?

Thanks.

Most likely the #Error comes from making a call into a custom assembly and insufficient security permissions for that custom assembly to be invoked.

You can partially simulate the server environment, if you run the report in the stand-alone preview of report designer. I.e. instead of clicking on the Preview tab, hit F5 which should open a new window for the stand-alone preview. For the #Error you should get warning messages with more details in the output window.

-- Robert

Gettin data fromdifferent database

Hi

My query syntax is such

select 'INSERT INTO UserCourse(Start_Date, User_ID) values
(Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
FROM DB1..EMPLOYEE_TRAINING, DB2..User
WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

I run this query in the Query Analyser on the database DB1 and get the following error

The column prefix 'User' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

What I need is to get the ID and Date from differnt tables in different databases
Any clues or suggestions
NimishaTry changing:

FROM DB1..EMPLOYEE_TRAINING, DB2..User

WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

to

FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User

WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

HTH

Ken|||Hi,
Try this
In clause replace the DB2..User
with servername.databasename.userid.tablename
and in where clause simply use the column name.

e.g

select *
from a, ndokech1.echi.dbo.tx_echi c
where a.myid = c.myid

i think it will work..

Cheers
Gola munjal

Originally posted by Nimisha
Hi

My query syntax is such

select 'INSERT INTO UserCourse(Start_Date, User_ID) values
(Course_Date + ''', ''' + rtrim(convert(varchar(20),AppUser.ID)) + ''');'
FROM DB1..EMPLOYEE_TRAINING, DB2..User
WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

I run this query in the Query Analyser on the database DB1 and get the following error

The column prefix 'User' does not match with a table name or alias name used in the query.
Server: Msg 107, Level 16, State 1, Line 1
The column prefix 'DB1..User' does not match with a table name or alias name used in the query.

What I need is to get the ID and Date from differnt tables in different databases
Any clues or suggestions
Nimisha|||Sorry i thought u asked for getting data from different servers.
i think the problem is with table name user, bcause user is a keyword in sql.
give user table name with in [] this.
select *
from fscms..[user] ,fscms_backup..[user]
where fscms..[user].Employee_Number = fscms_backup..[user].Employee_Number

i tried this ..its working ..

Cheers
gola

Originally posted by GA_KEN
Try changing:

FROM DB1..EMPLOYEE_TRAINING, DB2..User

WHERE DB2..User.Employee_Number = DB1..EMPLOYEE_TRAINING.EMPLOYEE_NO

to

FROM [DB1].dbo.EMPLOYEE_TRAINING, DB2..User

WHERE [DB2].dbo.User.Employee_Number = [DB1].dbo.EMPLOYEE_TRAINING.EMPLOYEE_NO

HTH

Ken|||Thanx guys for all the input apprecaite it.
Nimisha

geting value from binary

hi,
is it possible to get value as follows in SQL server
I will have values in 101, 111 like in binary , I want to get its decimal values
I want to get 5 for 101, 7 for 111 ...
please let me know ...
RamSo, you have a string containing a kind of binary number?

Consider to solve this in a user-defined function. Loop through your string, and compute the power of 2 if applicable.

GetDate() in SQL Server

Hi,
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' +
convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate
()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate())
, 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (T
his happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happe
ning?
Thanks,
C.Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...

GetDate() in SQL Server

Hi,
I have a Stored Proc that creates an Unique ID for me.
I pass in an ID and append on other values as below.
select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_' + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi, getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms, getdate()), 2))
In some cases my Left(datepart(ms, getdate()), 2)) returns the same value (This happens approx 1 in 5000 ID's that I create.)
Does anyone know why this is the case? Is there some kind of buffering happening?
Thanks,
C.
Time in SQL Server is only accurate to 1/300th of a second, so if you have
two calls to your stored procedure within that timeframe, you will get the
same ID. Downside is that your code doesn't work as expected, upside is that
your server is performing reasonably well ;-)
If you want a truly unique number, you can use a GUID, which you can
generate with NEWID().
Jacco Schalkwijk
SQL Server MVP
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
> Thanks,
> C.
|||Using time, even as part of a uniqueID, is a flawed approach. You know that
two events can happen at the same time, especially given SQL Server's loose
accuracy, right? Why do you need such a complex and manual uniqueID anyway?
SQL Server has multiple built-in facilities for this, such as IDENTITY,
GUID...
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
> + convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
> getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
> getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
> (This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
> happening?
> Thanks,
> C.
|||"C" <anonymous@.discussions.microsoft.com> wrote in message
news:EFB88CC5-21CA-4880-B07D-5B7F6026740E@.microsoft.com...
> Hi,
> I have a Stored Proc that creates an Unique ID for me.
> I pass in an ID and append on other values as below.
> select @.ID + '_' + REPLACE(CONVERT(varchar,getdate(), 103), '/', '') + '_'
+ convert(varchar,(datepart(hh, getdate()) * 360000) + (datepart(mi,
getdate()) * 6000) + (datepart(ss, getdate()) * 100) + Left(datepart(ms,
getdate()), 2))
> In some cases my Left(datepart(ms, getdate()), 2)) returns the same value
(This happens approx 1 in 5000 ID's that I create.)
> Does anyone know why this is the case? Is there some kind of buffering
happening?
the range of ms is 0-999 and repeats every second ...

GetChanges Update problem

I have the below C# routine which is working but now I need to write its newly randomized L_Rank values back up to the Sql Server. How can i do that in Button1_Click? DataTable 'dt' contains only two fields - L_ID and L_Rank. I have a stored procedure but I do not know how to call it in Button1_Click and pass the parameters it is looking for.

SP:
PROCEDURE RandomizeLinks
@.L_ID int,
@.L_Rank int
AS
UPDATE tblLinkInfo_OLD2 SET L_Rank = @.L_Rank
WHERE (L_ID = @.L_ID)

protected void Button1_Click(object sender, EventArgs e)
{
GetRandLinks();
DataTable dt = GetRandLinks();
int RowIncrement;
RowIncrement = 0;
System.Random myRandom = new System.Random();
foreach (DataRow row in dt.Rows)
{
int LinkRank = myRandom.Next(25, 250);
row["L_Rank"] = LinkRank;
RowIncrement++;
}

Is what I want to do possible - it seems easy but nothing I do works.

|||

Am I asking the question wrong - Can you use a DataTable to update a SQL Table and can it be done in a batch UPDATE as opposed to incrementing through every row?

|||

anyone?

GetAttribute Method of the Urn Class

There is probably a simple way to find this out but I cannot find it:

How would I be able to find out what the possible attributeName values are which you can retrieve by using the GetAttribute method. For example, one of them is "Name" which returns the name of the object. I need to find out the type of the object (e.g. table, view, UDF...)

You can obtain the type of the object as a string using the Urn.Type property.
eg. for Urn u = new Urn("Server/Database/Table");
u.Type would return you "Table".

Thanks,
Kuntal

GET VS. POST METHODS and Querystring lengths

We've run into a rather sizable problem here.

When passing querystring values using GET, to our SQL Server Reporting Services Reports, we've discovered that we've exceeded the maximum URL length,.

We are thinking that we need to change how we're passing this data to our reports.

Can we use POST and transfer the data throught hte HTTP header and bypass this limitation?

Thanks.

Doug

What in the world could you be passing as parameters? Is it that there are so many parameters or that the parameter values are long strings?|||yes u can use hidden variables , param tags or Session variable to overcome this problem|||

good question.

We have data issues on our back end...which translates to list box items with multiple sometimes up to 10 values per selection. With multiple selections in a wizard that has 5 steps, they add up...

yeah it's ugly...

|||Maybe you should create a record in a temp database with all the keys/values. Then pass in the key to that record to your report. A datasource goes out gets the crazy values.

Get values from database query

I have written the following lines

myConnection =New MySqlConnection("server=" + dbServer +"; user id=" + dbUserID +"; password=" + dbPassword +"; database=" + dbName +"; pooling=false;")strSQL ="SELECT * FROM user where type=1;"

user table has name, tel, addr, id, type fields

I would like to know how to use a string array to store the name in the result of strSQL?

Thank you

Hi thtang

You can use fllowing code:

SqlCommand cmd = new SqlCommand(myConnection ,strSQL );

SqlDataReader dr = cmd.ExecuteReader();

while(dr.Read())

{

stringList.Add(dr.GetString(0));

}

Get value of parameters passed in stored procedure in a trigger

In updating my tables (insert/update), i use stored procedures.
Some of the values passed as parameters to the stored procedures
are only necessary for audit trail only and not for updating the tables.
How can i get hold of these parameter values while inside a trigger?Put the parameters in a permanent table or a local temp table.
David Portas
SQL Server MVP
--
"manK" <manK@.discussions.microsoft.com> wrote in message
news:EA0833BF-A070-4720-9E50-9C80EAE45FF9@.microsoft.com...
> In updating my tables (insert/update), i use stored procedures.
> Some of the values passed as parameters to the stored procedures
> are only necessary for audit trail only and not for updating the tables.
> How can i get hold of these parameter values while inside a trigger?
>

Get Value from CURSOR

I'm writing a stored procedure that involves looping through a recordset and using the values from the recordset as parameters for a second stored procedure. Here's what I've got so far...

My question is, how do I get the value out of the Cursor? There's only one field.

Declare @.Day as int
Declare @.Plant as varchar(30)

SET NOCOUNT ON

CREATE Table #Temp (Facility varchar(30), ProductCategory nvarchar(3), Target int, Quantity int, Percentage decimal(10,2), Production_Date smalldatetime,As_Of_Time smalldatetime)

Declare Facility_Cursor CURSOR
For Select Distinct(Facility) From ProductionHistory
OPEN Facility_CURSOR
Declare @.Facility_Cursor as sysname

FETCH NEXT From Facility_CURSOR into @.Facility_Cursor

WHILE @.@.FETCHSTATUS = 1

--YESTERDAY
Set @.Day = -2

Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'

--TODAY
SET @.Day = -1
Insert Into #Temp
exec sp_GetDailyProductionByPlantAndCategory @.Day, @.Facility, 'NAP'

FETCH NEXT FROM Facility_CURSOR into @.Facility_Cursor

CLOSE Facility_Cursor
DEALLOCATE Facility_CURSOR

SET NOCOUNT OFF

Select * From #Temp ORDER BY Production_Date, Facility, ProductCategory DESCI see that you are trying to pass a variable "@.Facility" to your sproc without defining it, and the results of the cursor are being placed into @.Facility_Cursor. If you change the variable declaration to @.Facility and then FETCH NEXT From Facility_CURSOR into @.Facility it just might work!|||That did the trick. I knew it was something simple. Thanks!

Get Unique Values in a group statement

Hi,
Suppose a table [Inventory]:

Item Color Quantity
------- ------- --------
Table Blue 10
Table Red 20
Table Yellow 30
Chair Blue 40
Chair Red 50

I'm wondering if there is a group state like this:
Select Item, ?Function(Color), Sum(Quantity) From Inventory Group by Item
which returns this:

Table Blue,Red,Yellow 60
Chair Blue,Red 90

Does anyone has an idea how this can be achieved?

Regards,
Manolis PerrakisDoes anyone has an idea how this can be achieved?Ooh ooh ooh - me - pick me!

http://sqljunkies.com/WebLog/amachanic/archive/2004/11/10/5065.aspx?Pending=true
:)|||Hi,
thanks for the answer.
I don't think is a good idea to run a query for each record. This will consume a lot of resources, and in my case when having to do with large tables and a lot of resulted records this is not an option.
I was hoping there was an internal function, of if it can be defined such a funtion. For example if AVG is used the MSSQL access all the records keep their values and at the end calculates the result. Instead of adding these values I was hoping to create a string which can be compared internally without having to execute another query.
Reagrds,
Manolis Perrakis|||Search the text for "Yep, me too... until I tuned it and then it did it all in 5 seconds flat on a million rows for 50,000 CustID's. " and see if the suggested optimisations are appropriate for you.

Ultimately you are taking relational data and trying to put it into a context that violates first normal form so it is not surprising that SQL does not provide a built in function to do this.

The other alternative is that you can write (if you are using SQL Server 2005) your own CLR aggregate functions. I don't really know anything about these (including how they perform) but you could try researching to see if it is applicable to your needs.|||Yes you are right. There is the article:
http://msdn2.microsoft.com/en-us/library/ms131056.aspx
that does exactly this.
However I use MSSQL2000.|||However I use MSSQL2000.I suspect you are probably stuffed then.

I like the article I linked to as it demonstrates, and compares, two methods of skinning this particular cat. In particular it focuses on performance and, as I pointed out, later on one contributer offers a few refinements that get a decent performance for a medium sized table (1 million rows).

I doubt you will find any alternative technique that will substantially outperform the solution in the article but maybe one of the posters here will surprise me :)

BTW - what sort of performance did you get?|||Hi,
I want to use this operation in some aggregate complex queries that are already slow. Apart from this in order to get the correct data when calculating these value I must run these queries with other criteria also, such as date range which must be used in order to get the correct records. Therefore it's quite complicated. However I will try it the next days.
Regards,
Manolis|||I don't suppose there is a finite, known and ideally small number of possible "color" values?|||No, the "color" values is large, about 25000.|||you can get these things in 25000 different colours? Henry Ford would turn in his grave...|||I don't think is a good idea to run a query for each record. Just thought - I don't know how SQL Server optimises the query for this but presumably does as you suggest - runs the function for each record and then groups on the results. So you could improve things (I imagine) with something like:

SELECT Item, dbo.MyConcatFunction(Item) AS CSV_Colors, TotalQuantity
FROM (
SELECT Item, SUM(Quantity) AS TotalQuantity
FROM [Inventory]
GROUP BY Item
) AS Distinct_Items
Call the function once per item rather than once per row.

Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. Severson
I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID =
(SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:

>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson

Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. SeversonWith SQL 2005, you can try something like that :
with Ordering (JOB_ID, TASK_ID, RN) as
(select JOB_ID,
TASK_ID,
ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY PRIORITY)
from MyTable
)
select JOB_ID, TASK_ID
from Ordering
where RN=1
With SQL 2000, you should work with # tables or table variables. (with ORDER
BY ...)
JN.
"NerdRunner" <sseverson@.2sts.biz> a écrit dans le message de news:
1169075237.717157.102200@.s34g2000cwa.googlegroups.com...
> My task table contains the id of the task, the jobid that the task is
> associated with, and a numeric value representing the priority of the
> task. There can be multiple tasks for a job. I need a view that
> returns the task id, and job id of the task with the higest priority
> for each job. I tried grouping but this does not appear to work.
> Thanks for the help.
> Sean M. Severson
>|||I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID = (SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:
>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson|||Roy,
That did it. I was missing the JobID comparison. Thanks so much!!
Sean M. Severson
Roy Harvey wrote:
> I think this will do it.
> SELECT *
> FROM Tasks as A
> WHERE TaskID => (SELECT TOP 1 TaskID
> FROM Tasks as B
> WHERE A.JobID = B.JobID
> ORDER BY B.Priority DESC)
> Roy Harvey
> Beacon Falls, CT
> On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
> wrote:
> >My task table contains the id of the task, the jobid that the task is
> >associated with, and a numeric value representing the priority of the
> >task. There can be multiple tasks for a job. I need a view that
> >returns the task id, and job id of the task with the higest priority
> >for each job. I tried grouping but this does not appear to work.
> >
> >Thanks for the help.
> >
> >Sean M. Severson

Get top values

My task table contains the id of the task, the jobid that the task is
associated with, and a numeric value representing the priority of the
task. There can be multiple tasks for a job. I need a view that
returns the task id, and job id of the task with the higest priority
for each job. I tried grouping but this does not appear to work.
Thanks for the help.
Sean M. SeversonWith SQL 2005, you can try something like that :
with Ordering (JOB_ID, TASK_ID, RN) as
(select JOB_ID,
TASK_ID,
ROW_NUMBER() OVER (PARTITION BY JOB_ID ORDER BY PRIORITY)
from MyTable
)
select JOB_ID, TASK_ID
from Ordering
where RN=1
With SQL 2000, you should work with # tables or table variables. (with ORDER
BY ...)
JN.
"NerdRunner" <sseverson@.2sts.biz> a crit dans le message de news:
1169075237.717157.102200@.s34g2000cwa.googlegroups.com...
> My task table contains the id of the task, the jobid that the task is
> associated with, and a numeric value representing the priority of the
> task. There can be multiple tasks for a job. I need a view that
> returns the task id, and job id of the task with the higest priority
> for each job. I tried grouping but this does not appear to work.
> Thanks for the help.
> Sean M. Severson
>|||I think this will do it.
SELECT *
FROM Tasks as A
WHERE TaskID =
(SELECT TOP 1 TaskID
FROM Tasks as B
WHERE A.JobID = B.JobID
ORDER BY B.Priority DESC)
Roy Harvey
Beacon Falls, CT
On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
wrote:

>My task table contains the id of the task, the jobid that the task is
>associated with, and a numeric value representing the priority of the
>task. There can be multiple tasks for a job. I need a view that
>returns the task id, and job id of the task with the higest priority
>for each job. I tried grouping but this does not appear to work.
>Thanks for the help.
>Sean M. Severson|||Roy,
That did it. I was missing the JobID comparison. Thanks so much!!
Sean M. Severson
Roy Harvey wrote:[vbcol=seagreen]
> I think this will do it.
> SELECT *
> FROM Tasks as A
> WHERE TaskID =
> (SELECT TOP 1 TaskID
> FROM Tasks as B
> WHERE A.JobID = B.JobID
> ORDER BY B.Priority DESC)
> Roy Harvey
> Beacon Falls, CT
> On 17 Jan 2007 15:07:17 -0800, "NerdRunner" <sseverson@.2sts.biz>
> wrote:
>

Get the value of a testbox

Hello, I built a report which returns some calculated fields and I would like to know if it's possible to use the returned values in another field.

Exemple:
textbox10 = Fields!MyColumn.Value
textbox11 = Fields!MyOtherColumn.Value

I would like to know if there is a way to do something like this:
textbox12 = textbox10 + textbox11

Has anyone any idea? Thanks in advance for your help

Use the ReportItems collection i.e.

Code Snippet

textbox12 = ReportItems!textbox10.Value + ReportItems!textbox11.Value

Get some values from a group

Hello,

here's my problem. There's table t with:

year, id, price
2000 1 100
2000 1 200
2000 2 100
2000 3 500
2000 4 100
2001 1 100
2001 2 300
...

I need a way, to get those prices, so I have e.g. 3 Groups for every year with the same number of elements in it. Here's the bakground:
My employer wants a report for the sold objects in each year classified by upper, middle and lower price level. The biggest problem is, that I cannot define prices manually. What I have to do, is to order prices in each group (e.g. year), combine that with row_number(), devide row_count by 3, do a loop with step (row_number / 3) and get price at that position.
But getting those prices gives me the willies. I just discoverd the "MODEL" Clause. Do you think, that can help me solve the problem?
Maybe you have some tips.
Thanks!sorry, anything with "do a loop" in the requirements is not going to get a lot of responses on this site

we normally suggest that you restate your requirements in terms of what data you want, rather than how to get it (especially if how to get it involves cursors)|||nevermind, I think ntile is what i'm looking for ;-)|||The statistically correct way to do what you've described using sets is:CREATE TABLE #p (
yearID INT
, ID INT
, price MONEY
)

INSERT INTO #p (
yearID, ID, price
) SELECT 2000, 1, 100
UNION ALL SELECT 2000, 1, 200
UNION ALL SELECT 2000, 2, 100
UNION ALL SELECT 2000, 3, 500
UNION ALL SELECT 2000, 4, 100
UNION ALL SELECT 2001, 1, 100
UNION ALL SELECT 2001, 2, 300

SELECT a.yearID, a.ID, a.price
, 1 + Convert( INT, (SELECT Count(*)
FROM #p AS b
WHERE b.price < a.price)
/ (SELECT Count(*) / 3.0 FROM #p AS c))
FROM #p AS a

DROP TABLE #pNote that the conversion back to INT (forcing truncation) is very important from a statistical perspective.

-PatP|||Thank you Pat! I could make it with ntile

select year,pos,min(price) from
(
select
ntile(4) over(partition by year order by price) as pos,
year,
price
from
(select distinct id,year,price from #t ) as a
)as b
group by pos,year

This works fine, because I dont need to know anything about the data before. Anyway, this query doesn't look optimal. Is there a way to reduce selects?
Thx!