KPI Help Required-I want a revenue report with average Daily Revenue

Hi All,

What I am trying to do is create a average daily revenue KPI using vs.net 2005.

The necessary fields from my fact table are Revenue and the datetimeID, there will be multiple entries perday.

I would like my KPI to check if we are hitting a $10,000 daily average in revenue. But I need to know how many Distinct Days are being calcuated. If I were using regular SQL I could use:

COUNT( SELECT DISTINCT dayNumberOfMonth,monthNumberOfYear,YearNumber)
or
COUNT(SELECT DISTINCT LEFT(datetimeAlternateKey,10))

My Current Datetime Table contains:
datetimeID
fulldatetime
monthNumberOfYear
calendarYear
dayNumberOfMonth

Any references out there on how I can go about doing this? More of less this is what I want to do:

Value Expression:
[Measures].[Revenue] * (Distinct Number Of days)

Goal Expression:
10,000*[Distinct Number of Days)

Any suggestions of books to look for or good web resources would be much appreciated.

Thanks all

What period would you be calculating this over and have you considered how you want partial periods treated?

Is the KPI meant to be "adaptive"? So if the user selects a month, it calculates the average for that month and if they select a year it calculates the average for the year?

Assuming that your Time dimension has a calendar hierarchy and a Day attribute, you could do something like the following:

Count(descendants([Time].[Calendar].CurrentMember, [Time].[Calendar].[Day]))

If there is $30,000 revenue recorded on day 1 of the month, does that mean that the KPI has been met for the whole month?

|||Thanks for the reply Darren,

When you refer to [Time].[Calendar].[Day], does [Day] represent an ongoing incrementing day count from the start of dates kept? i.e. Not day or month, nor day of year?

The KPI is meant to be adaptive, at a later point I will go for fixed KPIS (e.g. yesterday).

If for example our Goal Value was $1000 a day, and we did $30,000 in day. Our KPI would be met for any 30 day span that held that date. So right now my datetime table looks like this:

Do you have any suggestions on books I buy for Analysis Services / MDX?

Thanks
|||

winnipeg wrote:

When you refer to [Time].[Calendar].[Day], does [Day] represent an ongoing incrementing day count from the start of dates kept? i.e. Not day or month, nor day of year?

No, the first parameter to the descendants function picks up the current context of the time.calendar hierarchy. If the current context of time.calendar is a month member, the descendants will be all the days in the month. If it is a year member, it will be all the days in the year

winnipeg wrote:

Do you have any suggestions on books I buy for Analysis Services / MDX?

MDX Solutions: With Microsoft SQL Server Analysis Services 2005 and Hyperion Essbase by George Spofford, Sivakumar Harinath, Christopher Webb, and Dylan Hai Huang - is the best one I know for MDX

Applied Microsoft Analysis Services 2005: And Microsoft Business Intelligence Platform by Teo Lachev - is a good alround SSAS book

sql

known, expected or weird behavior ?

Hi all,
Please have a look at a script below:
USE tempdb
DECLARE @.t TABLE (c1 uniqueidentifier, c2 uniqueidentifier)
INSERT @.t (c1, c2)
SELECT y.id, NULL
FROM ( SELECT 1 UNION
SELECT 2 UNION
SELECT 3 UNION
SELECT 4 UNION
SELECT 5
) x (id)
CROSS JOIN
( SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID() UNION
SELECT NEWID()
) y (id)
UPDATE t
SET c2 = y.c2
FROM @.t t,
( SELECT c1, NEWID()
FROM ( SELECT DISTINCT c1
FROM @.t
) x (c1)
) y (c1, c2)
WHERE t.c1 = y.c1
SELECT * FROM @.T
I would expect c2 column value to be the same accross all records where c1
column value is the same.
But in fact c2 column is unique accross the table. I looked at plan and can
see what it's doing and I can rewrite it in a proper way but question
remains, - why is that? Can comeone explain that behavior?
Thank a lot in advance
AlexThat's the way functions work. They are executed for each row of the final
result.
What are you trying to do? If you need uniqueidentifier values for each
integer value, insert them into a temporary table (i.e. table variable)
before issuing the update.
ML
http://milambda.blogspot.com/|||my colleague came accros this piece of code. She reworked it with use of
temp table, and I could find a workaround with derived table. But why the
function is executed in the final set? The code imlies newid() should be
called inside derived table y, and then derived table y joins the table var?
"ML" <ML@.discussions.microsoft.com> wrote in message
news:BBDCFC04-63E4-4408-B153-26A1C5A9EB2B@.microsoft.com...
> That's the way functions work. They are executed for each row of the final
> result.
> What are you trying to do? If you need uniqueidentifier values for each
> integer value, insert them into a temporary table (i.e. table variable)
> before issuing the update.
>
> ML
> --
> http://milambda.blogspot.com/|||We'll know this for sure as soon as we find another system function that
produces as random results as NEWID(). :)
Have you tried using a user-defined function that returns a random result?
ML
http://milambda.blogspot.com/|||Interesting, I used RAND() and float instead of NEWID() and
uniqueidentifier, and in this case RAND() was applied to the final result
set too, but the difference seems to be that RAND() was called only once
since ALL records have the same float value.
No, I didn't used UDF yet, maybe later today when I have time.
"ML" <ML@.discussions.microsoft.com> wrote in message
news:FDDC2357-937A-42DF-8358-CFF69860095B@.microsoft.com...
> We'll know this for sure as soon as we find another system function that
> produces as random results as NEWID(). :)
> Have you tried using a user-defined function that returns a random result?
>
> ML
> --
> http://milambda.blogspot.com/|||NEWID is special. It is called for every row in a query. All other function
(the I know of) are
called only once in a query. Hence the difference between RAND and NEWID.
USE northwind
SELECT
NEWID() AS myNEWID
,RAND() AS myRand
,CURRENT_TIMESTAMP AS myTS
FROM "Order Details"
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Alex" <alex_remove_this_mak@.telus.net> wrote in message news:dB6Ef.153384$6K2.43614@.edtnps
90...
> Interesting, I used RAND() and float instead of NEWID() and uniqueidentifi
er, and in this case
> RAND() was applied to the final result set too, but the difference seems t
o be that RAND() was
> called only once since ALL records have the same float value.
> No, I didn't used UDF yet, maybe later today when I have time.
>
> "ML" <ML@.discussions.microsoft.com> wrote in message
> news:FDDC2357-937A-42DF-8358-CFF69860095B@.microsoft.com...
>|||This is the expected behaviour of RAND.
ML
http://milambda.blogspot.com/|||I was wondering whether getdate() is called for each row or for the set...
Would it show on a big enough set?
ML
http://milambda.blogspot.com/|||thanks guys a lot
"ML" <ML@.discussions.microsoft.com> wrote in message
news:D44C180C-8F26-461D-A552-9365B4DA27C9@.microsoft.com...
> This is the expected behaviour of RAND.
>
> ML
> --
> http://milambda.blogspot.com/|||Getdate() is normally only called once.
However, Itzik Ben-Gan came up with a really clever workaround for both
rand() and getdate().
Normally, you can't put getdate() or rand() in a User Defined Function, but
you can put them in a view, and then have your function select from the
view. You can then put your function in the select list, to have the
getdate() or rand() regenerated for each row.
Note that it might not look like getdate() is called for every single row,
because of the precision of the datatype. The function might be called
repeatedly more quickly than the getdate() value changes. But if you have
enough rows, you'll see that they aren't ALL the same, even though there
could be duplication.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"ML" <ML@.discussions.microsoft.com> wrote in message
news:30C35A84-706B-48F3-8C41-69ECB598DA13@.microsoft.com...
>I was wondering whether getdate() is called for each row or for the set...
> Would it show on a big enough set?
>
> ML
> --
> http://milambda.blogspot.com/
>

