return a TABLE

hello all

I need a function with a return value TABLE...

this is not the problem..

but i need the returned table dynamic..

example:

i call the function getTable(schema_name, TableName)

the 1st value is the schema. the 2nd is the table i need...

now i must have a return value like this

select * from schema_name.TableName

is this possible? can i build by return value dynamically?

i tried a lot but nothing worked..

thx

greg

Give a look to CREATE FUNCTION in books online and you will find that in functions two things that you are not allowed to do are:

EXEC ( ' (any SQL statement)' ) EXEC aStoredProceduresql

Retriving Position In A Field

Hi All.
Is there a way to retrieve the position of a word, phrase or sign in a field?
For example, Field content is ABCDEFG1239/1002STJ
I would like to get the exact position of / which will be position 12.
Thank you.
Best regardsThe patindex function should work for you here. But, it will only give you the location of the first one. Syntax:
PATINDEX ( '%pattern%' , expression )|||I wonder what he difference is?

SELECT PATINDEX ( '%/%' , 'ABCDEFG1239/1002STJ' )
SELECT CHARINDEX ( '/','ABCDEFG1239/1002STJ' )|||PatIndex() allows SQL Server regular expressions. CharIndex() only allows literals.

-PatP|||Who was that masked man?|||Hello All.

Thank you for your reply. I couldn't thank of you earlier because I was away to Europe on a business trip.

I will try out your solutions today.

Once again. Thank you.

Best regards

Retrieving User-Defined Member Properties using PROPERTIES keyword

I am using an example from ‘SQL Server 2005 Books Online’, which explain how to retrieve User-Defined Member Properties.

Using the PROPERTIES Keyword to Retrieve User-Defined Member Properties:

DIMENSION PROPERTIES [Dimension.]Level.<Custom_Member_Property>

The PROPERTIES keyword appears after the set expression of the axis specification. For example, the following MDX query the PROPERTIES keyword retrieves the List Price and Dealer Price user-defined member properties and appears after the set expression that identifies the products sold in January:

SELECT

CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members,

[Measures].[Sales Amount]) ON COLUMNS,

NON EMPTY Product.Product.MEMBERS

DIMENSION PROPERTIES

Product.Product.[List Price],

Product.Product.[Dealer Price]ON ROWS

FROM [Adventure Works]

WHERE ([Date].[Month of Year].[January])

After running the above MDX query, I don’t see any [List Price] or [Dealer Price] and the result is exactly like running the following MDX query:

SELECT

CROSSJOIN([Ship Date].[Calendar].[Calendar Year].Members,

[Measures].[Sales Amount]) ON COLUMNS,

NON EMPTY Product.Product.MEMBERS ON ROWS

FROM [Adventure Works]

WHERE ([Date].[Month of Year].[January])

How can I retrieve User-Defined Member Properties?

Thanks,

Yones

I found the problem, which is related to the way data is returned after execution of an MDX query. It is returned differently in Analysis Services 2000 and 2005. For example using an XMLReader, elements names are returned as follow:

AS 2000:

clXmlReader.Name:"List Price"

clXmlReader.value:"List Price value"

clXmlReader.Name:"Dealer Price"

clXmlReader.value:"Dealer Price value"

AS 2005:

clXmlReader.Name:"_x005B_ Product _x005D_._x005B_Product_x005D_._x005B_Product_x005D_._x005B_ List Price _x005D_"

clXmlReader.value:"List Price value"

clXmlReader.Name:"_x005B_ Product _x005D_._x005B_ Product _x005D_._x005B_ Product _x005D_._x005B_ Dealer Price _x005D_"

clXmlReader.value:"Dealer Price value"

Retrieving user defined Role name

Is there a System stored procedure that gives me the Role in which a user is in. For example I execute this procedure, give the user as parameter an that gives me back the Role the user is in. It has to be said that this is a user defined role, I got three of them, HR, Employee, Approver.

Greetings,
GodofredoIs it not clear? I just want to retrieve a user defined Role

Greets,
geoff|||I'd use sp_helpuser (http://msdn.microsoft.com/library/en-us/tsqlref/ts_sp_help_45o2.asp).

-PatP

Retrieving the error message

In Transact-SQL you can get the error number from @.@.Error, but is there
anything to get the message?
Here's an example that forces a FK error and tries to get a message for it
from the sysmessages table. The message contains all the parameters holders
instead of their values though. Anybody have any ideas?
DECLARE @.intID INT
DECLARE @.intError INT
DECLARE @.strErrorMessage VARCHAR(4000)
UPDATE OrderDetails SET OrderID = 0 WHERE OrderID = 42
SELECT @.intError = @.@.ERROR
IF @.intError <> 0
BEGIN
SELECT @.strErrorMessage = description FROM master.dbo.sysmessages WHERE
error = @.intError
PRINT 'Error Number:' + CAST(@.intError AS VARCHAR)
PRINT 'Error Message:' + @.strErrorMessage
ENDPlease ignore this post. I triple-posted it by accident.

