Showing posts with label assign. Show all posts
Showing posts with label assign. Show all posts

Tuesday, March 20, 2012

asssigning values to multiple vars in a SP in one go (without temp table)

I have to select several field values from a table and need to assign them to different variables in my SP.

Here's what I do now:

declare

@.ReceiverEmailnvarchar(50)

SET

@.ReceiverEmail=(SELECT EmailFROM UsersWHERE UserCode=@.UserCodeOwner)

declare

@.UsernameSendernvarchar(50)

SET

@.UsernameSender=(SELECT UsernameFROM UsersWHERE UserCode=@.UserCodeOwner)
As you can see I have to search the Users table twice: once for the Email and a second time for the Username...and all that based on the SAME usercode...:S
So, is there an option where I only have to search the table once and return the Email and UserName fields and assign them to my variables (without using a temp table...)?

Peter,

i dont know off the top of my head a way to get around the 2X search without the temptable, unless you use a table variable instead which in principle is still the same thing as your temp table. if the result set of email and usernames is not that big, then the table variable may save you a bit since it is being run in memory. I know this is not the answer your probably looking for, but its all i have...good luck!

|||the reason I dont want to use a temp table is because i've read that it might cause concurrency conflicts amongst others...
Is that still true in SQL Server 2005?
Otherwise I might as well go with the temp table..|||

Hi there,

try with this code it works

DECLARE @.RECEIVEREMAILNVARCHAR(50)DECLARE @.USERNAMESENDERNVARCHAR(50)
SELECT
@.RECEIVEREMAIL = EMAIL,
@.USERNAMESENDER = USERNAME
FROM USERS
WHERE USERCODE = @.USERCODEOWNER


Regards,

Fernando

|||

It sude did!
Thanks!

Monday, March 19, 2012

Assing Report Parameters

Can you evaluate and assign a new value to a parameter in an expression for a textbox?

Did you just curse?

I'm not even sure what you're asking. It's too general. What are you trying to do?

|||

Basically you want to assign a new value to the Parameter after the user selects his option. is that correct?

If yes, you will not be able to reassign this parameter but what you can do is

1. create another hidden parameter

2. Make sure that this is placed next tothe first parameter

3. Now, you can use the expression to assign the value to this hidden parameter based on what is selected in the first parameter.

Hope this answers your question...or another option is to take it back to SQL and then use the expression in SQL to reassign the values.

assigning xml output

I have a small question.
I am recently started using 2005 and I want to assign results of following
query in to a variable of any type.
select top 1 * from dbo.authors for xml auto
Please let me know how do I achieve this.
Kishortry using for xml path()
--
"kishor" wrote:

> I have a small question.
> I am recently started using 2005 and I want to assign results of following
> query in to a variable of any type.
> select top 1 * from dbo.authors for xml auto
>
> Please let me know how do I achieve this.
> Kishor|||and use it this way. Hope this helps.
declare @.a varchar(8000)
set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
select @.a|||Hi
I got error.
Line 2: Incorrect syntax near 'xml'.
Kishor
"Omnibuzz" wrote:

> and use it this way. Hope this helps.
> declare @.a varchar(8000)
> set @.a= (select top 1 * from dbo.Authors for xml path('AUTHORS') )
> select @.a
>|||This works only is SQL Server 2005. You are using SQL 2005 right?

assigning weight to columns with fulltext (is it possible?)

Hello all,
I have a table which has several fulltext enabled columns.
Title, Summary, Description
I was wondering whether it is possible to assign some kind of weight
to the columns? For example, I find it more relevant if the keywords
are in the title than in the description. Therefore I would like
records that have the matching keywords in the title to come up first.
I'm thinking the way to achieve this is by assigning weight to the
columns, but am not sure how to go about doing this.
Any advice is much appreciated.
PS. I did have something going a while ago where I used CONTAINSTABLE
and severals join plus some variables which contained the weight and
used that on the RANK value. Thing was, it was REALLY really slow on
10,000+ records.
On Dec 13, 7:09 pm, Pacific Fox - Web Design Brisbane
<tacofl...@.gmail.com> wrote:
> Hello all,
> I have a table which has several fulltext enabled columns.
> Title, Summary, Description
> I was wondering whether it is possible to assign some kind of weight
> to the columns? For example, I find it more relevant if the keywords
> are in the title than in the description. Therefore I would like
> records that have the matching keywords in the title to come up first.
> I'm thinking the way to achieve this is by assigning weight to the
> columns, but am not sure how to go about doing this.
> Any advice is much appreciated.
|||Unfortunately the join approach is the only solution to something like this.
http://www.zetainteractive.com - Shift Happens!
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Pacific Fox - Web Design Brisbane" <tacofleur@.gmail.com> wrote in message
news:facf10c4-82e5-4fac-9752-3921c1504650@.d4g2000prg.googlegroups.com...
> PS. I did have something going a while ago where I used CONTAINSTABLE
> and severals join plus some variables which contained the weight and
> used that on the RANK value. Thing was, it was REALLY really slow on
> 10,000+ records.
> On Dec 13, 7:09 pm, Pacific Fox - Web Design Brisbane
> <tacofl...@.gmail.com> wrote:
>