knowing which jobs/dts/SSIS affect which tables

hi, we were doing something and began to wonder which jobs/dts were
affecting a certain table (we use SQL 2000)... is there anyway to check
this?

and have the features for such dependency analysis been added for SQL
2005?I've never tried this, but you might be able to trap it using Profiler;
set up a log and filter on object_id.

http://www.microsoft.com/technet/ar...9.mspx?mfr=true

Post back and let us know if that works for you.

Stu

metaperl wrote:
> hi, we were doing something and began to wonder which jobs/dts were
> affecting a certain table (we use SQL 2000)... is there anyway to check
> this?
> and have the features for such dependency analysis been added for SQL
> 2005?sql

Knowing when <NULL>

I'm trying to change the <NULL> fields of my table, but I don't know how to tell the query to look for <NULL
For example:

select * from MyTable
where fieldx = <NULL
This doesn't work.
How should it be?Try this:

select * from MyTable where fieldx IS NULL

Terri

Knowing what is grouped?!

Hello!

I have a table "A" like this shown bellow and a problem...

Vr Kon Value ID DZ PRM 22900 -16.00 101 M PRM 22900 16.00 102 P PRM 22900 -728.19 103 M PRM 22900 728.19 104 P

I am doing simle grouping on this table...
Grouping is by Vr,Kon,Dz and Value is summed.

Select is like this
SELECT Vr,Kon,sum(value) from TableA
GROUP BY vr,kon,dz
So, my results look like this:

Vr Kon Value 1 PRM 22900 -744.19 2 PRM 22900 744.19

What I need to know is to somehow link row 1 from my results to IDs 101 and 103 from table and ofcourse row 2 to IDs 102 and 104...
Since english is not my native language i'll try to use colurs in effort to
clarify desired results...

I need to know that row 1 was product of grouping rows with IDs 101 and 103.
So my desired results will look like this...

Vr Kon Value Grouped_IDs 1 PRM 22900 -744.19 101,103 2 PRM 22900 744.19 102,104

or in ideal case same stuff but in relational table... like this:

Result_Row Grouped_ID_From_TableA 1 101 1 103 2 102 2 104

Any thoughts?!

p.s.
I forgot to say that I am working on SQL Server 2000...<marko2511@.discussions..microsoft.com> wrote in message news:167b3c86-3b1e-4611-ab79-4d46cf72284e_WBRev1_@.discussions..microsoft.com...What I need to know is to somehow link row 1 from my results to IDs 1 and 3 from table and ofcourse row 2 to IDs 2 and 4... I'm not sure what "link" means in this context. Can you show the output you're hoping for? -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457--|||I have edited my first post in effort to try clarify things little better...
Please, read again...|||Ah! In that case: http://www.aspfaq.com/show.asp?id=2529 -- Adam MachanicPro SQL Server 2005, available nowhttp://www..apress.com/book/bookDisplay.html?bID=457-- <marko2511@.discussions..microsoft.com> wrote in message news:7f30a711-c63a-4a87-b121-bc7ca1388a93@.discussions.microsoft.com...I have edited my first post in effort to try clarify things little better...Please, read again...|||Seams it's should work but it's late at night here so i'll check it in the morning
and get back here with results...
Regards.

Know list database and table in SQL Server

I use this macro to list in A column of sheet the name of sql server on
my lan.
Is possible to "scan server by server" and insert into column B the
name of database and related table?
Tks.
Sub test_sql()
Dim TEST As String
Dim RIGA As String

Set sqlApp = CreateObject("SQLDMO.Application")

Set serverList = sqlApp.ListAvailableSQLServers

numServers = serverList.Count
RIGA = 2

For I = 1 To numServers

TEST = serverList(I)

Range("A" + RIGA) = TEST
RIGA = RIGA + 1
Next

Set sqlApp = Nothing

End Subsorry for UP...|||Give this a try - the code assumes that you can connect to each server via Windows Authentication and that you have appropriate permissions to list the databases/tables. I've added Error handling for the connection to each server - if there is a problem listing the databases/tables, the code will stop with an error. Please note that I haven't performed any extensive testing of the code.

Sub SQLAudit()
'Clear sheet
Cells.ClearContents
Cells.ClearFormats

Const DisplaySystemDatabases = False ' Change this if you want to view system databases
Const DisplaySystemTables = False ' change this if you want to view system tables
Dim objSQLApp, objSQLServer, objSQLDatabase, objSQLTable
Dim strSQLServer
Dim i As Integer
i = 1

Set objSQLApp = CreateObject("SQLDMO.Application")

' Enumerate list of available SQL Servers
For Each strSQLServer In objSQLApp.ListAvailableSQLServers
' Server Header (Remove if header not required)
Range("A" & i).Value = strSQLServer
Range("A" & i & ":C" & i).Merge
Range("A" & i & ":C" & i).Font.Bold = True
Range("A" & i & ":C" & i).Font.Size = 14
Range("A" & i & ":C" & i).HorizontalAlignment = xlCenter
i = i + 1
' ***********************
Set objSQLServer = CreateObject("SQLDMO.SQLServer")
objSQLServer.LoginSecure = True ' Connect using Windows Authentication
On Error Resume Next
' Connect to the server (will fail if user does not have logon for server)
objSQLServer.Connect strSQLServer
If Err.Number <> 0 Then
' Display a message if unable to connect to the server
MsgBox ("Failed to connect to: " & strSQLServer & vbCrLf & Err.Description)
Err.Clear
Else
On Error GoTo 0 ' Turn off resume next error handling (throw exception if error occurs)
' Enumerate databases on server
For Each objSQLDatabase In objSQLServer.Databases
If (Not objSQLDatabase.SystemObject) Or DisplaySystemDatabases Then
' Database Header (Remove if header not requied)
Range("B" & i).Value = objSQLDatabase.Name
Range("B" & i & ":C" & i).Merge
Range("B" & i & ":C" & i).Font.Bold = True
Range("B" & i & ":C" & i).HorizontalAlignment = xlCenter
i = i + 1
' ***********************
'Enumerate tables in database
For Each objSQLTable In objSQLDatabase.Tables
If (Not objSQLTable.SystemObject) Or DisplaySystemTables Then
Range("A" & i).Value = objSQLServer.Name
Range("B" & i).Value = objSQLDatabase.Name
Range("C" & i).Value = objSQLTable.Name
i = i + 1
End If
Next
End If
Next
End If
Next

Set objSQLApp = Nothing
Set objSQLServer = Nothing
Set objSQLDatabase = Nothing
Set objSQLTable = Nothing
End Sub

Knickers in a Loop