Retrieving records within an index range, the nth record?

if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?

Possible? How can I do it?

When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?

I'm using SQL 2005 Express, not SQL 2000.if I create an index for a table with some records, do you think I can retrieve records in a giving range? for example, the 5th to 10th records?
Create index and schedule update statistics according to your requirement...

Use comparison operator for retrieving desired data.

i.e. Like, Between, Not Between etc.

When we insert data at the table, would the index in sequential order? How would the index be created for new inserted records?
Use clustered and non clustered index considering your needs.

Indexes are for arranging, sorting & fast retrieval of the data. Each time you don't need to create index when you insert a row, just schedule update statistics job or set auto update statistics.

Explore Books OnLine (From query analyzer -> Help Menu) for more information.|||how do we include index as a criteria when we use normal SQL query?|||how do we include index as a criteria when we use normal SQL query?
You don't need to do such thing, SQL Server will do it for you...|||after some research, i think it's easier for me to insert row_number() into the table instead of using index. What do you think?|||Read documents / books (rather get some knowledge) before making any changes...|||Data in a relational database has no inherent order.
Why do you think you need to add a rownumber column?

There are several ways to get a "page" or "range" of records from a table. Here is one:

select top 5 *
from
(select top 10 *
from [YourTable]
order by [YourColumn] asc) Subquery
order by [YourColumn] desc

You should not be relying on the concept of a "row number"|||this is for a particular case to generate rigid report using SQL 2005.

I need the output to be in the right order in my control, and because there is headers and footers involved, I got no choice but to fix them in certain special order.

the output is to an excel spreadsheet.|||So throw an ORDER BY clause into your query.

If you want the data to be ordered according to the way it was entered, then use a datetime column to record the entry date.|||Order by cannot work without row_number. I have too many identical rows.

Entry date is not accurate as the time unit used by SQL 2005 is not small enough.|||Then use an Identity column.|||unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.|||You're not listening...|||You can also use temporary table to process desired request in memory, rather to store in table permanently,
try just like this:

SELECT ROWID=IDENTITY(int,1,1) , Col1
INTO #TempTable FROM
<UrTable List and Where Clause>

and then retrieve from Temporary table, it will save ur time, disk space and locking issues on underlying table.

--Riaz

unfortunately, there is no identity column, because I use this to generate a rigid report. the only identity column is the row number I created as part of the table.|||it will save ur time, disk space and locking issues on underlying table.
Dunno about time but this will use more disk space than blindman's query (temp tables are not held in memory but written to tempdb) and will lock tempdb while it runs (this is due to the "select into" bit).

You can use the OVER clause if you are really eager to use row_number().

SELECT *
FROM--Derived TABLE - numbering rows
(SELECT *
, ROW_NUMBER() OVER (ORDERBY my_unique_column ASC) AS rn
FROM dbo.MyTable) AS der_t
WHERE rn BETWEEN 5 AND 10

Retrieving grouped data