Assigning values to multiple variables (via subqueries) for use in an update

Hi, figured out where I was going wrong in my post just prior, but is
there ANY way I can assign several variables to then use them in an
Update statement, for example (this does not work):

ALTER PROCEDURE dbo.UpdateXmlWF
(
@.varWO varchar(50)
)
AS
DECLARE @.varCust VARCHAR(50)
SELECT @.varCust = (SELECT Customer FROM tblWorkOrders
WHERE WorkOrder=@.varWO)

DECLARE @.varAssy VARCHAR(50)
SELECT @.varAssy=(SELECT Assy FROM tblWorkOrders
WHERE WorkOrder=@.varWO)

UPDATE statement here using declared variables...

I can set one @.variable but not multiple. Any clues? kinda new to
this.

Thanks,
KathyKathy,

See my response to your previous post.

I think you want to try this instead:

DECLARE @.varCust varchar(50), varAssy varchar(50)
SELECT @.varCust = Customer,
@.varAssy = Assy
FROM tblWorkOrders
WHERE WorkOrder = @.varWO

UPDATE table
SET field = @.varCust,
field2 = @.varAssy
where somefield = somevalue

In fact, you could condense this to the following:

UPDATE table
SET field = tblWorkOrders.Customer,
field2 = tblWorkOrders.Assy
FROM tblWorkOrders
JOIN table ON tblWorkOrders.PK = table.FK
WHERE tblWorkOrders.WorkOrder = @.varWO
AND otherconditions...

Hope this helps.

--
-Chuck Urwiler, MCSD, MCDBA
http://www.eps-software.com|||Kathy,

You don't need two separate SELECT statements to assign values to your
variables. You can do it like this:

DECLARE @.varCust VARCHAR(50),
@.varAssy VARCHAR(50)

SELECT @.varCust = Customer,
@.varAssy = Assy
FROM tblWorkOrders
WHERE WorkOrder = @.varWO

But I don't understand your problem with inserting. You can modify as many
columns as you want in UPDATE statement. What's your problem here?
By the way, if these variables have been declared just to use in your UPDATE
statement, then you don't really need them. You can join tblWorkOrders table
with whatever table you want update and fetch values from tblWorkOrders
directly into your destination table.

Shervin

"KathyB" <KathyBurke40@.attbi.com> wrote in message
news:75e8d381.0310030718.82cad7f@.posting.google.co m...
> Hi, figured out where I was going wrong in my post just prior, but is
> there ANY way I can assign several variables to then use them in an
> Update statement, for example (this does not work):
> ALTER PROCEDURE dbo.UpdateXmlWF
> (
> @.varWO varchar(50)
> )
> AS
> DECLARE @.varCust VARCHAR(50)
> SELECT @.varCust = (SELECT Customer FROM tblWorkOrders
> WHERE WorkOrder=@.varWO)
> DECLARE @.varAssy VARCHAR(50)
> SELECT @.varAssy=(SELECT Assy FROM tblWorkOrders
> WHERE WorkOrder=@.varWO)
> UPDATE statement here using declared variables...
> I can set one @.variable but not multiple. Any clues? kinda new to
> this.
> Thanks,
> Kathy

Sunday, March 11, 2012

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

Assigning values based on InitialToggleState of a control in SSRS

Hi,

I need to assign the value for a field in a report based on Expand/Collapse state of another field.

Eg. If Collapsed, the value should be "AA" else if Expanded "BB".

Is there any way to get the value of InitialToggleState for any field in SSRS.

Thanks in advance.

Sathya

Use True for Collapsed and False for Expanded.

As explained at the following link

http://www.manning-sandbox.com/thread.jspa?threadID=14881&tstart=60

assigning Select results to local vars in SP

Hi. I'd like to assign the results of a select statement to a local
variables in my stored procedure. My intent is something like this:
SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
only, some how I'd like to get the field2,field3 into variables. Can
this be done?
Thanks in advanceFirst of all, do not use TOP without using ORDER BY, unless selecting
somewhat random results is required (which I gues is not).
Other than that, this is the way to go:
select @.variable_name = owner.table.colum
from owner.table
where (owner.table.another_column = @.parameter)
Don't forget to look up using local variables in Books Online.
ML|||Johnny,
Something like this:
USE Pubs
GO
CREATE PROC TESTPROC
@.AID varchar(11)
AS
DECLARE @.FName varchar(30)
DECLARE @.LName varchar(30)
SELECT @.FName = au_fname, @.LName = au_lname
FROM authors
WHERE au_id = @.AID
PRINT @.FName + ' ' + @.LName
GO
EXEC TESTPROC '172-32-1176'
HTH
Jerry
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127950731.353336.297780@.g43g2000cwa.googlegroups.com...
> Hi. I'd like to assign the results of a select statement to a local
> variables in my stored procedure. My intent is something like this:
> SELECT TOP 1 field1,field2,field3 FROM table WHERE field1 = @.InParam
> only, some how I'd like to get the field2,field3 into variables. Can
> this be done?
> Thanks in advance
>|||Thanks Jerry, I'll try this out!|||Just be careful! If the SELECT returns more than 1 rows, you will *not* get
an error. The
variable(s) will contain the value for an unspecified row.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Johnny Ruin" <schafer.dave@.gmail.com> wrote in message
news:1127953579.251983.177820@.o13g2000cwo.googlegroups.com...
> Thanks Jerry, I'll try this out!
>