Now I know I'm probably doing this all wrong but bear with me: I'm
trying to break a table down into separate rows so that these can be
used in the @.query in xp_sendmail. Now I've been able to create tables
per row but can't populate the tables. The problem is that it is
asking for a variable to be declared when if I ask select @.variable it
tells me what I want to know. So please help.
Thanks
John McGinty
TSQL
--create tables
declare @.rm_tkemail varchar(30)
declare @.rm_table varchar(10)
declare @.sql varchar(4000)
declare cc cursor for
select tkemail from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_tkemail
if @.@.fetch_status <>0
break
select @.rm_table = 'jpm_' + @.rm_tkemail
set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
'(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
(15),
total_time money, total_cost money, total_bill decimal (9), lowlimit
decimal (9), medlimit decimal (9), highlimit decimal(9)) '
exec (@.sql)
end
close cc
deallocate cc
go
--this works fine and creates tables called jpm_@.rm_tkemail for every
row in
--the table.
--populate table
--declare variables
declare @.rm_table varchar(10)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
--declare and open cursor
declare cc cursor for
select fee_earner, tkemail, mmatter from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
if @.@.fetch_status <>0
break
select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
select @.rm_table = 'jpm_' + @.rm_tkemail
--print @.rm_table this confirms @.rm_table has a value
insert into @.rm_table --but here its asking to declare @.rm_table!
(fee_earner, tkemail, mmatter)
values
(@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
end
close cc
deallocate cc
go
John,
You cannot substitute a table with a variable. Use dynamic SQL for that. The reason why you get a "strange"
error is that SQL Server assumes that the variable is a table variable...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John McGinty" <jpmcginty@.talk21.com> wrote in message news:87642ea9.0405130322.424e8227@.posting.google.c om...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go
|||Hi
Just wondering why you create the table? You code is assuming that tkemail
is unique (otherwise you would have duplicate table names) . Why not call
xp_sendmail in the loop or if necessary have a second cursor and call it
within the second loop?
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:87642ea9.0405130322.424e8227@.posting.google.c om...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go
|||thanks for the reply John.
What I'm trying to do is this:
Table 1: Contains a list of people and the current state of their
accounts which have all breached a set level.
I want to generate an individual email that notifies the person that
they have breached the limit on a certain account and show them the
details.
For example
Name Expenses Agreed Expeneses Email
Woody 150 100 Woody@.blah.com
Buzz 200 190 Buzz@.blah.com
Rex 60 50 rex@.blah.com
Send Email
To: Woody@.blah.com
From: DBA
Subject: Expenses Exceeded
[message] Woody, You have breached the agreed level on the following
accounts (select * from [appropriate table]
Name Expenses Agreed Expeneses
Woody 150 100
Please see the accounts manager
Now I could include all the people that have breached but the email will
contain that is not relevant and theres a good chance they would bother
with contact.
My idea was to create table that only contained data for one email so I
planned (I've simplied this but hopefully you'll get the idea)
create table [email]
(name, expenses, agreed_expeneses)
insert into [email] (name, expenses, agreed_expeneses)
values (@.name, @.expenses, @.agreed_expenses)
so that I could use xp_sendmail with the details of the table.
I am able to send emails to specific people, able to create tables with
the email but unable to insert data into the table and its a bit of a
bugger. As I said, as always, this is probably not the best way of doing
it but I don't know anything else as I'm still on that learning curve so
help, advise, critism would be appreciated.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
|||Hi
This is just rough and untested as there is not enough information in your
postings:
declare @.rm_email_subject varchar(30)
declare @.rm_email_text varchar(100)
declare @.rm_email_content varchar(8000)
declare @.rm_email_signature varchar(100)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
declare @.rm_expenses varchar(10)
declare @.rm_agreed_expenses varchar(10)
set @.rm_email_text = ' You have breached the agreed level on the following
accounts
Name Expenses Agreed Expeneses
', @.rm_email_signature = '
Yours faithfully
John', @.rm_email_subject = 'Expenses Exceeded'
declare cc cursor for
select fee_earner, tkemail, mmatter, CONVERT(varchar(10),expenses),
CONVERT(varchar(10),agreed_expenses), from rm_80
where expenses > aggreed_expenses
open cc
while 1=1
begin
fetch next from cc
into @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter, @.rm_expenses,
@.rm_agreed_expenses
if @.@.fetch_status <>0
break
SELECT @.rm_email_content = @.rm_fee_earner + @.rm_email_text + @.rm_fee_earner
+ @.rm_expenses + ' ' + @.rm_agreed_expenses + @.rm_email_signature
EXEC master..xp_sendmail @.recipients =@.rm_tkemail, @.message
=@.rm_email_content ,@.subject =@.rm_email_subject
close cc
deallocate cc
go
This would work for 1 account per email, if you required more then there
would need to be a second cursor that loops through each row and appends
details to the message body.
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:upncqXQOEHA.1620@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply John.
> What I'm trying to do is this:
> Table 1: Contains a list of people and the current state of their
> accounts which have all breached a set level.
> I want to generate an individual email that notifies the person that
> they have breached the limit on a certain account and show them the
> details.
> For example
> Name Expenses Agreed Expeneses Email
> ----
> Woody 150 100 Woody@.blah.com
> Buzz 200 190 Buzz@.blah.com
> Rex 60 50 rex@.blah.com
> Send Email
> To: Woody@.blah.com
> From: DBA
> Subject: Expenses Exceeded
> [message] Woody, You have breached the agreed level on the following
> accounts (select * from [appropriate table]
> Name Expenses Agreed Expeneses
> --
> Woody 150 100
> Please see the accounts manager
> Now I could include all the people that have breached but the email will
> contain that is not relevant and theres a good chance they would bother
> with contact.
> My idea was to create table that only contained data for one email so I
> planned (I've simplied this but hopefully you'll get the idea)
> create table [email]
> (name, expenses, agreed_expeneses)
> insert into [email] (name, expenses, agreed_expeneses)
> values (@.name, @.expenses, @.agreed_expenses)
> so that I could use xp_sendmail with the details of the table.
> I am able to send emails to specific people, able to create tables with
> the email but unable to insert data into the table and its a bit of a
> bugger. As I said, as always, this is probably not the best way of doing
> it but I don't know anything else as I'm still on that learning curve so
> help, advise, critism would be appreciated.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!

Knickers in a Loop

Now I know I'm probably doing this all wrong but bear with me: I'm
trying to break a table down into separate rows so that these can be
used in the @.query in xp_sendmail. Now I've been able to create tables
per row but can't populate the tables. The problem is that it is
asking for a variable to be declared when if I ask select @.variable it
tells me what I want to know. So please help.
Thanks
John McGinty
TSQL
--create tables
declare @.rm_tkemail varchar(30)
declare @.rm_table varchar(10)
declare @.sql varchar(4000)
declare cc cursor for
select tkemail from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_tkemail
if @.@.fetch_status <>0
break
select @.rm_table = 'jpm_' + @.rm_tkemail
set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
'(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
(15),
total_time money, total_cost money, total_bill decimal (9), lowlimit
decimal (9), medlimit decimal (9), highlimit decimal(9)) '
exec (@.sql)
end
close cc
deallocate cc
go
--this works fine and creates tables called jpm_@.rm_tkemail for every
row in
--the table.
--populate table
--declare variables
declare @.rm_table varchar(10)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
--declare and open cursor
declare cc cursor for
select fee_earner, tkemail, mmatter from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
if @.@.fetch_status <>0
break
select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
select @.rm_table = 'jpm_' + @.rm_tkemail
--print @.rm_table this confirms @.rm_table has a value
insert into @.rm_table --but here its asking to declare @.rm_table!
(fee_earner, tkemail, mmatter)
values
(@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
end
close cc
deallocate cc
goJohn,
You cannot substitute a table with a variable. Use dynamic SQL for that. The reason why you get a "strange"
error is that SQL Server assumes that the variable is a table variable...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John McGinty" <jpmcginty@.talk21.com> wrote in message news:87642ea9.0405130322.424e8227@.posting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go|||Hi
Just wondering why you create the table? You code is assuming that tkemail
is unique (otherwise you would have duplicate table names) . Why not call
xp_sendmail in the loop or if necessary have a second cursor and call it
within the second loop?
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:87642ea9.0405130322.424e8227@.posting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go

Knickers in a Loop

Now I know I'm probably doing this all wrong but bear with me: I'm
trying to break a table down into separate rows so that these can be
used in the @.query in xp_sendmail. Now I've been able to create tables
per row but can't populate the tables. The problem is that it is
asking for a variable to be declared when if I ask select @.variable it
tells me what I want to know. So please help.
Thanks
John McGinty
TSQL
--create tables
declare @.rm_tkemail varchar(30)
declare @.rm_table varchar(10)
declare @.sql varchar(4000)
declare cc cursor for
select tkemail from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_tkemail
if @.@.fetch_status <>0
break
select @.rm_table = 'jpm_' + @.rm_tkemail
set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
'(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
(15),
total_time money, total_cost money, total_bill decimal (9), lowlimit
decimal (9), medlimit decimal (9), highlimit decimal(9)) '
exec (@.sql)
end
close cc
deallocate cc
go
--this works fine and creates tables called jpm_@.rm_tkemail for every
row in
--the table.
--populate table
--declare variables
declare @.rm_table varchar(10)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
--declare and open cursor
declare cc cursor for
select fee_earner, tkemail, mmatter from rm_80
open cc
while 1=1
begin
fetch next from cc
into
@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
if @.@.fetch_status <>0
break
select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
select @.rm_table = 'jpm_' + @.rm_tkemail
--print @.rm_table this confirms @.rm_table has a value
insert into @.rm_table --but here its asking to declare @.rm_table!
(fee_earner, tkemail, mmatter)
values
(@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
end
close cc
deallocate cc
goJohn,
You cannot substitute a table with a variable. Use dynamic SQL for that. The
reason why you get a "strange"
error is that SQL Server assumes that the variable is a table variable...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"John McGinty" <jpmcginty@.talk21.com> wrote in message news:87642ea9.0405130322.424e8227@.pos
ting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go|||Hi
Just wondering why you create the table? You code is assuming that tkemail
is unique (otherwise you would have duplicate table names) . Why not call
xp_sendmail in the loop or if necessary have a second cursor and call it
within the second loop?
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:87642ea9.0405130322.424e8227@.posting.google.com...
> Now I know I'm probably doing this all wrong but bear with me: I'm
> trying to break a table down into separate rows so that these can be
> used in the @.query in xp_sendmail. Now I've been able to create tables
> per row but can't populate the tables. The problem is that it is
> asking for a variable to be declared when if I ask select @.variable it
> tells me what I want to know. So please help.
> Thanks
> John McGinty
> TSQL
> --create tables
> declare @.rm_tkemail varchar(30)
> declare @.rm_table varchar(10)
> declare @.sql varchar(4000)
> declare cc cursor for
> select tkemail from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_tkemail
> if @.@.fetch_status <>0
> break
> select @.rm_table = 'jpm_' + @.rm_tkemail
> set @.sql = ' CREATE TABLE ' + @.rm_tkemail +
> '(fee_earner nvarchar (20), tkemail varchar (5), mmatter varchar
> (15),
> total_time money, total_cost money, total_bill decimal (9), lowlimit
> decimal (9), medlimit decimal (9), highlimit decimal(9)) '
> exec (@.sql)
> end
> close cc
> deallocate cc
> go
> --this works fine and creates tables called jpm_@.rm_tkemail for every
> row in
> --the table.
> --populate table
> --declare variables
> declare @.rm_table varchar(10)
> declare @.rm_fee_earner varchar(20)
> declare @.rm_tkemail varchar(5)
> declare @.rm_mmatter varchar(15)
> --declare and open cursor
> declare cc cursor for
> select fee_earner, tkemail, mmatter from rm_80
> open cc
> while 1=1
> begin
> fetch next from cc
> into
> @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> if @.@.fetch_status <>0
> break
> select @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter,
> select @.rm_table = 'jpm_' + @.rm_tkemail
> --print @.rm_table this confirms @.rm_table has a value
> insert into @.rm_table --but here its asking to declare @.rm_table!
> (fee_earner, tkemail, mmatter)
> values
> (@.rm_fee_earner, @.rm_tkemail, @.rm_mmatter)
> end
> close cc
> deallocate cc
> go|||thanks for the reply John.
What I'm trying to do is this:
Table 1: Contains a list of people and the current state of their
accounts which have all breached a set level.
I want to generate an individual email that notifies the person that
they have breached the limit on a certain account and show them the
details.
For example
Name Expenses Agreed Expeneses Email
----
Woody 150 100 Woody@.blah.com
Buzz 200 190 Buzz@.blah.com
Rex 60 50 rex@.blah.com
Send Email
To: Woody@.blah.com
From: DBA
Subject: Expenses Exceeded
[message] Woody, You have breached the agreed level on the following
accounts (select * from [appropriate table]
Name Expenses Agreed Expeneses
--
Woody 150 100
Please see the accounts manager
Now I could include all the people that have breached but the email will
contain that is not relevant and theres a good chance they would bother
with contact.
My idea was to create table that only contained data for one email so I
planned (I've simplied this but hopefully you'll get the idea)
create table [email]
(name, expenses, agreed_expeneses)
insert into [email] (name, expenses, agreed_expeneses)
values (@.name, @.expenses, @.agreed_expenses)
so that I could use xp_sendmail with the details of the table.
I am able to send emails to specific people, able to create tables with
the email but unable to insert data into the table and its a bit of a
bugger. As I said, as always, this is probably not the best way of doing
it but I don't know anything else as I'm still on that learning curve so
help, advise, critism would be appreciated.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!|||Hi
This is just rough and untested as there is not enough information in your
postings:
declare @.rm_email_subject varchar(30)
declare @.rm_email_text varchar(100)
declare @.rm_email_content varchar(8000)
declare @.rm_email_signature varchar(100)
declare @.rm_fee_earner varchar(20)
declare @.rm_tkemail varchar(5)
declare @.rm_mmatter varchar(15)
declare @.rm_expenses varchar(10)
declare @.rm_agreed_expenses varchar(10)
set @.rm_email_text = ' You have breached the agreed level on the following
accounts
Name Expenses Agreed Expeneses
--
', @.rm_email_signature = '
Yours faithfully
John', @.rm_email_subject = 'Expenses Exceeded'
declare cc cursor for
select fee_earner, tkemail, mmatter, CONVERT(varchar(10),expenses),
CONVERT(varchar(10),agreed_expenses), from rm_80
where expenses > aggreed_expenses
open cc
while 1=1
begin
fetch next from cc
into @.rm_fee_earner, @.rm_tkemail, @.rm_mmatter, @.rm_expenses,
@.rm_agreed_expenses
if @.@.fetch_status <>0
break
SELECT @.rm_email_content = @.rm_fee_earner + @.rm_email_text + @.rm_fee_earner
+ @.rm_expenses + ' ' + @.rm_agreed_expenses + @.rm_email_signature
EXEC master..xp_sendmail @.recipients =@.rm_tkemail, @.message
=@.rm_email_content ,@.subject =@.rm_email_subject
close cc
deallocate cc
go
This would work for 1 account per email, if you required more then there
would need to be a second cursor that loops through each row and appends
details to the message body.
John
"John McGinty" <jpmcginty@.talk21.com> wrote in message
news:upncqXQOEHA.1620@.TK2MSFTNGP12.phx.gbl...
> thanks for the reply John.
> What I'm trying to do is this:
> Table 1: Contains a list of people and the current state of their
> accounts which have all breached a set level.
> I want to generate an individual email that notifies the person that
> they have breached the limit on a certain account and show them the
> details.
> For example
> Name Expenses Agreed Expeneses Email
> ----
> Woody 150 100 Woody@.blah.com
> Buzz 200 190 Buzz@.blah.com
> Rex 60 50 rex@.blah.com
> Send Email
> To: Woody@.blah.com
> From: DBA
> Subject: Expenses Exceeded
> [message] Woody, You have breached the agreed level on the following
> accounts (select * from [appropriate table]
> Name Expenses Agreed Expeneses
> --
> Woody 150 100
> Please see the accounts manager
> Now I could include all the people that have breached but the email will
> contain that is not relevant and theres a good chance they would bother
> with contact.
> My idea was to create table that only contained data for one email so I
> planned (I've simplied this but hopefully you'll get the idea)
> create table [email]
> (name, expenses, agreed_expeneses)
> insert into [email] (name, expenses, agreed_expeneses)
> values (@.name, @.expenses, @.agreed_expenses)
> so that I could use xp_sendmail with the details of the table.
> I am able to send emails to specific people, able to create tables with
> the email but unable to insert data into the table and its a bit of a
> bugger. As I said, as always, this is probably not the best way of doing
> it but I don't know anything else as I'm still on that learning curve so
> help, advise, critism would be appreciated.
>
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!sql

killing the duplicates from a table using sql

hi, what is a good way to kill the duplicates from a table. when i say killing duplicated, i mean killing all the rows for the repeated row.

WorkTempID ItemNo Seq
100196 RTP-22 1
100197 RTP-22 2
100198 RTP-22 3
100199 RTP-22 3
100200 RTP-22 4
100201 RTP-22 4
100202 RTP-22 5
100203 RTP-22 5

********************************************************
see how Seq 3, 4 and 5 are repeated? so for the output i want.

WorkTempID ItemNoSeq
100196RTP-221
100197RTP-222
********************************************************

i DO NOT want this as the output. i already know how to achive this using DISTINCT keyword

WorkTempID ItemNoSeq
100196RTP-221
100197RTP-222
100198RTP-223
100200RTP-224
100203RTP-225How big is the table?|||not that big.. few dozen rows. i am basically getting the job done by reading through the whole table, doing a count based on seq. if the count is more than 1, i update that row with "delete" as the ItemNo. at the end just deleting everyting that has "delete" for ItemNo. gets the job done but i think there is a better way to do this.|||Assuming that you actually want to remove the duplicates from the underlying table...
If the table isn't that big then consider something like this:

Declare @.tblTemp table (WorkTemplID int, Item char(10), Seq int)
insert into @.tblTemp
select distinct * from Items
truncate Items
insert into Items
select * from @.tblTemp

Not the most elegant code but very easy to understand|||thanks for the reply but it does not give me what i need. remember, i not only need to kill the duplicates but also the orginal row that is duplicated. if seq 3 is repeated 5 times DISTINCT keyword will give me 1 row that has seq 3 in it. but i dont want to get ANY rows with seq 3.|||create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)

select * from #t1
group by c1
having count(c1) < 2|||aaah. Ok you want something like this...

delete <table>
from <table> ORG
inner join
(select <col1>, <col2>,etc from <table> group by <col1>, <col2>,etc
having count(*) > 1) <some table alias STA> on
STA.<col1> = ORG.<col1> and etc (for all cols)

|||create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)

select *
into #t2
from #t1
group by c1
having count(c1) < 2

select * from #t2|||ok ok, here's my last go at a perfect template ...

create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)

delete from #t1
where c1 in
(
select c1
from #t1
group by c1
having count(c1) > 1
)|||ok, my last attempt at making the perfect template for this ...

create table #t1 (c1 int)
insert into #t1 values (1)
insert into #t1 values (2)
insert into #t1 values (3)
insert into #t1 values (3)
insert into #t1 values (4)
insert into #t1 values (4)
insert into #t1 values (5)
insert into #t1 values (5)

delete from #t1
where c1 in (
select c1
from #t1
group by c1
having count(c1) > 1
)

select * from #t1

Richard101|||Richard101 that only works if you've got a single unique column. The original example has no unique key columns...wouldn't have a problem if it did.

I just want to see you write out a few more templates :)|||ok, although my idea of a template is something that works, reduced to it's minimum, that you can build up.