Hi,
I have a table that contains a simple collection of rows, the rows have the
following fields...
URN, Year, Period, Cost, Value
As an example, the data is as follows...
2005|08|089.32|123.45
2005|10|056.68|045.68
2004|10|156.32|068.23
2005|11|123.56|548.12
2005|11|078.23|569.12
2005|7|078.25|875.657
2004|7|009.50|320.512
What I'd like to achieve is a stored procedure that retrieves the data in
the following...
Period | Sum Of Cost For A Year | Sum Of Value For A Year | Sum Of Cost For
Year -1 | Sum Of Value For Year - 1
I've got the following...
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS OFF
GO
ALTER PROCEDURE watson_GetLitresReporting_Monthly
@.intYear As INT
AS
SELECT NLPeriod,
NLYear,
LineValue,
Litres
INTO #thisYear
FROM dbo.Watson_FinsData
WHERE NLYear = @.intYear
GROUP BY NLYear,
NLPeriod,
LineValue,
Litres
SELECT NLPeriod,
NLYear,
LineValue,
Litres
INTO #lastYear
FROM dbo.Watson_FinsData
WHERE NLYear = @.intYear - 1
GROUP BY NLYear,
NLPeriod,
LineValue,
Litres
SELECT #thisYear.NLPeriod,
SUM(#thisYear.LineValue) AS Value,
SUM(#thisYear.Litres) AS Quatity,
SUM(#lastYear.LineValue) AS LastYear,
SUM(#lastYear.Litres) AS Qty
FROM #thisYear
FULL OUTER JOIN #lastYear
ON #thisYear.NLPeriod = #lastYear.NLPeriod
GROUP BY #thisYear.NLYear,
#thisYear.NLPeriod
ORDER BY #thisYear.NLYear,
#thisYear.NLPeriod DESC
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
But that doesn't work at all.
Could someone explain what I'm doing wrong please?Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, data types, etc. in
your schema are. Sample data is also a good idea, along with clear
specifications. It is very hard to debug code when you do not let us
see it.
Also, what is a URN? To me it, it is a vase with ashes of loved one in
it.|||On Thu, 24 Nov 2005 08:58:08 -0800, JMH wrote:

>Hi,
>I have a table that contains a simple collection of rows, the rows have the
>following fields...
>URN, Year, Period, Cost, Value
>As an example, the data is as follows...
>2005|08|089.32|123.45
>2005|10|056.68|045.68
>2004|10|156.32|068.23
>2005|11|123.56|548.12
>2005|11|078.23|569.12
>2005|7|078.25|875.657
>2004|7|009.50|320.512
>What I'd like to achieve is a stored procedure that retrieves the data in
>the following...
>Period | Sum Of Cost For A Year | Sum Of Value For A Year | Sum Of Cost For
>Year -1 | Sum Of Value For Year - 1
Hi JMH,
Try if this works:
SELECT COALESCE(a.Period, b.Period) AS Period,
SUM(a.Cost) AS Cost2005,
SUM(a.Value) AS Value2005,
SUM(b.Cost) AS Cost2004,
SUM(b.Value) AS Value2004
FROM (SELECT Period, Cost, Value
FROM dbo.Watson_FinsData
WHERE Year = 2005) AS a
FULL OUTER JOIN (SELECT Period, Cost, Value
FROM dbo.Watson_FinsData
WHERE Year = 2004) AS b
ON b.Period = a.Period
GROUP BY COALESCE(a.Period, b.Period)
(untested - see www.aspfaq.com/5006 if you prefer a tested reply)
Best, Hugo
--
(Remove _NO_ and _SPAM_ to get my e-mail address)|||--CELKO-- wrote:
> Also, what is a URN? To me it, it is a vase with ashes of loved one in
> it.
What exactly is wrong with this name? (I, indeed, never allowed
a thought that you are not aware of the very well known abbreviation.)

Retrieving error messages

In Transact-SQL you can get the error number from @.@.Error, but is there
anything to get the message?
Here's an example that forces a FK error and tries to get the message from
sysmessages. As you will see the error message doesn't contain the
parameter values. Anybody have any ideas? I'm still using SQL Server 2000,
so I can't use the new 2005 error functions.
DECLARE @.intID INT
DECLARE @.intError INT
DECLARE @.strErrorMessage VARCHAR(4000)
UPDATE OrderDetails SET OrderID = 0 WHERE OrderID = 42
SELECT @.intError = @.@.ERROR
IF @.intError <> 0
BEGIN
SELECT @.strErrorMessage = description FROM master.dbo.sysmessages WHERE
error = @.intError
PRINT 'Error Number:' + CAST(@.intError AS VARCHAR)
PRINT 'Error Message:' + @.strErrorMessage
ENDPlease ignore this post. I triple-posted it by accident.

Retrieving Data from next row

I have a little problem. An example of the data I work with is pasted below.
Qry_TestCtrack NAME STATUSTEXT ASSEMBLED LastAssembled
Bell B30 Dumper 1st Startup 03/03/2005 07:17:29 03/03/2005 07:17:29
Bell B30 Dumper Normal 03/03/2005 07:18:29 03/03/2005 07:18:29
Bell B30 Dumper Normal 03/03/2005 07:19:29 03/03/2005 07:19:29
Bell B30 Dumper Normal 03/03/2005 07:20:29 03/03/2005 07:20:29
Bell B30 Dumper Normal 03/03/2005 07:21:29 03/03/2005 07:21:29
Bell B30 Dumper Normal 03/03/2005 07:22:29 03/03/2005 07:22:29
Bell B30 Dumper Normal 03/03/2005 07:23:29 03/03/2005 07:23:29
Bell B30 Dumper Ignition off 03/03/2005 07:24:06 03/03/2005 07:24:06
The Query I'm making needs to give me the next row's ASSEMBLED as the
Previous row's LastAssembled so that I can calculate the time that was
carried out on that action. Anyone that can give me a helping hand? ^^
Thanks ^^Hi
Look at below example
create table tblConnection
(
StartTimeCon datetime not null,
EndTimeCon datetime not null
)
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:10','20000610 10:10')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
10:10','20000610 20:22')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
20:23','20000610 20:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
20:25','20000610 21:00')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:00','20000610 21:15')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:16','20000610 21:25')
insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
21:25','20000610 21:35')
SELECT
DISTINCT StartTimeCon
FROM tblConnection AS S1
WHERE ISNULL(
DATEDIFF(
minute,
(SELECT MAX(EndTimeCon)
FROM tblConnection AS S2
WHERE S2.EndTimeCon <= S1.StartTimeCon),S1.StartTimeCon),0) = 0
"Nightshade" <Nightshad3@.telkomsa.net> wrote in message
news:d18otn$1l6h$1@.newsreader02.ops.uunet.co.za...
> I have a little problem. An example of the data I work with is pasted
below.
> Qry_TestCtrack NAME STATUSTEXT ASSEMBLED LastAssembled
> Bell B30 Dumper 1st Startup 03/03/2005 07:17:29 03/03/2005 07:17:29
> Bell B30 Dumper Normal 03/03/2005 07:18:29 03/03/2005 07:18:29
> Bell B30 Dumper Normal 03/03/2005 07:19:29 03/03/2005 07:19:29
> Bell B30 Dumper Normal 03/03/2005 07:20:29 03/03/2005 07:20:29
> Bell B30 Dumper Normal 03/03/2005 07:21:29 03/03/2005 07:21:29
> Bell B30 Dumper Normal 03/03/2005 07:22:29 03/03/2005 07:22:29
> Bell B30 Dumper Normal 03/03/2005 07:23:29 03/03/2005 07:23:29
> Bell B30 Dumper Ignition off 03/03/2005 07:24:06 03/03/2005 07:24:06
>
> The Query I'm making needs to give me the next row's ASSEMBLED as the
> Previous row's LastAssembled so that I can calculate the time that was
> carried out on that action. Anyone that can give me a helping hand? ^^
> Thanks ^^
>|||Thank you for the response. I think me previous example was a bit confusing.
In my previous example I had the Column LastAssembled. That was my failure
to get it to show as I want it.
This is what I have to work with.
Qry_TestCtrack NAME STATUSTEXT ASSEMBLED
Bell B30 Dumper 1st Startup 03/03/2005 07:17:29
Bell B30 Dumper Normal 03/03/2005 07:18:29
Bell B30 Dumper Normal 03/03/2005 07:19:29
Bell B30 Dumper Normal 03/03/2005 07:20:29
Bell B30 Dumper Normal 03/03/2005 07:21:29
Bell B30 Dumper Normal 03/03/2005 07:22:29
Bell B30 Dumper Normal 03/03/2005 07:23:29
Bell B30 Dumper Ignition off 03/03/2005 07:24:06
Thus There's only 1 timestamp per event. But the one timestamp to the next
is the lenght of the action. Thus what I'm tyring, is to create another
tale, or just a function, wich would either place the 2nd line's Assembled
next to the 1s't lines assembled, so I can work out the difference between
them, or even directly subtract the first line from the 2nd line. End of the
day I need to caculate how much time was spent on each action, so I can just
add the totals of the actions.
Thanks again ^^
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:eMNawEgKFHA.3928@.TK2MSFTNGP09.phx.gbl...
> Hi
> Look at below example
> create table tblConnection
> (
> StartTimeCon datetime not null,
> EndTimeCon datetime not null
> )
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:10','20000610 10:10')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 10:10','20000610 20:22')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 20:23','20000610 20:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 20:25','20000610 21:00')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:00','20000610 21:15')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:16','20000610 21:25')
> insert into tblConnection(StartTimeCon,EndTimeCon)va
lues ('20000610
> 21:25','20000610 21:35')
>
> SELECT
> DISTINCT StartTimeCon
> FROM tblConnection AS S1
> WHERE ISNULL(
> DATEDIFF(
> minute,
> (SELECT MAX(EndTimeCon)
> FROM tblConnection AS S2
> WHERE S2.EndTimeCon <= S1.StartTimeCon),S1.StartTimeCon),0) = 0
>
>
> "Nightshade" <Nightshad3@.telkomsa.net> wrote in message
> news:d18otn$1l6h$1@.newsreader02.ops.uunet.co.za...
> below.
>|||Hi
Does that mean the query I posted does not work?
Please post DDL + expected result.
"Nightshade" <Nightshad3@.telkomsa.net> wrote in message
news:d18r49$1lbg$1@.newsreader02.ops.uunet.co.za...
> Thank you for the response. I think me previous example was a bit
confusing.
> In my previous example I had the Column LastAssembled. That was my failure
> to get it to show as I want it.
> This is what I have to work with.
> Qry_TestCtrack NAME STATUSTEXT ASSEMBLED
> Bell B30 Dumper 1st Startup 03/03/2005 07:17:29
> Bell B30 Dumper Normal 03/03/2005 07:18:29
> Bell B30 Dumper Normal 03/03/2005 07:19:29
> Bell B30 Dumper Normal 03/03/2005 07:20:29
> Bell B30 Dumper Normal 03/03/2005 07:21:29
> Bell B30 Dumper Normal 03/03/2005 07:22:29
> Bell B30 Dumper Normal 03/03/2005 07:23:29
> Bell B30 Dumper Ignition off 03/03/2005 07:24:06
>
> Thus There's only 1 timestamp per event. But the one timestamp to the next
> is the lenght of the action. Thus what I'm tyring, is to create another
> tale, or just a function, wich would either place the 2nd line's Assembled
> next to the 1s't lines assembled, so I can work out the difference between
> them, or even directly subtract the first line from the 2nd line. End of
the
> day I need to caculate how much time was spent on each action, so I can
just
> add the totals of the actions.
> Thanks again ^^
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:eMNawEgKFHA.3928@.TK2MSFTNGP09.phx.gbl...
0
07:17:29
>

retrieves the information about the pipeline components

Dear Experts,

I can look the values of the proprieties in each PipelineComponentInfo, for example:

ComponentType: Transform
CreationName: DTSTransform.Merge.1
Description: Merge Transformation
FileName: C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\TxMerge.dll
FileNameVersionString: 2000.90.1049.0
IconFile: C:\Program Files\Microsoft SQL Server\90\DTS\PipelineComponents\TxMerge.dll
IconResource: -201
ID: {08AE886A-4124-499C-B332-16E3299D225A}
Name: Merge
NoEditor: False
ShapeProgID:
UITypeName: Microsoft.DataTransformationServices.....

but I don't know what means the proprieties: FileName, FileNameVersionString, IconFile, IconResource, NoEdit, ShapeProgID and UITypeName...

Can anyone helps Me?

Thanks

Francesco

What information are you looking for.

The properties are those used by the runtime and designer to use the component.

|||

I wanna know the meaning of the words:

IconFile, IconResource, NoEdit, ShapeProgID and UITypeNam... .

I already know which are the values of these informations

|||

Please see the PipelineComponentInfo class documentation in the programming reference. Use the BOL Index to locate it quickly.

-Doug

|||

Good IDEA!

This is link

Sorry, but in this period I'm tense and very tired...

Thanks a lot

Francesco

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the week
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like Week('01/08/06') to return 2. I cannot find
such a thing.
Thanks.
use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/
|||No, don't use DATEPART for weeknumbers. The function doesn't calculate week numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for weeknumbers. The function doesn't calculate
> week numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
>
|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||you don't need to, that was just to show you 2 values
The ISOWeek ifunction can be found here
http://msdn.microsoft.com/library/de...reate_7r1l.asp
http://sqlservercode.blogspot.com/
|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different week numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the week
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like Week('01/08/06') to return 2. I cannot find
such a thing.
Thanks.
use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/
|||No, don't use DATEPART for weeknumbers. The function doesn't calculate week numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for weeknumbers. The function doesn't calculate
> week numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
>
|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegr oups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>
|||you don't need to, that was just to show you 2 values
The ISOWeek ifunction can be found here
http://msdn.microsoft.com/library/de...reate_7r1l.asp
http://sqlservercode.blogspot.com/
|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different week numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the w
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like W('01/08/06') to return 2. I cannot find
such a thing.
Thanks.use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/|||No, don't use DATEPART for wnumbers. The function doesn't calculate w
numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for wnumbers. The function doesn't calculate
> w numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
>|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||you don't need to, that was just to show you 2 values
The ISOW ifunction can be found here
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
http://sqlservercode.blogspot.com/|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different w numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve week # of the year

Is there a SQL command I can use in a select statement to retrieve the week
of the year. For example a command of Year('01/08/06') would return 2006.
I am looking for a command like Week('01/08/06') to return 2. I cannot find
such a thing.
Thanks.use datepart
select datepart(wk,('01/08/06')),datepart(wk,getdate())
http://sqlservercode.blogspot.com/|||No, don't use DATEPART for weeknumbers. The function doesn't calculate week
numbers correctly.
Install the ISOWEEK function found in Books Online instead.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||Excuse my ingorance, but how do you install the ISOWEEK function? I can't
find much info on it.
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:%237S53eVPGHA.2924@.TK2MSFTNGP11.phx.gbl...
> No, don't use DATEPART for weeknumbers. The function doesn't calculate
> week numbers correctly. Install the ISOWEEK function found in Books Online
> instead.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "SQL" <denis.gobo@.gmail.com> wrote in message
> news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
>|||Why do I have to use the datepart(wk,getdate()) as part of the syntax?
"SQL" <denis.gobo@.gmail.com> wrote in message
news:1141234454.350062.298590@.p10g2000cwp.googlegroups.com...
> use datepart
> select datepart(wk,('01/08/06')),datepart(wk,getdate())
>
> http://sqlservercode.blogspot.com/
>|||you don't need to, that was just to show you 2 values
The ISOWeek ifunction can be found here
http://msdn.microsoft.com/library/d...r />
_7r1l.asp
http://sqlservercode.blogspot.com/|||Do you have Books Online? It's in there, I believe it's one of the CREATE
FUNCTION examples.
Also see http://www.aspfaq.com/2519 for a different approach, e.g. if you
have a different week numbering system than ISO or SQL Server's default
behavior...

> Excuse my ingorance, but how do you install the ISOWEEK function? I can't
> find much info on it.

Retrieve values from child table

Hello there,

I need to get the last value (status) from a child table. I try to simplify the problem with the following example.

Create Table Users
(
UserId int,
Lastname nvarchar(50)
)

Create Table UserStatus
(
UserId int,
Date datetime,
StatusId int
)

Create Table Status
(
StatusId int
Status nvarchar(50)
)

A user will go through all Status one by one. (1) Registered -> (2) In progress -> (3) authorized.
Now I want to know which users are in progress (2) but a simple select statement like:

Select LastName from Users Inner Join Users.UsersId = UserStatus.UserId Where UsersStatus.StatusId = 2

Will not return the wanted records because all authorized Users have been in this status.

I hope you understand the problem and can help me out.

Thx in advance.

Etinuzso you want the last names of users that stopped at status 2?

Select Users.LastName From Users Inner Join (Select Max(StatusID) As MaxStatusID, UserID, UserStatus UserID) As LimitedStatus On Users.UserID = LimitedStatus.UserID WHERE MaxStatusID = 2

Or something to that effect.|||Thx KraGIE, i suppose this is the right direction. Only the values are not in a specific order (and can not be set this way). So the example was not right.
So a user can be Authorized (3) and later set to In progress (2). However in the table there is a ID.

Create Table UserStatus
(
Id int, (identifier)
UserId int,
Date datetime,
StatusId int
)

At this moment I havent figured out how to use

Select Users.LastName From Users Inner Join (Select Max(ID) As MaxID, UserID, StatusId From UserStatus Group BY USerId, StatusId) As LimitedStatus On Users.UserID = LimitedStatus.UserID WHERE StatusID = 2|||I found my solution

SELECT
u.LastName
FROM
Users u
JOIN UserStatus us
ON u.UserId = us.UserId
JOIN
(SELECT UserId, MAX([Date]) AS last_date
FROM UserStatus
GROUP BY UserId ) AS last_status
ON u.UserId = last_status.UserId
AND us.[Date] = last_status.last_date
WHERE
us.StatusId = 2

retrieve the value that occurs the most

Hi,

I am trying to retrieve the value that occurs the most at a dimension level. For example, I have to analyze the product type mostly used in a specific target segment by number of products.

The mathematical function normally used is MODE. However, in AS2005 there is not such function. I tried to use TopCount but it is not as easy as I thought.

Any suggestion?

Thanks!

As far as I know, TopCount should work.

This is a sample from Adventure Works:

with

set [TopCustomers] as

TOPCOUNT(

[Dim Customer].[Dim Customer].[Dim Customer].members,

1,

[Measures].[Sales Amount]

)

select {[Measures].[Sales Amount]} on 0,

[TopCustomers] on 1

from [Adventure Works DW]

Hope this helps,

Santi

|||I think TopCount would only get you a mode if you were to use it against a count measure. I am guessing that you want to find out which member from the product dimension has the most transactions against it for a given set of criteria. Depending on the granularity of your dimensions you may have difficulty doing this without having some sort of count measure.

Retrieve the error description

In Transact-SQL you can get the error number from @.@.Error, but is there
anything to get the message?
Here's an example that forces a FK error and tries to get a message for it
from the sysmessages table. The message contains all the parameters holders
instead of their values though. Anybody have any ideas?
DECLARE @.intID INT
DECLARE @.intError INT
DECLARE @.strErrorMessage VARCHAR(4000)
UPDATE OrderDetails SET OrderID = 0 WHERE OrderID = 42
SELECT @.intError = @.@.ERROR
IF @.intError <> 0
BEGIN
SELECT @.strErrorMessage = description FROM master.dbo.sysmessages WHERE
error = @.intError
PRINT 'Error Number:' + CAST(@.intError AS VARCHAR)
PRINT 'Error Message:' + @.strErrorMessage
ENDIn SQL 2005, there is the ERROR_MESSAGE() function that can be used inside a
TRY - CATCH block.
In SQL 2000, the only way that I know of to get the actual error message is
by using DBCC OUTPUTBUFFER. It's ugly, but it's the only way I know.
--
"Jack" wrote:

> In Transact-SQL you can get the error number from @.@.Error, but is there
> anything to get the message?
> Here's an example that forces a FK error and tries to get a message for it
> from the sysmessages table. The message contains all the parameters holder
s
> instead of their values though. Anybody have any ideas?
> DECLARE @.intID INT
> DECLARE @.intError INT
> DECLARE @.strErrorMessage VARCHAR(4000)
> UPDATE OrderDetails SET OrderID = 0 WHERE OrderID = 42
> SELECT @.intError = @.@.ERROR
> IF @.intError <> 0
> BEGIN
> SELECT @.strErrorMessage = description FROM master.dbo.sysmessages WHERE
> error = @.intError
> PRINT 'Error Number:' + CAST(@.intError AS VARCHAR)
> PRINT 'Error Message:' + @.strErrorMessage
> END
>
>|||Mark Williams (MarkWilliams@.discussions.microsoft.com) writes:
> In SQL 2000, the only way that I know of to get the actual error message
> is by using DBCC OUTPUTBUFFER. It's ugly, but it's the only way I know.
Yes, that was really ugly! But since the standard answer is "you can't",
this is a leap forward. I will have to try this.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I'm not entirely sure of its usefullness, because DBCC OUTPUTBUFFER doesn't
actually show the error message until after the statement that cause it
completes. You'd have to check @.@.ERROR, dump the contents of DBCC
OUTPUTBUFFER into a temp table, then possibly use a cursor to concatenate
each row into a single text string (whew!).
The question is, will a batch terminate before you get a chance to do
anything usefull with the output.
"Erland Sommarskog" wrote:

> Mark Williams (MarkWilliams@.discussions.microsoft.com) writes:
> Yes, that was really ugly! But since the standard answer is "you can't",
> this is a leap forward. I will have to try this.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>

Retrieve records separated by spaces

Hello
I want to retrieve some records separated by spaces. Do you have any idea?
For example,
select col from T for xml path(''), root('x')
You get
<x>
<col>abc</col>
<col>def</col>
......
</x>
However I want to get,
<x>abc def ghi ... </x>
After post I got an idea, but looking for better one.
select @.x=(select col from T for xml path(''))
select @.x.query('
for $a in /col
return (concat(/$a/text(), " "))
')
for xml path
Any idea will be appreciated.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%233fljmVFHHA.4652@.TK2MSFTNGP04.phx.gbl...
> Hello
> I want to retrieve some records separated by spaces. Do you have any idea?
> For example,
> select col from T for xml path(''), root('x')
> You get
> <x>
> <col>abc</col>
> <col>def</col>
> .....
> </x>
> However I want to get,
> <x>abc def ghi ... </x>
>
|||This should be more elegant and performing (not tested - from memory)
select col as "data()" from T for xml path(''), root('x')
Best regards,
Eugene
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23aSIkxVFHHA.2268@.TK2MSFTNGP06.phx.gbl...
> After post I got an idea, but looking for better one.
> select @.x=(select col from T for xml path(''))
> select @.x.query('
> for $a in /col
> return (concat(/$a/text(), " "))
> ')
> for xml path
> Any idea will be appreciated.
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:%233fljmVFHHA.4652@.TK2MSFTNGP04.phx.gbl...
>
|||Thanks Eugene.
It worked.
"Eugene Kogan [MSFT]" <eugene.kogan@.online.microsoft.com> wrote in message
news:upkjKm$HHHA.4068@.TK2MSFTNGP03.phx.gbl...
> This should be more elegant and performing (not tested - from memory)
> select col as "data()" from T for xml path(''), root('x')
> Best regards,
> Eugene
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:%23aSIkxVFHHA.2268@.TK2MSFTNGP06.phx.gbl...
>

Retrieve records separated by spaces

Hello
I want to retrieve some records separated by spaces. Do you have any idea?
For example,
select col from T for xml path(''), root('x')
You get
<x>
<col>abc</col>
<col>def</col>
.....
</x>
However I want to get,
<x>abc def ghi ... </x>After post I got an idea, but looking for better one.
select @.x=(select col from T for xml path(''))
select @.x.query('
for $a in /col
return (concat(/$a/text(), " "))
')
for xml path
Any idea will be appreciated.
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%233fljmVFHHA.4652@.TK2MSFTNGP04.phx.gbl...
> Hello
> I want to retrieve some records separated by spaces. Do you have any idea?
> For example,
> select col from T for xml path(''), root('x')
> You get
> <x>
> <col>abc</col>
> <col>def</col>
> .....
> </x>
> However I want to get,
> <x>abc def ghi ... </x>
>|||This should be more elegant and performing (not tested - from memory)
select col as "data()" from T for xml path(''), root('x')
Best regards,
Eugene
"Han" <hp4444@.kornet.net.korea> wrote in message
news:%23aSIkxVFHHA.2268@.TK2MSFTNGP06.phx.gbl...
> After post I got an idea, but looking for better one.
> select @.x=(select col from T for xml path(''))
> select @.x.query('
> for $a in /col
> return (concat(/$a/text(), " "))
> ')
> for xml path
> Any idea will be appreciated.
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:%233fljmVFHHA.4652@.TK2MSFTNGP04.phx.gbl...
>|||Thanks Eugene.
It worked.
"Eugene Kogan [MSFT]" <eugene.kogan@.online.microsoft.com> wrote in message
news:upkjKm$HHHA.4068@.TK2MSFTNGP03.phx.gbl...
> This should be more elegant and performing (not tested - from memory)
> select col as "data()" from T for xml path(''), root('x')
> Best regards,
> Eugene
> "Han" <hp4444@.kornet.net.korea> wrote in message
> news:%23aSIkxVFHHA.2268@.TK2MSFTNGP06.phx.gbl...
>

Retrieve One Row at a time

Hi,

I am going to be difficult here... How do I retrieve one row at a
time from a table without using a cursor?

For example, I have a table with 100 rows. I want to retrieve the
data from row 1, do some stuff to it and send it on to anther table,
then I want to grab
row 2, do some stuff to it and send it to another table.

Here is how I am envisioning it:

WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
rowcount)
BEGIN
SELECT * FROM [TABLE] BUT ONLY ONE ROW
... MANIPULATE THE DATA
INSERT into another table
END

Other notes, I am using SQL Sever 2000...
Thanks and in advance and as always the help is greatly appreciated.

Regards,

CLRI don't know why you don't want to use a cursor which is probably the most
suitable means to solve your problem. But anyway, you have some other
options like these:

1. Add a flag to your table. After proccessing each record set the flag and
select the next nonprocessed record (using select top 1).

2. Copy all the records you want into a temporary table and again using
selectp top 1 read them one by one and delete them after processing.

3. Use a temporary table as a list of processed records, after processing
each record add its key to this list and select next record where its key
does not belong to this list.

If you give us more information about what you are exactly looking for and
what your problem is, you'll have a better chance to get the solution.

Shervin

"Chris" <chris@.dagran.com> wrote in message
news:736fadb1.0309301643.572f3730@.posting.google.c om...
> Hi,
> I am going to be difficult here... How do I retrieve one row at a
> time from a table without using a cursor?
> For example, I have a table with 100 rows. I want to retrieve the
> data from row 1, do some stuff to it and send it on to anther table,
> then I want to grab
> row 2, do some stuff to it and send it to another table.
> Here is how I am envisioning it:
> WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
> rowcount)
> BEGIN
> SELECT * FROM [TABLE] BUT ONLY ONE ROW
> ... MANIPULATE THE DATA
> INSERT into another table
> END
> Other notes, I am using SQL Sever 2000...
> Thanks and in advance and as always the help is greatly appreciated.
> Regards,
> CLR|||Chris (chris@.dagran.com) writes:
> For example, I have a table with 100 rows. I want to retrieve the
> data from row 1, do some stuff to it and send it on to anther table,
> then I want to grab
> row 2, do some stuff to it and send it to another table.
> Here is how I am envisioning it:
> WHILE arg1 < arg2 {arg1 is my initial row, arg2 would be by total
> rowcount)
> BEGIN
> SELECT * FROM [TABLE] BUT ONLY ONE ROW
> ... MANIPULATE THE DATA
> INSERT into another table
> END
> Other notes, I am using SQL Sever 2000...
> Thanks and in advance and as always the help is greatly appreciated.

SELECT TOP 1 @.key = keycol, @.var1 = col1, @.var2 = col2'
FROM tbl
WHERE keycol > @.key
ORDER BY keycol

If you have a multi-column, you can still do this, but logic becomes
hairier.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||I want to thank you both for your answers they have helped
tremendously. We are going to use a cursor for our problem as well, I
just wanted another way of handling what we are triyng to accomplish.
We have a table with over a million rows, which from one row we will
query about 5 other tables to extract more information which will be
sent to a new table, then we grab the next row and so on and so forth.
We want to try using a cursor and anther method to see which way
would be more CPU friendly. I feel it doesn't really matter which way
we go, they both will take over my computer. Thanks though for your
responses, it has helped us out a lot!

Regards,

CLR|||Chris (chris@.dagran.com) writes:
> I want to thank you both for your answers they have helped
> tremendously. We are going to use a cursor for our problem as well, I
> just wanted another way of handling what we are triyng to accomplish.
> We have a table with over a million rows, which from one row we will
> query about 5 other tables to extract more information which will be
> sent to a new table, then we grab the next row and so on and so forth.

A million rows iteratively? That could take a couple of days! Sometimes
this can be justified, if it's sort of a one time operation. (Actually,
I was recently involved in writing a task that took 3 days to complete.)

But if you can find a set-based operation, you can win lots of
performance.

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

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