Assigning query output to a variable

I am running a query that will return 1 field from 1 row. What is the
syntax to assign that to a variable.
Samplecode:
Declare @.DataFilePath varchar(200)
Declare @.DBName varchar(200)
Set @.DBName = 'TestDB'
@.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
@.DBName
Thanks!
Ron@.DataFilePath = select MAX(FileName) From master.dbo.sysaltfiles WHERE name
=
@.DBName
Or MIN. Since you are getting one row, it does not do much, but it does
tell SQL Server that only one value is coming out of the select.
RLF
"RSH" <way_beyond_oops@.yahoo.com> wrote in message
news:Op0ca4p%23FHA.1032@.TK2MSFTNGP11.phx.gbl...
>I am running a query that will return 1 field from 1 row. What is the
>syntax to assign that to a variable.
> Samplecode:
> Declare @.DataFilePath varchar(200)
> Declare @.DBName varchar(200)
> Set @.DBName = 'TestDB'
> @.DataFilePath = select FileName From master.dbo.sysaltfiles WHERE name =
> @.DBName
>
>
> Thanks!
> Ron
>
>

Assigning properties to variables

I have a package level Event which runs a stored procedure, to log the error. What I want to do is assign some of the properties of the erroring task to package level variables so that I can use them as parameters.

I can see how to assign properties from variables, but not the other way round. Tongue Tied
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

1. Create some Variables you need in the Variables Window
2. Create a Script Task and put you Readonly and Readwrite Varibales at the
Properties of the Script Task Editor.
3. Use the Design Scritp Button at this Task to define a Script.
4. My Script was designed to create dynamic SQL Statements:

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

Assigning properties to variables

I have a package level Event which runs a stored procedure, to log the error. What I want to do is assign some of the properties of the erroring task to package level variables so that I can use them as parameters.

I can see how to assign properties from variables, but not the other way round. Tongue Tied
Can anyone explain to me how to do this?

Many thanks

RudyHi,

i've done something like this with a Script Task in SSIS.

1. Create some Variables you need in the Variables Window
2. Create a Script Task and put you Readonly and Readwrite Varibales at the
Properties of the Script Task Editor.
3. Use the Design Scritp Button at this Task to define a Script.
4. My Script was designed to create dynamic SQL Statements:

Dim CreateTable, SelectTable, CreateIndex, CreateConstraint, DropConstraint As String

Dim Table As String = CStr(Dts.Variables("TSDES").Value)

Dim SB As New System.Text.StringBuilder(1024)

'Create Table

SB.Append("IF NOT EXISTS (SELECT * FROM sys.tables WHERE type ='U' and name = 'Dim" & CStr(Dts.Variables("TSDES").Value).Trim & "')" & vbNewLine)

SB.Append("BEGIN" & vbNewLine)

SB.Append(" BEGIN Transaction" & vbNewLine)