right, using your data...

--
create table #t1 (c1 varchar(10), c2 varchar(10), c3 int)
insert into #t1 values ('100196', 'RTP-22', 1)
insert into #t1 values ('100197', 'RTP-22', 2)
insert into #t1 values ('100198', 'RTP-22', 3)
insert into #t1 values ('100199', 'RTP-22', 3)
insert into #t1 values ('100200', 'RTP-22', 4)
insert into #t1 values ('100201', 'RTP-22', 4)
insert into #t1 values ('100202', 'RTP-22', 5)
insert into #t1 values ('100203', 'RTP-22', 5)

delete from #t1
where c3 in
(
select c3
from #t1
group by c3
having count(c3) > 1
)

select * from #t1
--

Richard101|||Teehee. I was assuming that that a duplicates had to be c1 AND c2 AND c3. My fault. So how would you write that one Richard? ;)

Killing a process does not help table that can't be read

I have a table that is locked by a process, but when I try to kill it using
Query Analyzer, I get the following message:
"SPID 136: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds."
It has been saying this for a long time now and I need to get this table
functioning because it's my main customer table. Any help is appreciated.
I had a similar problem yesterday with a view yesterday, and I couldn't get
it cleared out until I restarted the server. It doesn't make sense to me
that I would have to restart an entire server to unlock this one table, does
it?
Jeremiah
Hi
It looks like people have contacted PSS regarding this in the past!
http://tinyurl.com/5opp6
You may want to check your version number and see if there are any more
up-to-date patches that fix it.
John
"Jeremiah Traxler" wrote:

> I have a table that is locked by a process, but when I try to kill it using
> Query Analyzer, I get the following message:
> "SPID 136: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds."
> It has been saying this for a long time now and I need to get this table
> functioning because it's my main customer table. Any help is appreciated.
> I had a similar problem yesterday with a view yesterday, and I couldn't get
> it cleared out until I restarted the server. It doesn't make sense to me
> that I would have to restart an entire server to unlock this one table, does
> it?
> Jeremiah
sql

Killing a process does not help table that can't be read

I have a table that is locked by a process, but when I try to kill it using
Query Analyzer, I get the following message:
"SPID 136: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds."
It has been saying this for a long time now and I need to get this table
functioning because it's my main customer table. Any help is appreciated.
I had a similar problem yesterday with a view yesterday, and I couldn't get
it cleared out until I restarted the server. It doesn't make sense to me
that I would have to restart an entire server to unlock this one table, does
it?
JeremiahHi
It looks like people have contacted PSS regarding this in the past!
http://tinyurl.com/5opp6
You may want to check your version number and see if there are any more
up-to-date patches that fix it.
John
"Jeremiah Traxler" wrote:

> I have a table that is locked by a process, but when I try to kill it usin
g
> Query Analyzer, I get the following message:
> "SPID 136: transaction rollback in progress. Estimated rollback completion
:
> 0%. Estimated time remaining: 0 seconds."
> It has been saying this for a long time now and I need to get this table
> functioning because it's my main customer table. Any help is appreciated.
> I had a similar problem yesterday with a view yesterday, and I couldn't ge
t
> it cleared out until I restarted the server. It doesn't make sense to me
> that I would have to restart an entire server to unlock this one table, do
es
> it?
> Jeremiah

