Query / function ?

Query/Function Question
I have a retail issue where I store sizes in a table per item. The table
looks like:
PO #
Item #
Color
Size
Size Equiv
What I am trying to do is get a comma delimited list of sizes AND size Equiv
per po/item. The sizes AND Size Equiv need to be a unique list (group by)
excluding color
Example:
PO Item # Size Size Equiv
123 1 2, 4, 6, 8, 10 12, 14, 16, 18, 20
123 2 S, M, L, XL XS, S, M, L
Now the question:
I need to create a SQL query that builds the about list? Is there a way to
do a query call or must I use a stored proc curser to loop thru a select
query?
Thanks in advance,
Stewart Rogershttp://www.aspfaq.com/2529
Please post DDL, sample data and desired results.
See http://www.aspfaq.com/5006 for info.
"Datasort" <Datasort@.discussions.microsoft.com> wrote in message
news:1942D589-AD0C-44D9-B285-E6CB7682F208@.microsoft.com...
> Query/Function Question
> I have a retail issue where I store sizes in a table per item. The table
> looks like:
> PO #
> Item #
> Color
> Size
> Size Equiv
> What I am trying to do is get a comma delimited list of sizes AND size
Equiv
> per po/item. The sizes AND Size Equiv need to be a unique list (group by)
> excluding color
> Example:
> PO Item # Size Size Equiv
> 123 1 2, 4, 6, 8, 10 12, 14, 16, 18, 20
> 123 2 S, M, L, XL XS, S, M, L
> Now the question:
> I need to create a SQL query that builds the about list? Is there a way
to
> do a query call or must I use a stored proc curser to loop thru a select
> query?
>
> Thanks in advance,
> Stewart Rogers
>|||See if this helps:
http://groups-beta.google.com/group...
5bf366dd9e73e
AMB
"Datasort" wrote:

> Query/Function Question
> I have a retail issue where I store sizes in a table per item. The table
> looks like:
> PO #
> Item #
> Color
> Size
> Size Equiv
> What I am trying to do is get a comma delimited list of sizes AND size Equ
iv
> per po/item. The sizes AND Size Equiv need to be a unique list (group by)
> excluding color
> Example:
> PO Item # Size Size Equiv
> 123 1 2, 4, 6, 8, 10 12, 14, 16, 18, 20
> 123 2 S, M, L, XL XS, S, M, L
> Now the question:
> I need to create a SQL query that builds the about list? Is there a way t
o
> do a query call or must I use a stored proc curser to loop thru a select
> query?
>
> Thanks in advance,
> Stewart Rogers
>|||If you want to get it done quickly and easily (minimal sql coding)
and don't care about the 'how', check out the RAC utility for S2k.
See the @.concatenate operator and you'll know what I mean:)
RAC and QALite @.
www.rac4sql.net
"Datasort" <Datasort@.discussions.microsoft.com> wrote in message
news:1942D589-AD0C-44D9-B285-E6CB7682F208@.microsoft.com...
> Query/Function Question
> I have a retail issue where I store sizes in a table per item. The table
> looks like:
> PO #
> Item #
> Color
> Size
> Size Equiv
> What I am trying to do is get a comma delimited list of sizes AND size
> Equiv
> per po/item. The sizes AND Size Equiv need to be a unique list (group by)
> excluding color
> Example:
> PO Item # Size Size Equiv
> 123 1 2, 4, 6, 8, 10 12, 14, 16, 18, 20
> 123 2 S, M, L, XL XS, S, M, L
> Now the question:
> I need to create a SQL query that builds the about list? Is there a way
> to
> do a query call or must I use a stored proc curser to loop thru a select
> query?
>
> Thanks in advance,
> Stewart Rogers
>sql

Query - need help using the IN function/statement

Hello all,
I am trying to find the instances in a field containg specific keywords or
strings of information. My table name is History, and my field name is
Notes. So what I am trying to do is find every record where History.Notess
conatins;
'chrom' or 'cell' or 'lab'
I think I need the IN functin as opposed to using a bunch of OR statements.
The OR statments work, but there are so many different keywords/strings,
that it is a real mess to enter all of the information.
Thank you for your help
JohnJohn,
You can put all the keywords in a table variable / temporary table /
permanent table a use:
select distinct notes
from history as h inner join t1 on h.notes like '%' + t1.keyword + '%'
Example:
use northwind
go
create table t1 (
c1 varchar(255)
)
go
create table t2 (
keyword varchar(25) not null unique
)
go
insert into t1 values('microsoft')
insert into t1 values('oracle')
insert into t1 values('microfocus')
go
insert into t2 values('micro')
insert into t2 values('of')
go
select distinct
t1.c1
from
t1
inner join
t2
on t1.c1 like '%' + t2.keyword + '%'
go
drop table t1, t2
go
Column [notes] can not be of type text / ntext.
AMB
"John Lloyd" wrote:

> Hello all,
> I am trying to find the instances in a field containg specific keywords or
> strings of information. My table name is History, and my field name is
> Notes. So what I am trying to do is find every record where History.Notes
s
> conatins;
> 'chrom' or 'cell' or 'lab'
> I think I need the IN functin as opposed to using a bunch of OR statements
.
> The OR statments work, but there are so many different keywords/strings,
> that it is a real mess to enter all of the information.
> Thank you for your help
> John
>sql

queries

my function needs to execute 3 queries. is it faster to execute 3 queries
seperately or make a combine query?
update query
execute
update query
execute
insert query
execute
OR
update query;update query;insert query
execute>> is it faster to execute 3 queries seperately or make a combine query?
There is no such heuristic; it depends on what each of the DML statements
do, the indexes involved, the magnitude of the underlying datasets etc. Test
it out both ways are find it out yourself.
Anith|||First of all, your pseudo-code has NO queries at all! UPDATE and
INSERT INTO are statements; they change data. Queries do not change
data. Next, what the heck is EXECUTE? that calls a procedure in
T_SQL.
Finally, who knows? You gave no code to look at, hint about any
relationship that need to be maintained, etc. Can uyou explain what you
meant?

quastion about triggers

hi,

I have a few quastions about triggers:

1) I want that to run a specific function or exe, after a row update.

How can I run a function via the trigger?

2) Is ther any trigger that for an updae of a culomn (not a row)?

thanks,

moria

Hi Moria,
First of all, there are differences id you implement the trigger on a CLR basis (2005) or on a TSQL basis (<2005), so next time, try to give some information about your system, that the proposed solution will better fit your needs.
"How can I run a function via the trigger?"
You talked about executing a EXE, therefore you cann call the extended procedure SP_CMDSHELL which is able to execute a program via the command line (More information about that can be found in the BOL)
COMMENT: It′s odd to execute a command in a trigger. Trigger bahve synchronously, while executing the trigger other people using the data won′t be able to change the lock data (depends on your locking, worst case would be table locking). If there is an error in your command (or a long running command), like waiting for input or something, the trigger will never come back, so you have to
keep that in mind.

"Is ther any trigger that for an updae of a culomn (not a row)?"
Well, yes and no. A trigger is fired per statement (not per ROW) and regardless if your "watched" column changed or not, but if you are in the trigger you have the possibility to use the
IF UPDATE(SomeColumn)
DoSomething
But the trigger is fired regardless, if the column changed or not, keep that in mind, if you have more than this statement in your trigger.
HTH, Jens Suessmeyer.

|||

first of all, thank you for the detailed answer.

I am working with SQL 2000, and .NET 2005.

>>"More information about that can be found in the BOL"

what's BOL, and where can I find it?

>>we understand that an exe can block the SQL, but we want to know when a user updates a table so that we will run a certain program to take the new data, analyze it and reload it to another table. if not by an exe, how do you recommend to do it?

>>In case of deleted a column, is the trigger fired? which one?

thanks a lot,

moria.

|||

Hello again,

"what's BOL, and where can I find it?"
http://www.aspfaq.com/show.asp?id=2229

If this is an external program you sure have to use the exe, but what about wrting a entry in a table that something changed on the program running on an at basis or as a service checks the data, does its recalculation (or whatever) and marks the process as done in the table ? Thats are designing question which cannot be decided on the basis of the only few information given.

HTH, Jens Suessmeyer.

Quastion about Temp Tables

I'm relatively new to SQL-Server, and I have a quick question about
temporary tables. I am using a function from Erland Sommarskog's
excellent article about arrays in SQL server.

Erland's approach uses a user-defined function, which parses a string
of delimited values (integers) into a temporary table. The temporary
table is joined to tables in a stored procedure select statement to
limit the records returned.

The question is, what happens to the temporary table generated by the
function? Is it implicitly destroyed when the stored procedure
finishes? Does it need to be dropped explicitly (something not done in
his examples)?

Also, what are the implications of scope, if multiple users are
accessing the stored procedure through a web application?

Thanks in advance,

TimA temporary table created within a stored procedure is automatically dropped
when the procedure exists.

On 12 Feb 2004 12:59:53 -0800, tim.pascoe@.cciw.ca (Tim Pascoe) wrote:

>I'm relatively new to SQL-Server, and I have a quick question about
>temporary tables. I am using a function from Erland Sommarskog's
>excellent article about arrays in SQL server.
>Erland's approach uses a user-defined function, which parses a string
>of delimited values (integers) into a temporary table. The temporary
>table is joined to tables in a stored procedure select statement to
>limit the records returned.
>The question is, what happens to the temporary table generated by the
>function? Is it implicitly destroyed when the stored procedure
>finishes? Does it need to be dropped explicitly (something not done in
>his examples)?
>Also, what are the implications of scope, if multiple users are
>accessing the stored procedure through a web application?
>Thanks in advance,
>Tim|||[posted and mailed, please reply in news]