SB.Append(" CREATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value).Trim & vbNewLine)

SB.Append(" (" & vbNewLine)

SB.Append(" TDTYP numeric(38, 0) NOT NULL," & vbNewLine)

SB.Append(" TDIMX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TDESC nvarchar(30) NOT NULL," & vbNewLine)

SB.Append(" TPDIX nvarchar(6) NOT NULL," & vbNewLine)

SB.Append(" TEMNO numeric(38,0) NOT NULL" & vbNewLine)

SB.Append(" ) ON [PRIMARY]" & vbNewLine)

SB.Append(" COMMIT" & vbNewLine)

SB.Append("End" & vbNewLine)

SB.Append("ELSE" & vbNewLine)

SB.Append(" TRUNCATE TABLE baan.Dim" & CStr(Dts.Variables("TSDES").Value) & vbNewLine)

Dts.Variables("CREATETABLE").Value = SB.ToString

SB.Remove(0, SB.Length)
...

That's it.
I hope this could be helpfull for you.

Kind Regards
Andy L?wen

|||I am doing exactly this with a script task.

When you setup the script task, make sure you set the 'Read/Write Variables' property in the properties dialog, or the script code will fail.

Here is a sample of the script which should give you and idea how to set the value of a package level variable.

Public Sub Main()

Dim VarName As String = ""

Try

Dim varCurrent As Microsoft.SqlServer.Dts.Runtime.Variable

VarName = "User::ObjectName"

varCurrent = Dts.Variables.Item(VarName)

' Set the current value of the Variable
varCurrent.Value = "VTDW_PROD_CMS_AccountInstance"

VarName = "Completed"

Catch Ex As Exception

Dts.TaskResult = Dts.Results.Failure

Dts.Events.FireError(1, "Validate Variables", String.Format("Missing one of the following variables [User::Phase, User::ObjectName, User::ObjectType, User::StepName]. These Variables must be defined. Current Variable ='{0}'", VarName),Nothing, 0)

Return

End Try

' Found all variables. Let the Phase run

Dts.Events.FireInformation(0, "Valor DW DTS", String.Format("All step variables for Phase '{0}' and Step '{1}' have been set.", Phase, StepName), Nothing, 0, Nothing)

Dts.TaskResult = Dts.Results.Success

|||Andy

Can you explain me what your script task exactly does

I'm searching for a script that changes the sql-commands
Thx

assigning process priority to transactions?

I am using ADO 2.8 and SQL server 2000.
Is there any way to assign process priorities to multiple DB connections?
For example, consider that there is a long-time taking transaction and many
short-time transactions running on a server machine. I want to assign low
priority to the long-time transaction to prevent from starvation of
short-time transactions. Of couse, let's assume that there is no locking
influence between them.
Please reply. Thanks in advance.
Regards,
Hyun-jik BaeHi
I am affraid you cannot do that. Perhaps you want to look at SET
LOCK_TIMEOUT command.
"Bae,Hyun-jik" <imays@.NOSPAM.paran.com> wrote in message
news:upjA1nJbFHA.228@.TK2MSFTNGP12.phx.gbl...
> I am using ADO 2.8 and SQL server 2000.
> Is there any way to assign process priorities to multiple DB connections?
> For example, consider that there is a long-time taking transaction and
many
> short-time transactions running on a server machine. I want to assign low
> priority to the long-time transaction to prevent from starvation of
> short-time transactions. Of couse, let's assume that there is no locking
> influence between them.
> Please reply. Thanks in advance.
> Regards,
> Hyun-jik Bae
>

Assigning priority to users or logins

Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack
Jack,
There's no such option in SQL Server, nor can you set priority for a connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack
|||Agree with Tibor, but just to add, Ken Henderson presented an extended
stored procedure called xp_setpriority, in his book, "The Guru's Guide to
SQL Server Stored Procedures, XML, and HTML". This xp can do what you are
after.
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack
|||Tibor, is your time off, or am I missing something :-) Sure, I had a late
night last night!
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e5t$1e4MEHA.3988@.TK2MSFTNGP09.phx.gbl...
Jack,
There's no such option in SQL Server, nor can you set priority for a
connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack
|||Thats a shame. I hope they make that an enhancment in
later versions. I worked with Sybase and that is a really
useful feature. It prevents non technical (reporting)
users from bringing the machine down to its kness.

>--Original Message--
>Jack,
>There's no such option in SQL Server, nor can you set
priority for a connection.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...[vbcol=seagreen]
low[vbcol=seagreen]
is
>
>.
>

Assigning priority to users or logins

Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
HackJack,
There's no such option in SQL Server, nor can you set priority for a connection.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Agree with Tibor, but just to add, Ken Henderson presented an extended
stored procedure called xp_setpriority, in his book, "The Guru's Guide to
SQL Server Stored Procedures, XML, and HTML". This xp can do what you are
after.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack|||Tibor, is your time off, or am I missing something :-) Sure, I had a late
night last night!
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e5t$1e4MEHA.3988@.TK2MSFTNGP09.phx.gbl...
Jack,
There's no such option in SQL Server, nor can you set priority for a
connection.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Thats a shame. I hope they make that an enhancment in
later versions. I worked with Sybase and that is a really
useful feature. It prevents non technical (reporting)
users from bringing the machine down to its kness.
>--Original Message--
>Jack,
>There's no such option in SQL Server, nor can you set
priority for a connection.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
>> Is there a way in Sql ( 7 and 2000) where I can assign
low
>> priorities to certain users? That would mean that they
>> would have the last access to resources. If a higher
>> priority user came along, that would get priority for
>> resources.
>> I havent seen anything but was wondering if there
is
>> anything like that.
>> TIA,
>> Hack
>
>.
>