Killing a process does not help table that can't be read

I have a table that is locked by a process, but when I try to kill it using
Query Analyzer, I get the following message:
"SPID 136: transaction rollback in progress. Estimated rollback completion:
0%. Estimated time remaining: 0 seconds."
It has been saying this for a long time now and I need to get this table
functioning because it's my main customer table. Any help is appreciated.
I had a similar problem yesterday with a view yesterday, and I couldn't get
it cleared out until I restarted the server. It doesn't make sense to me
that I would have to restart an entire server to unlock this one table, does
it?
JeremiahHi
It looks like people have contacted PSS regarding this in the past!
http://tinyurl.com/5opp6
You may want to check your version number and see if there are any more
up-to-date patches that fix it.
John
"Jeremiah Traxler" wrote:
> I have a table that is locked by a process, but when I try to kill it using
> Query Analyzer, I get the following message:
> "SPID 136: transaction rollback in progress. Estimated rollback completion:
> 0%. Estimated time remaining: 0 seconds."
> It has been saying this for a long time now and I need to get this table
> functioning because it's my main customer table. Any help is appreciated.
> I had a similar problem yesterday with a view yesterday, and I couldn't get
> it cleared out until I restarted the server. It doesn't make sense to me
> that I would have to restart an entire server to unlock this one table, does
> it?
> Jeremiah

kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?|||Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
>> So I cancelled the query at 11:00. 12 hours later,
>> it is about half way done rolling back. As a result
>> we can't access the table and get any work done today.
>> If it is rolling back a SELECT INTO a new table, why would this prevent
>> you from accessing the existing table? Or did you mean to explain that
>> the rollback is consuming the server in terms of resources?
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>

kill long running query

I am changing some field types in a large table
by creating the new table and then selecting the
data into it. So no changes are being made to the
existing data.
I started this at 5:00 last night, and by 11:00
it became apparent that it wasn't going to finish
until this weekend.
I can run queries in UNCOMMITTED READ isolation level
and see how many images have been copied. By 11:00
it was only 12% done.
So I cancelled the query at 11:00. 12 hours later,
it is about half way done rolling back. As a result
we can't access the table and get any work done today.
I don't need this transaction rolled back. I could
just drop the temporary table and be done with it.
I've looked at the KILL copmmand, but that just causes
it to rollback, so it doesn't help.
I could reboot the server, but as far as I know, it might
pick up where it left off and continue rolling back the
transaction.
Is there any way out of this hole that I've dug myself into?
Thanks,
Brad.
> So I cancelled the query at 11:00. 12 hours later,
> it is about half way done rolling back. As a result
> we can't access the table and get any work done today.
If it is rolling back a SELECT INTO a new table, why would this prevent you
from accessing the existing table? Or did you mean to explain that the
rollback is consuming the server in terms of resources?
|||Aaron Bertrand [SQL Server MVP] wrote :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
I don't know the answer to that.
I assume that it has affected so many rows
(I was copying the entire table to a new table)
that the locks have been promoted to table level.
2G rows.
The script I was using use ISOLATION LEVEL SERIALIZABLE.
The question has been rendered moot, as the server crashed.
Thanks,
Brad.
|||Aaron Bertrand [SQL Server MVP] wrote on 4/4/2008 :
> If it is rolling back a SELECT INTO a new table, why would this prevent you
> from accessing the existing table? Or did you mean to explain that the
> rollback is consuming the server in terms of resources?
No, I don't know why it would be adding locks
on the SOURCE table.
What led me to think that was that queries against
any other table would return reasonably, while
queries against either of those tables would run
up to 30 minutes without returning before I killed them.
Thanks,
Brad.
|||2 billion rows, yeah that's going to be tough. If you have the space, I
would suggest making a backup of the database, restoring it as a different
database, and then copy the rows from the restored copy. Then you can use a
much lower isolation level because you don't have to worry about people
coming in and changing the source rows while you are copying...
"Brad White" <bwhite_at_inebraska_dot_com@.remove.nul> wrote in message
news:mn.24b57d840b74b3fd.81110@.remove.nul...
> Aaron Bertrand [SQL Server MVP] wrote :
> I don't know the answer to that.
> I assume that it has affected so many rows
> (I was copying the entire table to a new table)
> that the locks have been promoted to table level.
> 2G rows.
> The script I was using use ISOLATION LEVEL SERIALIZABLE.
> The question has been rendered moot, as the server crashed.
> Thanks,
> Brad.
>

Kick off Stored Procedure to run Nightly?

I have a stored procedure that updates a table. I want that table to be updated nightly. I looked into the DTS package but it seems a bit difficult. Is there some other schedule in Enterprise that I can kick off my stored proc without having to build a DTS package?

In DTS, they ask for all of these symbols and want the query, but my code is already in stored proc form.

Help?What you need to do is use SQL Agent. You can schedule the job to run nightly at your prferred time. When you create a new SQL Agent job, you then ad a job step. In this job step you specify the SQL command you want to run. I'm assuming you are using SQL Server 2000 since you refer to DTS. Using SQL Server Enterprise manager, expand the SQL Server you are working with, then expand the 'Managment' tree, then expand 'SQL Agent' tree and right click on Jobs and choose New Job... The tabs here should be self explanatory.|||Ok I see where the job steps are. In the command window, I can type in my proc name sp_DailyOrders and it will know how to kick it off?|||In the step name tab, just give the step a name like 'Execute procedure', make sure the database is the correct database where your procedure lives, and type in exec sp_your_proc_name in the command section. Then move to the Scheduile tab and click the button to add a new schedule and choose the frequency etc that you want the job to tun. If you have operators setup on your server, then you can use the notification tab to have the job email you when it completes or fails.|||Thanks soooooooo much! You are awesome!

Josql

Keywords

Hi,
I have to build a table for something like 1.000.000 books.
I need to use keywords for each book (to be able to search with the keywords
in an intranet).
I wonder the best solution to achieve this:
*Add a new text field (varchar) and then use Full Text Search index
*Add two tables, one for the keywords and one to join the books' table and
the new keywords one.
Which one of these two solutions is the best with SQL Server ?
Thanks.Hello Lionel,
Disclaimer: I don't have much knowledge on full text search.
I would do the second option. This would allow you to quickly search through
the keywords assuming you have only one word in you table.
Aaron Weiker
http://aaronweiker.com/

> Hi,
> I have to build a table for something like 1.000.000 books.
> I need to use keywords for each book (to be able to search with the
> keywords
> in an intranet).
> I wonder the best solution to achieve this:
> *Add a new text field (varchar) and then use Full Text Search index
> *Add two tables, one for the keywords and one to join the books' table
> and
> the new keywords one.
> Which one of these two solutions is the best with SQL Server ?
> Thanks.
>|||Why don't you get a document management system that can do this job for
a fraction of the cost, 2-3 orders of magnitude faster and which comes
with a query language mean for text searches? SQL was never meant for
this kind of data.|||Well, do you have a name of a programmable document manager under Microsoft
and IIS (for an intranet) ?
Whatever, SQL server should be (as Oracle do) able to index text.
And if my boss wants to keep this solution, i still don't know if my
solution is the best issue: the keywords in a separate table, and then a
third one to join the books with the keywords.
Thanks for answring and taking time.