Tim Pascoe (tim.pascoe@.cciw.ca) writes:
> I'm relatively new to SQL-Server, and I have a quick question about
> temporary tables. I am using a function from Erland Sommarskog's
> excellent article about arrays in SQL server.

Glad to hear that you liked it!

> Erland's approach uses a user-defined function, which parses a string
> of delimited values (integers) into a temporary table. The temporary
> table is joined to tables in a stored procedure select statement to
> limit the records returned.
> The question is, what happens to the temporary table generated by the
> function? Is it implicitly destroyed when the stored procedure
> finishes? Does it need to be dropped explicitly (something not done in
> his examples)?

If you are using the version with the user-defined function, the table
is never stored on disk (at least conceptually), so you don't have to
bother.

If you are using the version where a stored procedure fills in a temp
table that was created prior to the call, that table is stored in disk.
However, as Steve J pointed out, a temp table disappears when the scope
it was created in goes away, so neither in this case you need to worry.

> Also, what are the implications of scope, if multiple users are
> accessing the stored procedure through a web application?

There are no issues with this. A temp table of the # variety are private
to the connection that created it. This applies as well to the return
table created by multi-step function.

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

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

Quarter() function in SSRS

I am uisng VS 2005 to build reports for SQL 2005. The problem is with getting the Quarter() function, mentioned in the BOL, to work in the report builder. When I build an expression and try to use the function as noted in the BOL I receive an error stating that the name "Quarter" is not declaired. When I use the expression builder to build the expression the function is not available in the builder, which is probably why I get the error.

I am assuming there is some sort of service pack of hot fix I need to apply to my VS but have not been able to locate which one. I have verified that I have all of the recent service packs applied for VS and SQL server 2005 but I am still unable to see/use this function.

Any help would be greatly appreciated. I need this function for my financial reports.

Using the QUARTER(datetime) function does not seem to be available in SQL 2005 SSRS;

=Quarter(today())

Results in an error.

After playing with the date part function I was able to resolve my problem. If you are having a similar problem use the following function to extract the quarter from a date

=DatePart(DateInterval.Quarter,today())

Replace Today() with the date you want to get the value for. You can test this by creating a report and placing a textbox anywhere on the report and setting the above code as the expression for the value of the textbox.

q; How to write this function

How can I write a function that accepts a data as a parameter and if the date
is
1. Last Monday in May or
2. First Monday in September or
3. 4th Tuesday in November
returns true otherwise returns false.The best answer, if these are particular dates known ahead of time, is
a calendar table. But brute force works too.
declare @.d datetime
set @.d = '28 Nov 2006'
SELECT CASE WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 5
AND DATEPART(day,@.d) between 25 and 31
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Monday'
AND DATEPART(month,@.d) = 9
AND DATEPART(day,@.d) between 1 and 7
THEN 'True'
WHEN DATENAME(weekday, @.d) = 'Tuesday'
AND DATEPART(month,@.d) = 11
AND DATEPART(day,@.d) between 22 and 28
THEN 'True'
ELSE 'False'
END as Answer
Roy Harvey
Beacon Falls, CT
On Wed, 27 Dec 2006 04:43:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>How can I write a function that accepts a data as a parameter and if the date
>is
>1. Last Monday in May or
>2. First Monday in September or
>3. 4th Tuesday in November
>returns true otherwise returns false.|||JIM
Take a look at Steve Kass's function
create function dbo.NthWeekDay(
@.first datetime, -- First of the month of interest (no time part)
@.nth tinyint, -- Which of them - 1st, 2nd, etc.
@.dow tinyint -- Day of week we want
) returns datetime as begin
-- Note: Returns a date in a later month if @.nth is too large
declare @.result datetime
set @.result = @.first + 7*(@.nth-1)
return @.result + (7 + @.dow - datepart(weekday,@.result))%7
end
go
-- Find the 5th Thursday of August, 2002
select dbo.NthWeekDay('2002/08/01',5,5) as D
select datename(weekday,D) + space(1) + cast(D as varchar(20))
from (
select dbo.NthWeekDay('2002/08/01',5,5) as D
) X
go
drop function NthWeekDay
"JIM.H." <JIMH@.discussions.microsoft.com> wrote in message
news:FED1D4FF-5F9E-414B-B115-2118EE32F4EC@.microsoft.com...
> How can I write a function that accepts a data as a parameter and if the
> date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>|||On Wed, 27 Dec 2006 07:18:00 -0800, JIM.H.
<JIMH@.discussions.microsoft.com> wrote:
>Thank you very much for your help, last one should be 4th Thursday (sorry my
>mistake), how would this change the code?
Well the fourth Thursday would be a lot like the fourth Tuesday,
wouldn't it? Just copy the day range from that one.
Roy|||Wow, I've been seeing homework assignments getting posted for 20 years now.
"JIM.H." wrote:
> How can I write a function that accepts a data as a parameter and if the date
> is
> 1. Last Monday in May or
> 2. First Monday in September or
> 3. 4th Tuesday in November
> returns true otherwise returns false.
>