Assigning priority to users or logins

Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
HackJack,
There's no such option in SQL Server, nor can you set priority for a connect
ion.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message news:967a01c43386$8d1a45c0$a
001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Agree with Tibor, but just to add, Ken Henderson presented an extended
stored procedure called xp_setpriority, in his book, "The Guru's Guide to
SQL Server Stored Procedures, XML, and HTML". This xp can do what you are
after.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
Is .NET important for a database professional?
http://vyaskn.tripod.com/poll.htm
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
Is there a way in Sql ( 7 and 2000) where I can assign low
priorities to certain users? That would mean that they
would have the last access to resources. If a higher
priority user came along, that would get priority for
resources.
I havent seen anything but was wondering if there is
anything like that.
TIA,
Hack|||Tibor, is your time off, or am I missing something :-) Sure, I had a late
night last night!
--
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e5t$1e4MEHA.3988@.TK2MSFTNGP09.phx.gbl...
Jack,
There's no such option in SQL Server, nor can you set priority for a
connection.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Jack A" <anonymous@.discussions.microsoft.com> wrote in message
news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
> Is there a way in Sql ( 7 and 2000) where I can assign low
> priorities to certain users? That would mean that they
> would have the last access to resources. If a higher
> priority user came along, that would get priority for
> resources.
> I havent seen anything but was wondering if there is
> anything like that.
> TIA,
> Hack|||Thats a shame. I hope they make that an enhancment in
later versions. I worked with Sybase and that is a really
useful feature. It prevents non technical (reporting)
users from bringing the machine down to its kness.

>--Original Message--
>Jack,
>There's no such option in SQL Server, nor can you set
priority for a connection.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>
>"Jack A" <anonymous@.discussions.microsoft.com> wrote in
message news:967a01c43386$8d1a45c0$a001280a@.phx.gbl...
low[vbcol=seagreen]
is[vbcol=seagreen]
>
>.
>

Assigning parameters in a function

I am trying to create a function in SQL 2000. Im rusty on function
syntax and need to also assign some variables for use within this
function.

Example:
create function blah (@.param1 int)

declare @.var1 int, @.var2 int

--it doesnt like this method of assigning values to my
variables????
select @.var1, @.var2 = (select name, phone from customer where id =
@.param1)

--Then I need to use those variables in this next statement...

DECLARE @.Zip int
SELECT @.Results = (select zip from table2 where name = @.var1 and phone
= @.var2)

return @.Results

-----------
Any help would be greatly appreciated at filling in the missing
syntax.> --it doesnt like this method of assigning values to my
> variables????
> select @.var1, @.var2 = (select name, phone from customer where id =
> @.param1)

Try:

select @.var1= name, @.var2 = phone
from customer where id = @.param1

--
Hope this helps.

Dan Guzman
SQL Server MVP

"Dave" <funkdm1@.yahoo.com> wrote in message
news:f5174e0f.0404291539.211a5032@.posting.google.c om...
> I am trying to create a function in SQL 2000. Im rusty on function
> syntax and need to also assign some variables for use within this
> function.
> Example:
> create function blah (@.param1 int)
> declare @.var1 int, @.var2 int
> --it doesnt like this method of assigning values to my
> variables????
> select @.var1, @.var2 = (select name, phone from customer where id =
> @.param1)
> --Then I need to use those variables in this next statement...
> DECLARE @.Zip int
> SELECT @.Results = (select zip from table2 where name = @.var1 and phone
> = @.var2)
> return @.Results
> -----------
> Any help would be greatly appreciated at filling in the missing
> syntax.

Assigning one text box value to the other text box in the sql reporting services Form

Hi,

Can some one help me in this case,

I am manipulating a value for onetext.value , And I want to assign like this

seconetext.value = onetext.value in the SQl reporting services form,

Is it possible? if so can any one help me with the syntax?

-Thanks

Yes, this is possible.

In "seconetext", use this expression:

=ReportItems!onetext.Value

-Chris

Assigning multiple values to a parameter in stored procedure

Hi:
How do you write a SQL statement in a stored procedure so
that it will allow you to assign multiple values to a
parameter?
For instance in this example below, depending on what the
users select on the front-end application, the values
assign can be one customer id value or multiple customer
id values:
Create procedure dbo.SP_Test
As @.CustID varchar(3)
Select * from tblCust where customerid = @.CustID
Please help!This is probably not the best solution but it should work
create procedure sp_test
@.cust_id varchar(50)
as
set nocount on
exec ('select * from tblcust where customerid in (' + @.cust_id +')')
go
This procedure would be called as sp_test '50' for one cust_id or sp_test
'50, 55, 100' for several cust_id's

Assigning Group Numbers for millions of row