Keyword Function in SQL Server 8.0 but not in 7.0

Hi,
SQL server 8.0 has "function" as keyword but version 7.0
doesn't. I have a table that has two columns labeled
Module, and function. I have a store procedure that calls
this two columns but since I swicht from SQL Server 7.0 to
8.0 the function its a keyword in 8.0...and I can't store
my records when I swicth to SQL version 8.0
How I can force the SP to take the name of a column as a
field instead of a keyword? I tried to place brakets but
still I can't run my store procedure...in other words I
can't store new records in my table whose field's name is
a keyword...
I define my table like this.
...
[Module]
[Function]
...using brackets...but nothing...any ideas?
Thanks,
Patty
*******************SP*******************
***********
ALTER PROCEDURE sp_LogErrors
@.ID int, @.Number int, @.Description varchar(255),
@.Application varchar(30), @.Version varchar(30), @.Source
varchar(30), @.Module varchar(30),
@.Function varchar(30), @.Occurred DateTime, @.SBCID
varchar(30), @.Machine varchar(30)
AS
INSERT INTO Error (ID, Number, Description, Application,
Version, Source, [Module], [Function], Occurred,
Machine )
values (@.ID, @.Number, @.Description, @.Application,
@.Version, @.Source, @.Module, @.Function, @.Occurred,
@.Machine)What error message do you get if you execute that procedure from Query
Analyzer?
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:b58901c3ecd3$aa67cd90$a301280a@.phx.gbl...
> Hi,
> SQL server 8.0 has "function" as keyword but version 7.0
> doesn't. I have a table that has two columns labeled
> Module, and function. I have a store procedure that calls
> this two columns but since I swicht from SQL Server 7.0 to
> 8.0 the function its a keyword in 8.0...and I can't store
> my records when I swicth to SQL version 8.0
> How I can force the SP to take the name of a column as a
> field instead of a keyword? I tried to place brakets but
> still I can't run my store procedure...in other words I
> can't store new records in my table whose field's name is
> a keyword...
> I define my table like this.
> ...
> [Module]
> [Function]
> ...using brackets...but nothing...any ideas?
> Thanks,
> Patty
> *******************SP*******************
***********
> ALTER PROCEDURE sp_LogErrors
> @.ID int, @.Number int, @.Description varchar(255),
> @.Application varchar(30), @.Version varchar(30), @.Source
> varchar(30), @.Module varchar(30),
> @.Function varchar(30), @.Occurred DateTime, @.SBCID
> varchar(30), @.Machine varchar(30)
> AS
> INSERT INTO Error (ID, Number, Description, Application,
> Version, Source, [Module], [Function], Occurred,
> Machine )
> values (@.ID, @.Number, @.Description, @.Application,
> @.Version, @.Source, @.Module, @.Function, @.Occurred,
> @.Machine)
>
>

Keyword Function in SQL Server 8.0 but not in 7.0

Hi,
SQL server 8.0 has "function" as keyword but version 7.0
doesn't. I have a table that has two columns labeled
Module, and function. I have a store procedure that calls
this two columns but since I swicht from SQL Server 7.0 to
8.0 the function its a keyword in 8.0...and I can't store
my records when I swicth to SQL version 8.0
How I can force the SP to take the name of a column as a
field instead of a keyword? I tried to place brakets but
still I can't run my store procedure...in other words I
can't store new records in my table whose field's name is
a keyword...
I define my table like this.
...
[Module]
[Function]
...using brackets...but nothing...any ideas?
Thanks,
Patty
*******************SP******************************
ALTER PROCEDURE sp_LogErrors
@.ID int, @.Number int, @.Description varchar(255),
@.Application varchar(30), @.Version varchar(30), @.Source
varchar(30), @.Module varchar(30),
@.Function varchar(30), @.Occurred DateTime, @.SBCID
varchar(30), @.Machine varchar(30)
AS
INSERT INTO Error (ID, Number, Description, Application,
Version, Source, [Module], [Function], Occurred,
Machine )
values (@.ID, @.Number, @.Description, @.Application,
@.Version, @.Source, @.Module, @.Function, @.Occurred,
@.Machine)What error message do you get if you execute that procedure from Query
Analyzer?
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Patty" <anonymous@.discussions.microsoft.com> wrote in message
news:b58901c3ecd3$aa67cd90$a301280a@.phx.gbl...
> Hi,
> SQL server 8.0 has "function" as keyword but version 7.0
> doesn't. I have a table that has two columns labeled
> Module, and function. I have a store procedure that calls
> this two columns but since I swicht from SQL Server 7.0 to
> 8.0 the function its a keyword in 8.0...and I can't store
> my records when I swicth to SQL version 8.0
> How I can force the SP to take the name of a column as a
> field instead of a keyword? I tried to place brakets but
> still I can't run my store procedure...in other words I
> can't store new records in my table whose field's name is
> a keyword...
> I define my table like this.
> ...
> [Module]
> [Function]
> ...using brackets...but nothing...any ideas?
> Thanks,
> Patty
> *******************SP******************************
> ALTER PROCEDURE sp_LogErrors
> @.ID int, @.Number int, @.Description varchar(255),
> @.Application varchar(30), @.Version varchar(30), @.Source
> varchar(30), @.Module varchar(30),
> @.Function varchar(30), @.Occurred DateTime, @.SBCID
> varchar(30), @.Machine varchar(30)
> AS
> INSERT INTO Error (ID, Number, Description, Application,
> Version, Source, [Module], [Function], Occurred,
> Machine )
> values (@.ID, @.Number, @.Description, @.Application,
> @.Version, @.Source, @.Module, @.Function, @.Occurred,
> @.Machine)
>
>

Keyword Density/Count

Hi All,

let me try to explain what I'm trying to accomplish - I really hope someone
can help.

I have a table (tblArticles) which has the following:

vcrKeywords varchar(2000)
txtBody text(8000)
vcrType varchar(128)

and this is a sample of the data

vcrKeyWords || txtBody || vcrType
key1,key2,key4,key7,key9 || <snipped body|| Site5 News
key1,key3,key6,key8,key9 || <snipped body|| Site5 News
key1,key3,key4,key5,key9 || <snipped body|| Site5 News
key1,key2,key5,key7,key8 || <snipped body|| Site5 News

What I'm trying to accomplish is to return a keyword count based on the
content of vcrKeywords (i.e. each comma seperated entry as a count.

My SQL statement originally was :

SELECT vcrKeyWords, COUNT(vcrKeyWords) AS keycount FROM tblArticles
where vcrType LIKE 'site 5%' GROUP BY vcrKeyWords ORDER BY keycount DESC

However, this simply matches the entire vcrKeyword Column and not each comma
seperated value.

Is there a way in SQL that I can achieve this or do I need to use some kind
of scripting language to accomplish it...?

Regards,

Carl.Carl,

It would take you more time than it's worth to develop a procedure to
search for keywords on the fly in that mess. And once you do create
it, it's going to be really slow because it's not going to be able to
use any indexes. Let's look at a redesign.

Looks to me like you have a many to many relationship between Keywords
and Articles.

tbl_keywords:
keyword_id (unique-idx)
keyword_name (pk)

tbl_article:
article_id (unique-idx)
txtBody
vcrType

tbl_art_key:
keyword_id (Clust-PK)
article_id (Clust-PK)

Now, for your query above with the new design:
SELECT tk.keyword_name, count(tak.article_id)
FROM tbl_keyword tk JOIN tbl_art_key tak
ON tk.keyword_id = tak.keyword_id

Cheers,
Jason Lepack

On Apr 16, 5:15 am, "news.demon.co.uk" <c...@.ocvision.comwrote:

Quote:

Originally Posted by

Hi All,
>
let me try to explain what I'm trying to accomplish - I really hope someone
can help.
>
I have a table (tblArticles) which has the following:
>
vcrKeywords varchar(2000)
txtBody text(8000)
vcrType varchar(128)
>
and this is a sample of the data
>
vcrKeyWords || txtBody || vcrType
key1,key2,key4,key7,key9 || <snipped body|| Site5 News
key1,key3,key6,key8,key9 || <snipped body|| Site5 News
key1,key3,key4,key5,key9 || <snipped body|| Site5 News
key1,key2,key5,key7,key8 || <snipped body|| Site5 News
>
What I'm trying to accomplish is to return a keyword count based on the
content of vcrKeywords (i.e. each comma seperated entry as a count.
>
My SQL statement originally was :
>
SELECT vcrKeyWords, COUNT(vcrKeyWords) AS keycount FROM tblArticles
where vcrType LIKE 'site 5%' GROUP BY vcrKeyWords ORDER BY keycount DESC
>
However, this simply matches the entire vcrKeyword Column and not each comma
seperated value.
>
Is there a way in SQL that I can achieve this or do I need to use some kind
of scripting language to accomplish it...?
>
Regards,
>
Carl.

|||Ah, the joys of not proofreading...

Now, for your query above with the new design:
SELECT tk.keyword_name, count(tak.article_id) keycount
FROM tbl_keyword tk
JOIN tbl_art_key tak
ON tk.keyword_id = tak.keyword_id
JOIN tbl_article ta
ON ta.article_id = tak.article_id
WHERE ta.vcrType LIKE 'site 5%'
GROUP BY tk.keyword_name
ORDER BY keycount DESC

On Apr 16, 8:41 am, "Jason Lepack" <jlep...@.gmail.comwrote:

Quote:

Originally Posted by

Carl,
>
It would take you more time than it's worth to develop a procedure to
search for keywords on the fly in that mess. And once you do create
it, it's going to be really slow because it's not going to be able to
use any indexes. Let's look at a redesign.
>
Looks to me like you have a many to many relationship between Keywords
and Articles.
>
tbl_keywords:
keyword_id (unique-idx)
keyword_name (pk)
>
tbl_article:
article_id (unique-idx)
txtBody
vcrType
>
tbl_art_key:
keyword_id (Clust-PK)
article_id (Clust-PK)
>
Now, for your query above with the new design:
SELECT tk.keyword_name, count(tak.article_id)
FROM tbl_keyword tk JOIN tbl_art_key tak
ON tk.keyword_id = tak.keyword_id
>
Cheers,
Jason Lepack
>
On Apr 16, 5:15 am, "news.demon.co.uk" <c...@.ocvision.comwrote:
>
>
>

Quote:

Originally Posted by

Hi All,

>

Quote:

Originally Posted by

let me try to explain what I'm trying to accomplish - I really hope someone
can help.

>

Quote:

Originally Posted by

I have a table (tblArticles) which has the following:

>

Quote:

Originally Posted by

vcrKeywords varchar(2000)
txtBody text(8000)
vcrType varchar(128)

>

Quote:

Originally Posted by

and this is a sample of the data

>

Quote:

Originally Posted by

vcrKeyWords || txtBody || vcrType
key1,key2,key4,key7,key9 || <snipped body|| Site5 News
key1,key3,key6,key8,key9 || <snipped body|| Site5 News
key1,key3,key4,key5,key9 || <snipped body|| Site5 News
key1,key2,key5,key7,key8 || <snipped body|| Site5 News

>

Quote:

Originally Posted by

What I'm trying to accomplish is to return a keyword count based on the
content of vcrKeywords (i.e. each comma seperated entry as a count.

>

Quote:

Originally Posted by

My SQL statement originally was :

>

Quote:

Originally Posted by

SELECT vcrKeyWords, COUNT(vcrKeyWords) AS keycount FROM tblArticles
where vcrType LIKE 'site 5%' GROUP BY vcrKeyWords ORDER BY keycount DESC

>

Quote:

Originally Posted by

However, this simply matches the entire vcrKeyword Column and not each comma
seperated value.

>

Quote:

Originally Posted by

Is there a way in SQL that I can achieve this or do I need to use some kind
of scripting language to accomplish it...?

>

Quote:

Originally Posted by

Regards,

>

Quote:

Originally Posted by

Carl.- Hide quoted text -

>
- Show quoted text -

|||I agree with Jason that normalizing the design of the table for keywords
will be most beneficial. It adds so much power to what you can do with those
keywords.

A couple notes that may help too, especially if you are not in a position to
change table design:

- Scripting and client side languages have a very good support to deal with
lists and arrays. Most of them implement some sort of "split" function where
passing a list and delimiter as parameters will give you an array that will
be much easier to deal with. Plus they do it fast.

- Erland Sommarskog has excellent articles on Arrays and Lists in SQL Server
that can help you to normalize the existing data and use it more
efficiently:
http://www.sommarskog.se/arrays-in-sql-2005.html
http://www.sommarskog.se/arrays-in-sql-2000.html
- If you just need the count of keywords and your keyword delimiter is
always a comma, then you can write something like this to get the count:

SELECT vcrKeywords,
CASE
WHEN LEN(vcrKeyWords) = 0 THEN 0
ELSE
LEN(vcrKeyWords) - LEN(REPLACE(vcrKeyWords, ',', '')) + 1
END AS keycount
FROM tblArticles
WHERE vcrType LIKE 'site 5%'
GROUP BY vcrKeyWords
ORDER BY keycount DESC

Not sure if your keywords column is a blank string or NULL when there are no
keywords associated, so you can tune up the first CASE condition accordingly
to handle the case when there are 0 keywords.

HTH,

Plamen Ratchev
http://www.SQLStudio.com|||Oops, I just did a copy/paste on the bottom part of your query... Not sure
if you need the GROUP BY at all. If the intent is to summarize counts and
there are multiple occurrences of the same set of keywords, then you can
just add SUM for the CASE expression.

Plamen Ratchev
http://www.SQLStudio.com|||

Quote:

Originally Posted by

Is there a way in SQL that I can achieve this or do I need to use
some kind of scripting language to accomplish it...?
>
Regards,
>

Hi All,

Thanks for the pointers - I'd kind of figured it was too much hassle to be
worth it and unfortunately I've inheritied this database which is 3+ years
old and has *a lot* of content in it, so redesigning the schema would be a
huge undertaking.

I'm already using FTS Contains Clause to get the data out, so I'm going to
have to make do with that for now...

Thanks anyway,

Carl.|||news.demon.co.uk wrote:

Quote:

Originally Posted by

Thanks for the pointers - I'd kind of figured it was too much hassle to be
worth it and unfortunately I've inheritied this database which is 3+ years
old and has *a lot* of content in it, so redesigning the schema would be a
huge undertaking.

The question is not how much content, but how many tables and how many
things looking at those tables. (And if you can create views based on
the new tables that look like the old ones, then you can avoid having
to redesign the things-looking-at-them right away.)