I have a table with first name, last name, SSN(social security number)
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.
Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3
I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. I now that this is not an efficient
code.
INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)
dist FLS is distinct First Name, last Name and SSN table from the
people table.
I have posted part of this question, schema one w ago. Please refer
this thread.
http://groups.google.com/group/comp...6eb380b5f2e6de6Basically, this is just a query that sorts or groups on a CASE function.
However, the catch is how we want to classify different names as "similar".
I would say that two rows should be considered similar if they have the same
SSN and the names start with the same first letter. Instead of a group
number, let's do a group code which conists of those 2 characters. Since
fname and lname may be transposed, the lowest of the 2 characters will be
encoded first followed by the highest character.
fname lname SSN grpCode
-- -- -- --
J S 1234 JS
J Smith 1234 JS
S John 1234 JS
John Smith 1111 JS
John Smith 1234 JS
Smith John 1234 JS
Dan Brown 1234 BD
select lname, fname, SSN, grpCode
from
(
select
fname,
lname,
SSN,
-- Here we calculate the grpCode:
case
when left(fname,1) <= left(lname,1) then left(fname,1)
else left(lname,1)
end as grpCode
--
from
distFLS
) as x
order by
SSN,
grpCode,
fname,
lname
<jacob.dba@.gmail.com> wrote in message
news:1143482451.181115.64620@.v46g2000cwv.googlegroups.com...
>I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
>
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
>
> I have posted part of this question, schema one w ago. Please refer
> this thread.
>
> http://groups.google.com/group/comp...6eb380b5f2e6de6
>|||The group code calculation returns only with one letter.
I have added this code on it.
-- Here we calculate the grpCode:
case
when left(fname,1) <= left(lname,1) then left(fname,1) +
left(lname,1)
else left(lname,1) +left(fname,1)
end as grpCode
--|||I didn't run it on my end.
Thanks.
<jacob.dba@.gmail.com> wrote in message
news:1143487218.339033.116420@.v46g2000cwv.googlegroups.com...
> The group code calculation returns only with one letter.
> I have added this code on it.
> -- Here we calculate the grpCode:
> case
> when left(fname,1) <= left(lname,1) then left(fname,1) +
> left(lname,1)
> else left(lname,1) +left(fname,1)
> end as grpCode
> --
>|||I fogot to mention that some of the records have middle name entered
in place of first name or last name.
fname mname lname ssn
John coleman smith 1234
john smith coleman 1234
john S coleman 1234
John C Smith 1234
John Smith 1234
John-coleman Smith 1234
Smith John 1234
During the grouping process I am concerned only about fname,lname,
ssn.(no need of middle name). If there is other suggestion to include
columns I am happy to accept.
I have the idea to assign groups if one of the initial of the names is
similar with the others considering that the SSN is the same. that
means if SSN is equal and if J or S or C are there as an initial in the
names, we can say they are in the same group.|||Just revise the case function as needed, but the concept is the same.
<jacob.dba@.gmail.com> wrote in message
news:1143490139.170226.286890@.g10g2000cwb.googlegroups.com...
> I fogot to mention that some of the records have middle name entered
> in place of first name or last name.
> fname mname lname ssn
> John coleman smith 1234
> john smith coleman 1234
> john S coleman 1234
> John C Smith 1234
> John Smith 1234
> John-coleman Smith 1234
> Smith John 1234
> During the grouping process I am concerned only about fname,lname,
> ssn.(no need of middle name). If there is other suggestion to include
> columns I am happy to accept.
> I have the idea to assign groups if one of the initial of the names is
> similar with the others considering that the SSN is the same. that
> means if SSN is equal and if J or S or C are there as an initial in the
> names, we can say they are in the same group.
>|||Consider using Integration Services as that tool has a Fuzzy Lookup and
Fuzzy Grouping tasks that were specifically designed for this type of work.
<jacob.dba@.gmail.com> wrote in message
news:1143482451.181115.64620@.v46g2000cwv.googlegroups.com...
>I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
>
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
>
> I have posted part of this question, schema one w ago. Please refer
> this thread.
>
> http://groups.google.com/group/comp...6eb380b5f2e6de6
>

Assigning group numbers for millions of data

I have a table with first name, last name, SSN(social security number)
and other columns.
I want to assign group number according to this business logic.
1. Records with equal SSN and (similar first name or last name) belong
to the same group.
John Smith 1234
Smith John 1234
S John 1234
J Smith 1234
John Smith and Smith John falls in the same group Number as long as
they have similar SSN.
This is because I have a record of equal SSN but the first name and
last name is switched because of people who make error inserting last
name as first name and vice versa. John Smith and Smith John will have
equal group Name if they have equal SSN.
2. There are records with equal SSN but different first name and last
name. These belong to different group numbers.
Equal SSN doesn't guarantee equal group number, at least one of the
first name or last name should be the same. John Smith and Dan Brown
with equal SSN=1234 shouldn't fall in the same group number.

Sample data:
Id Fname lname SSN grpNum
1 John Smith 1234 1
2 Smith John 1234 1
3 S John 1234 1
4 J Smith 1234 1
5 J S 1234 1
6 Dan Brown 1234 2
7 John Smith 1111 3

I have tried this code for 65,000 rows. It took 20 minute. I have to
run it for 21 million row data. I now that this is not an efficient
code.

INSERT into temp_FnLnSSN_grp
SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
(SELECT 1 + count(*)
FROM distFLS AS c2
WHERE c2.ssn < c1.ssn
or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
substring(c1.fname,1,1) or substring(c2.lname,1,1) =
substring(c1.lname,1,1)
or substring(c2.fname,1,1) =
substring(c1.lname,1,1) or substring(c2.lname,1,1) =
substring(c1.fname,1,1))
)) AS group_number
FROM distFLS AS c1
JOIN tu_people_data AS c3
ON (c1.ssn = c3.ssn and
c1.fname = c3.fname and
c1.lname= c3.lname)

dist FLS is distinct First Name, last Name and SSN table from the
people table.

I have posted part of this question, schema one week ago. Please refer
this thread.

http://groups.google.com/group/comp...6eb380b5f2e6de6I forgot to mention that some of the records have middle name entered
in place of first name or last name.
fname mname lname ssn

John coleman smith 1234
john smith coleman 1234
john S coleman 1234
John C Smith 1234
John Smith 1234
John-coleman Smith 1234
Smith John 1234

During the grouping process I am concerned only about fname,lname,
ssn.(no need of middle name). If there is other suggestion to include
columns I am happy to accept.
I have the idea to assign groups if one of the initial of the names is

similar with the others considering that the SSN is the same. that
means if SSN is equal and if J or S or C are there as an initial in the

names, we can say they are in the same group.

Reply

jacob.dba@.gmail.com wrote:
> I have a table with first name, last name, SSN(social security number)
> and other columns.
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.
> Sample data:
> Id Fname lname SSN grpNum
> 1 John Smith 1234 1
> 2 Smith John 1234 1
> 3 S John 1234 1
> 4 J Smith 1234 1
> 5 J S 1234 1
> 6 Dan Brown 1234 2
> 7 John Smith 1111 3
>
> I have tried this code for 65,000 rows. It took 20 minute. I have to
> run it for 21 million row data. I now that this is not an efficient
> code.
>
> INSERT into temp_FnLnSSN_grp
> SELECT c1.fname, c1.lname, c1.ssn AS ssn, c3.tu_id,
> (SELECT 1 + count(*)
> FROM distFLS AS c2
> WHERE c2.ssn < c1.ssn
> or (c2.ssn = c1.ssn and (substring(c2.fname,1,1) =
> substring(c1.fname,1,1) or substring(c2.lname,1,1) =
> substring(c1.lname,1,1)
> or substring(c2.fname,1,1) =
> substring(c1.lname,1,1) or substring(c2.lname,1,1) =
> substring(c1.fname,1,1))
> )) AS group_number
> FROM distFLS AS c1
> JOIN tu_people_data AS c3
> ON (c1.ssn = c3.ssn and
> c1.fname = c3.fname and
> c1.lname= c3.lname)
>
> dist FLS is distinct First Name, last Name and SSN table from the
> people table.
> I have posted part of this question, schema one week ago. Please refer
> this thread.
> http://groups.google.com/group/comp...6eb380b5f2e6de6|||(jacob.dba@.gmail.com) writes:
> I want to assign group number according to this business logic.
> 1. Records with equal SSN and (similar first name or last name) belong
> to the same group.
> John Smith 1234
> Smith John 1234
> S John 1234
> J Smith 1234
> John Smith and Smith John falls in the same group Number as long as
> they have similar SSN.
> This is because I have a record of equal SSN but the first name and
> last name is switched because of people who make error inserting last
> name as first name and vice versa. John Smith and Smith John will have
> equal group Name if they have equal SSN.
> 2. There are records with equal SSN but different first name and last
> name. These belong to different group numbers.
> Equal SSN doesn't guarantee equal group number, at least one of the
> first name or last name should be the same. John Smith and Dan Brown
> with equal SSN=1234 shouldn't fall in the same group number.

What if you have both John Smith and Southerland Jane? Are the
same person or not?

This looks like a very difficult task, and the fact that you have
800 million rows certainly does not help to make it easier.

I think you need to scrap the idea you got from Itzik. My gut feeling
say that it will not scale.

Here is a very simple-minded solution where I've assumed that as
long as any combination of initials match, it's the same group.

CREATE TABLE [TU_People_Data] (
[tu_id] [bigint] NOT NULL ,
[count_id] [int] NOT NULL ,
[fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
[ssn] [int] NULL ,
CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
(
[tu_id],
[count_id]
) ON [PRIMARY]
) ON [PRIMARY]
GO
CREATE TABLE #initials (ssn int NOT NULL,
fname varchar(32) NOT NULL,
lname varchar(32) NOT NULL,
initials char(2) NOT NULL)
go
CREATE TABLE #ssnmania (ident int NOT NULL,
ssn int NOT NULL,
initials char(2) NOT NULL,
PRIMARY KEY(ssn, initials))
go
INSERT #initals (ssn, fname, lname, initials)
SELECT DISTINCT ssn, fname, lname,
CASE WHEN fname < lname
THEN substring(fname, 1, 1) + substring(lname, 1, 1)
ELSE substring(lname, 1, 1) + substring(fname, 1, 1)
END
FROM TU_People_Data
go
INSERT #ssnmania (ssn, initials)
SELECT DISTINCT ssn, initials
FROM #initials
go
SELECT i.ssn, i.fname, i.lname, i.initials, groupno = s.ident
FROM #initials i
JOIN #ssnmania s ON i.ssn = s.ssn
AND s.initials = i.initials
go
DROP TABLE #initials, #ssnmania, TU_People_Data

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Thanks Erland.
I have tried this procedure in the morning and it solves half of my
problem.
let me start by answering your question.
>What if you have both John Smith and Southerland Jane? Are the
> same person or not?
If these guys' SSN is the same, they are considered to be in the the
same group.
I am willing to take the chance that John Smith, Southerland Jane and
Jack Sam with similar SSN has slim chance to occur. if they exist,
they are gouped in one group number.
>>regarding your solution
In my table some of the rows for one person are displayed like this.
1.John Coleman Smith 1111 JS
2.John Smith Coleman 1111 CJ
3.Coleman John Smith 1111 CS
4.John-coleman Smith 1111 JS
5. Smith John 1111 JS
6.John Smith 2222 JS
7.J Smith 1111 JS
8 Jack Sam 3333 JS
you can see that all this guys can be grouped in the same group
name(except the 6th and 8th). I see that SSN is the major factor to
identify the groups.
So once SSN is the same then the intitals has to be one or two of the
three J or S or C.

Erland Sommarskog wrote:
> (jacob.dba@.gmail.com) writes:
> > I want to assign group number according to this business logic.
> > 1. Records with equal SSN and (similar first name or last name) belong
> > to the same group.
> > John Smith 1234
> > Smith John 1234
> > S John 1234
> > J Smith 1234
> > John Smith and Smith John falls in the same group Number as long as
> > they have similar SSN.
> > This is because I have a record of equal SSN but the first name and
> > last name is switched because of people who make error inserting last
> > name as first name and vice versa. John Smith and Smith John will have
> > equal group Name if they have equal SSN.
> > 2. There are records with equal SSN but different first name and last
> > name. These belong to different group numbers.
> > Equal SSN doesn't guarantee equal group number, at least one of the
> > first name or last name should be the same. John Smith and Dan Brown
> > with equal SSN=1234 shouldn't fall in the same group number.
> What if you have both John Smith and Southerland Jane? Are the
> same person or not?
> This looks like a very difficult task, and the fact that you have
> 800 million rows certainly does not help to make it easier.
> I think you need to scrap the idea you got from Itzik. My gut feeling
> say that it will not scale.
> Here is a very simple-minded solution where I've assumed that as
> long as any combination of initials match, it's the same group.
>
> CREATE TABLE [TU_People_Data] (
> [tu_id] [bigint] NOT NULL ,
> [count_id] [int] NOT NULL ,
> [fname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [lname] [varchar] (32) COLLATE Latin1_General_CI_AS NULL ,
> [ssn] [int] NULL ,
> CONSTRAINT [PK_tu_bulk_people] PRIMARY KEY CLUSTERED
> (
> [tu_id],
> [count_id]
> ) ON [PRIMARY]
> ) ON [PRIMARY]
> GO
> CREATE TABLE #initials (ssn int NOT NULL,
> fname varchar(32) NOT NULL,
> lname varchar(32) NOT NULL,
> initials char(2) NOT NULL)
> go
> CREATE TABLE #ssnmania (ident int NOT NULL,
> ssn int NOT NULL,
> initials char(2) NOT NULL,
> PRIMARY KEY(ssn, initials))
> go
> INSERT #initals (ssn, fname, lname, initials)
> SELECT DISTINCT ssn, fname, lname,
> CASE WHEN fname < lname
> THEN substring(fname, 1, 1) + substring(lname, 1, 1)
> ELSE substring(lname, 1, 1) + substring(fname, 1, 1)
> END
> FROM TU_People_Data
> go
> INSERT #ssnmania (ssn, initials)
> SELECT DISTINCT ssn, initials
> FROM #initials
> go
> SELECT i.ssn, i.fname, i.lname, i.initials, groupno = s.ident
> FROM #initials i
> JOIN #ssnmania s ON i.ssn = s.ssn
> AND s.initials = i.initials
> go
> DROP TABLE #initials, #ssnmania, TU_People_Data
>
>
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx|||(jacob.dba@.gmail.com) writes:
> I have tried this procedure in the morning and it solves half of my
> problem.

And the other half is? :-) I did not include the middle initial, because
I did not see that post until later.

But I guess that you could extend the logic that I posted to handle
the middle initial as well.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||you need a function that takes the first character from first name,
last name, and middle initial, and sorts them. Call it "SortInit"
So, pass "Sam Alfred Jones" and it passes back "AJS". Likewise,
"Jones Alfred Sam" is returned as "AJS".

then, create your temp table and populate it with SSN and Sortinit().
then alter table on your temp table and add an identity column.
Then make your "temp table" a permanent one, as your business rules
will change, and fundamentally what you are doing is looking for
"duplicate rows" and grouping them, and this is almost always a
multiple pass project.