Fuzzy Lookups and Groupings Algorithm

Hello,
I'm trying to clean my data using fuzzy lookup algorithm though SSIS, but i get null values everywhere. This is what i did:

I applied the fuzzy lookup in a table (tblValues). As source table i have the tblValues, and as reference table in Fuzzy Lookup i have the tblValues as well, resulting null values in all fields/columns.

Do i have to create my own reference table? If yes, how do i do that and what values will i have in this table?I didn't understand how the reference table must be in order the algorithm to work. Any suggestions?

Thank you in advance!

I'm not sure I understand what your objective is. If you are trying to remove duplicates in tblValues, you're better off using a fuzzy grouping task and not a fuzzy lookup task. See the following article for how to use both if you haven't read it yet.

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsql90/html/FzDTSSQL05.asp

|||

Thanks for the article i have already read it and i used it as reference to my project. My objective is to fill the null values with the most possible value.

I'm not sure if fuzzy does that, but in that articles says that "Fuzzy Lookup matches input records that are "dirty" (because of misspellings, truncations, missing or inserted tokens, null fields, unexpected abbreviations, and other irregularities) with clean records in a reference table. ", but i didn't understand how the reference table must be.

Functions with global variables

Hello,

I am porting a stored procedure from Oracle. It uses a variable that
remembers its previous values from each invocation. (It uses a PRAGMA
REFERENCES clause for those who are familiar with Oracle.) In other
words, the variable in a particular stored procedure acts as a global
variable. So the each invocation of the stored procedure can see its
last value, instead of its initial default value.

Is there something similar in SQLServer?There are no global variables in SQL and local variables in a stored
procedure go out of scope when the SP returns. Maybe you can put the values
you want to persist into a table?

I can think of two likely reasons for wanting to do what you have described:
an auto-incrementing ID or a user-defined aggregate function. A
auto-incrementing ID is easy: use an IDENTITY column. User-defined aggregate
functions aren't possible in SQL2000 but there are solutions for some of the
non-standard aggregates that are commonly requested (Median, Product and
String Concatenation for example).

--
David Portas
SQL Server MVP
--

Function with local parameters

I am trying to write a function that returns different values regdaring to a
input value.
Why can't I write like this. What shoudl I do instead.
I want to be able to write a select Item from GetPermission ('user', 're')
Regards
/Magnus
alter function [dbo].[GetPermissions3]
(
@.UserName varchar(50),
@.ItemName varchar(50),
)
RETURNS TABLE
AS
declare @.objecttype int
if @.ItemType='RE' set @.objecttype=1
RETURN
(
If
select i.Item, o.[name] from ItemGroups i
full outer join Groups g
on g.ItemGroup = i.ItemGroup
full outer join Users u
on u.GroupName = g.GroupName
join Control.dbo.Object o on i.item = o.object and o.no=@.objecttype
where (UserName = @.UserName or i.ItemGroup = @.UserName) and ItemName =
@.ItemName
)Please have a look at the syntax used to create functions. You are
attempting to create an inline function which must not have a function body
and which must contain a single select query within the returns clause. If
you want to include logic that cannot be handled within a single select
statement, then you will need to use the more complex table-valued function
syntax.|||Thanks!
After some searching for complex table-valued function I found the syntax
necessary!
My simple test (if someone wants to know) became:
alter FUNCTION MBTest
( @.FirstColNumber int )
RETURNS @.MyTable TABLE
( FirstCol varchar(50),
SecondCol varchar(50) )
AS BEGIN
declare @.mynum varchar(50)
select @.mynum = case @.FirstColNumber
when 0 then 'zero'
when 1 then 'one' end
insert @.MyTable (FirstCol,SecondCol) select @.mynum,FirstName from
BookingsApril2006
RETURN END
Best Regards
/Magnus
"Scott Morris" <bogus@.bogus.com> wrote in message
news:uZhVjdhbGHA.3908@.TK2MSFTNGP02.phx.gbl...
> Please have a look at the syntax used to create functions. You are
> attempting to create an inline function which must not have a function
> body and which must contain a single select query within the returns
> clause. If you want to include logic that cannot be handled within a
> single select statement, then you will need to use the more complex
> table-valued function syntax.
>

function to return multi-selected values?

Is there a function to return the multi-selected parm values in a comma
delimited string? Using RS2005. dataset1 is the source of a parameter I'll
call STORE. I have this defined as a multi-select parm. Getting error
(cannot add multi value query parameter 'STORE' for dataset 'dataset2'
because it is not supported by the data extension) on trying to select parms
that are needed for my next dataset query unless I map the parameter for
dataset2 to an expression like this STORE = Parameters!STORE.Value(0) + "," +
Parameters!STORE.Value(1)
I cant do that in reality though - I was just seeing if it would work. I
need a function that I can map the parameter to which will feed to dataset2
all the STORE values chosen (similar to above). Why does it say that it is
not supported when I can type in comma delimited values in that parameter
field and run the report? Thanks in advanceIf I understand you have this...
Parameter 1, STORE, multi-select. Source is from query (dataset1) -
something like SELECT STORE_NAME FROM STORE.
dataset2 feeds the data in your report. It has to have a where clause
based on the values selected from the STORE parameter.
You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
are using a supported database: SQL Server or... i think... Oracle).
If you are using another dbms (we use sybase for some of our reports,
and we get that error) you have to be creative...
In that case, for dataset2, I have done the following.
="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
Join(Parameters!STORE.Value, "','") & "'"
There are single quotes in there to put them around each value in STORE
(assuming it is a string). If they are integers, you can lose the
single quotes.
Good luck,
Regards,
Dan|||Thanks Dan,
Actually I am using db2 version 8.1 (database lives on a unix box). You
understood correctly that I have dataset1 which provides the selections for
the parm STORE. The parm is then mapped to a query parm that is needed for
dataset2 which is a stored procedure (no sql to manipulate there). Any ideas
there? Again ... thanks
"Dan" wrote:
> If I understand you have this...
> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> something like SELECT STORE_NAME FROM STORE.
> dataset2 feeds the data in your report. It has to have a where clause
> based on the values selected from the STORE parameter.
> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> are using a supported database: SQL Server or... i think... Oracle).
> If you are using another dbms (we use sybase for some of our reports,
> and we get that error) you have to be creative...
> In that case, for dataset2, I have done the following.
> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> Join(Parameters!STORE.Value, "','") & "'"
> There are single quotes in there to put them around each value in STORE
> (assuming it is a string). If they are integers, you can lose the
> single quotes.
> Good luck,
> Regards,
> Dan
>|||When you pass a multi-select parameter to a stored procedure you are sending
a comma separated string of values. Why your stored procedure is having
trouble with it I don't know.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"MJT" <MJT@.discussions.microsoft.com> wrote in message
news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> Thanks Dan,
> Actually I am using db2 version 8.1 (database lives on a unix box). You
> understood correctly that I have dataset1 which provides the selections
> for
> the parm STORE. The parm is then mapped to a query parm that is needed
> for
> dataset2 which is a stored procedure (no sql to manipulate there). Any
> ideas
> there? Again ... thanks
> "Dan" wrote:
>> If I understand you have this...
>> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
>> something like SELECT STORE_NAME FROM STORE.
>> dataset2 feeds the data in your report. It has to have a where clause
>> based on the values selected from the STORE parameter.
>> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
>> are using a supported database: SQL Server or... i think... Oracle).
>> If you are using another dbms (we use sybase for some of our reports,
>> and we get that error) you have to be creative...
>> In that case, for dataset2, I have done the following.
>> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
>> Join(Parameters!STORE.Value, "','") & "'"
>> There are single quotes in there to put them around each value in STORE
>> (assuming it is a string). If they are integers, you can lose the
>> single quotes.
>> Good luck,
>> Regards,
>> Dan
>>|||I dont know either. If I take the multi-value off of that parm and type in 2
comma separated values then it works just fine. Something about the
multi-value it doesnt like or I am setting up wrong. This is db2 v8.1 ...
the stored proc works to accept comma-separated values when typed in ... just
not when selected as multi-value ... I cant see what I would be doing wrong?
"Bruce L-C [MVP]" wrote:
> When you pass a multi-select parameter to a stored procedure you are sending
> a comma separated string of values. Why your stored procedure is having
> trouble with it I don't know.
>
> --
> Bruce Loehle-Conger
> MVP SQL Server Reporting Services
> "MJT" <MJT@.discussions.microsoft.com> wrote in message
> news:DED795B5-D458-481F-9AC5-8CF2B1D07B05@.microsoft.com...
> > Thanks Dan,
> > Actually I am using db2 version 8.1 (database lives on a unix box). You
> > understood correctly that I have dataset1 which provides the selections
> > for
> > the parm STORE. The parm is then mapped to a query parm that is needed
> > for
> > dataset2 which is a stored procedure (no sql to manipulate there). Any
> > ideas
> > there? Again ... thanks
> >
> > "Dan" wrote:
> >
> >> If I understand you have this...
> >>
> >> Parameter 1, STORE, multi-select. Source is from query (dataset1) -
> >> something like SELECT STORE_NAME FROM STORE.
> >>
> >> dataset2 feeds the data in your report. It has to have a where clause
> >> based on the values selected from the STORE parameter.
> >>
> >> You should be able to put WHERE STORE IN (@.STORE) in dataset2 (if you
> >> are using a supported database: SQL Server or... i think... Oracle).
> >>
> >> If you are using another dbms (we use sybase for some of our reports,
> >> and we get that error) you have to be creative...
> >>
> >> In that case, for dataset2, I have done the following.
> >>
> >> ="SELECT XXXXXX FROM TABLE WHERE STORE IN '" &
> >> Join(Parameters!STORE.Value, "','") & "'"
> >>
> >> There are single quotes in there to put them around each value in STORE
> >> (assuming it is a string). If they are integers, you can lose the
> >> single quotes.
> >>
> >> Good luck,
> >>
> >> Regards,
> >>
> >> Dan
> >>
> >>
>
>|||Bruce may be better versed in some of this than I am, but from what I
understand, a comma separated list is only sent through to the dbms
through a data extension that supports multi-value parameters. I don't
believe db2/odbc does.
What does the text from your dataset 2 look like?
If you changed it to something like the following, it should work.
="exec storedprocname '" & Join(Parameters!STORE.Value, "','") & "'"

Function to return all days in a month

Hi,

What I need to do is to join a table with all days in a range that the users inform with another table that has the values so I can have a montly report separated day by day...I don't want to create the table manually cause the range is different...

I tried to do with cursor but it's to slow..

Any better solution?

Thanks

You don't say what version of SQL Server you are using. If you are using SQL Server 2005 then you can use a recursive common table expression to do what you want. Here is a sample:

Code Snippet

DECLARE @.RepMonth as datetime

SET @.RepMonth = '2007-04-01'

-- Use a common table expression to loop over a set of years

;WITH DayList (DayDate) AS

(

SELECT @.RepMonth

UNION ALL

SELECT DATEADD(d, 1, DayDate)

FROM DayList

WHERE (DayDate < DATEADD(d, -1, DATEADD(m, 1, @.RepMonth)))

)

SELECT *

FROM DayList

|||

OK thanks. It solves for my other program....But I still need it to sql2000... =)

Thanks

|||

Been thinking about it since posting previously and I came up with the following code which works if you don't have SQL Server 2005. It depends upon having a table which simply contains the values from 0 to at least 30 (giving 31 records for 31 days). I put up to 32 in to show check the logic worked for any month - and that it can have any number of records in it. In the sample this is a table variable - but in a real system this would probably just be a little standard table.

Code Snippet

DECLARE @.ValueList table

(

ValueID int

)

SET NOCOUNT ON

INSERT INTO @.ValueList Values ( 0)

INSERT INTO @.ValueList Values ( 1)

INSERT INTO @.ValueList Values ( 2)

INSERT INTO @.ValueList Values ( 3)

INSERT INTO @.ValueList Values ( 4)

INSERT INTO @.ValueList Values ( 5)

INSERT INTO @.ValueList Values ( 6)

INSERT INTO @.ValueList Values ( 7)

INSERT INTO @.ValueList Values ( 8)

INSERT INTO @.ValueList Values ( 9)

INSERT INTO @.ValueList Values (10)

INSERT INTO @.ValueList Values (11)

INSERT INTO @.ValueList Values (12)

INSERT INTO @.ValueList Values (13)

INSERT INTO @.ValueList Values (14)

INSERT INTO @.ValueList Values (15)

INSERT INTO @.ValueList Values (16)

INSERT INTO @.ValueList Values (17)

INSERT INTO @.ValueList Values (18)

INSERT INTO @.ValueList Values (19)

INSERT INTO @.ValueList Values (20)

INSERT INTO @.ValueList Values (21)

INSERT INTO @.ValueList Values (22)

INSERT INTO @.ValueList Values (23)

INSERT INTO @.ValueList Values (24)

INSERT INTO @.ValueList Values (25)

INSERT INTO @.ValueList Values (26)

INSERT INTO @.ValueList Values (27)

INSERT INTO @.ValueList Values (28)

INSERT INTO @.ValueList Values (29)

INSERT INTO @.ValueList Values (30)

INSERT INTO @.ValueList Values (31)

INSERT INTO @.ValueList Values (32)

DECLARE @.RepMonth as datetime

SET @.RepMonth = '2007-04-01'

SELECT DATEADD(d, ValueID, @.RepMonth)

FROM @.ValueList

WHERE (DATEADD(d, ValueID, @.RepMonth) < DATEADD(m, 1, @.RepMonth))

Put an order by on the selects if you need the records in a particular order.

|||

You might want to give consideration to using a calendar table; you can find an article that describes this type of table here:

http://sqlserver2000.databases.aspfaq.com/why-should-i-consider-using-an-auxiliary-calendar-table.html

|||

GREAT !!! Thanks..you're the master....

Do you have msn....I'll add you to never forget !(or add me...it's my nickname @.hot....

|||

You may find having a 'Calendar' table to be constantly useful.

Datetime -Calendar Table
http://www.aspfaq.com/show.asp?id=2519

|||

create function udf_MakeDatesTable(

@.dtInput smalldatetime

,@.Months tinyint = 1

)

/*

this function will allow you get a table of dates

for any range of months you like,

starting at the input date.

See below for the usage

*/

returns @.TableOfDates table(dtTemp smalldatetime)

as

begin

declare @.dtEnd smalldatetime

--declare @.dtInput smalldatetime

--set @.dtInput = '1/5/2005 05:12'

--This line will strip out the Time component

set @.dtinput = convert(varchar,@.dtInput,106)

--Find our end date. Always @.Months after @.dtInput

set @.dtEnd = dateadd(m, @.Months, @.dtInput)

--declare @.TableOfDates table(dtTemp smalldatetime)

while @.dtInput < @.dtend

begin

insert into @.TableOfDates select @.dtInput

set @.dtInput = @.dtInput + 1

end

return

end

go

select * from dbo.udf_MakeDatesTable('1/15/2005',1)

Function that returns highest of two columns?

Is there a function that compares two columns in a row and will return
the highest of the two values? Something like:

Acct Total_Dollars Collected Total_Dollars_Due
11233 900.00 1000.00

Declare @.Value as money
set @.Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)
Print @.Value

This function will return 1000.00 or the Total_dollars_Due??

Is there such a creature?On 21 Sep 2004 08:41:16 -0700, Philip Mette wrote:

>Is there a function that compares two columns in a row and will return
>the highest of the two values? Something like:
>Acct Total_Dollars Collected Total_Dollars_Due
>11233 900.00 1000.00
>Declare @.Value as money
>set @.Value=GetHighest(Total_Dollars_Collected,TotalDol lars_Due)
>Print @.Value
>
>This function will return 1000.00 or the Total_dollars_Due??
>Is there such a creature?

Hi Philip,

No. But you can create a user-defined function, if you wish. Or simply use
a CASE expression:

SET @.Value = CASE WHEN a > b THEN a ELSE b END

The user-defined function might be more friendly to the eyes. Using the
CASE expression wherever you need it might be a little bit less readable,
but it will probably perform better.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)|||>> Is there a function that compares two columns in a row and will
return the highest of the two values? <<

in Oracle, there is a general GREATEST(<list>) function, but in
Standard SQL and T-SQL you nedd to use a CASE expression.

Function like SUM() but multiply

Is there a function that I can use to multiply all of the values of a column
from a result set together? Much like SUM() adds all the values together?
Or am I going to have to just use AVG() and COUNT() and multiply those 2
values together?
thnx,
Christoph
Christoph,
http://sqljunkies.com/WebLog/mosha/archive/2006/12/18/aggregate_multiplication.aspx
RLF
"Christoph Boget" <jcboget@.yahoo.com> wrote in message
news:O63$fDkwHHA.4132@.TK2MSFTNGP02.phx.gbl...
> Is there a function that I can use to multiply all of the values of a
> column from a result set together? Much like SUM() adds all the values
> together? Or am I going to have to just use AVG() and COUNT() and multiply
> those 2 values together?
> thnx,
> Christoph
>
|||On Mon, 9 Jul 2007 11:50:24 -0400, Christoph Boget wrote:

>Is there a function that I can use to multiply all of the values of a column
>from a result set together? Much like SUM() adds all the values together?
>Or am I going to have to just use AVG() and COUNT() and multiply those 2
>values together?
>thnx,
>Christoph
>
Hi Christoph,
If you're on SQL Server 2005, you can write a custom aggregate for this.
However, the technique below, which works on all versions of SQL Server,
is probably faster (though not really easy to understand for the novice
coder, and requiring some maths skills to graps as well).
(Simple version - use only if you know for sure that all values are > 0)
SELECT Grp, POWER(10.0, SUM(LOG10(Value))) AS Product
FROM YourTable
GROUP BY Grp;
(Advanced version - handles 0 and <0 values gracefully)
SELECT Grp,
CASE
WHEN MAX(CASE WHEN Value = 0 THEN 1 END) = 1 THEN 0
ELSE CASE
WHEN COUNT(CASE WHEN val < 0 THEN 1 END) % 2 = 0
THEN 1 ELSE -1
END * POWER(10.0, SUM(LOG10(NULLIF(ABS(Value),0))))
END AS Product
FROM YourTable
GROUP BY Grp;
(Advanced version, written in an even more incomprehensible way but
maybe a bit faster)
SELECT Grp,
CAST(ROUND(EXP(SUM(LOG(ABS(NULLIF(Value,0)))))*(1-SUM(1-SIGN(Value))%4)*(1-SUM(1-SQUARE(SIGN(val)))),0)
AS INT) AS Product
FROM YourTable
GROUP BY Grp;
These techniques are all described and explained in Itzik Ben-Gan's book
"Inside Microsoft SQL Server 2005 T-SQL Querying", page 358-360.
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

Function in select statement

How can I put a function in a select statement such as

SUM(code.GetValue( A, B, C, D, E)) AS TC_Reserve

I want to pass the function several values and have it perform a complex formula

and return a value. And then sum the value returned for each row.

This data is then used to create a chart "Dollars by Product Category"

Is this possible. I've only been at this for a week so I have no idea if it can done.

I get the message below

===============================================================

TITLE: Microsoft Report Designer

Could not generate a list of fields for the query.
Check the query syntax, or click Refresh Fields on the query toolbar.

ADDITIONAL INFORMATION:

Cannot find either column "code" or the user-defined function or aggregate "code.GetValue", or the name is ambiguous. (Microsoft SQL Server, Error: 4121)

It isn't possible to use a function that you've created in your report in a SQL statement. If you give an in-depth explanation of what you are trying to accomplish there may be a workaround I could help you with.

Another alternative is you could create a User Defined Function (UDF) and store it in your SQL Server database or you can create a stored procedure. Then you could reference it from a Select statement.

See this link for an intro to UDFs

http://msdn2.microsoft.com/en-us/library/ms179545.aspx

See this link for an intro to Stored Procedures

http://msdn2.microsoft.com/en-us/library/ms187451.aspx

|||

Thanks for the offer so here goes:

I need to pass

sales data (25 comma seperated value) - create an array (split function works great)

date of first activity

quantity on hand

factor (.59, .80. 1.00, blank, etc)

units (1.00, 60.0, blank)

months of sales to use ( 6 or 12)

function code (1 or 2)

cost (9999.9999)

what I forgot 1 (I'm sure I left 1 or 2 out)

what I forgot 2

=====================================================================

If function code = 1 then

if the date of first activity is < 365 days from today's date then

return 0.0

else

if the sum of the first 12 values in the sales data array = 0 then

return ( (qoh * (units * cost) ) * .90 )

else

if qoh > the sum of the first 6 values in the sales data array then

return ( (qoh * (units * cost) ) .50)

else

return 0.0

end

If fuction code = 2 then same as above but multiple the return value by the factor value

example: return ( ( (qoh * (units * cost) ) * .90 ) * factor )

Some of the data is string used as numeric values so it has to tested and converted to number.

Some of the string data could be blank so it has to tested and a default inserted.

Thanks for your efforts.

Note: Don't spend a lot of time on this it's not a required part of the report. I can do the basic report as above but I wanted to insert a chart (jazz it up and great learning experience) and I needed to have all the values done in the query so I can call it as a " jump to report". I got everything to work except the sum(function)) part. If I replace it with something like "sum(QOH) as TC_Reserve" it works great just the data is not correct.

|||

I think I understand what you want to do. This blog post has some details on a workaround. It's basically a custom aggregate hacked into Reporting Services:

http://blogs.msdn.com/bwelcker/archive/2005/05/10/416306.aspx

Let me know if this will work for you or you have some questions.

|||You may want look into Calculated Fields. You could use one based on an expression, which calls your custom code. Calculated field expressions allow you to access other field values--they will be the values from the current row.

In Report Designer, to add a calculated field right-click on the Data Set, and choose Add.... In the Add New Field dialog enter the name for the field, choose Calculated field, and then enter the expression for the Calculated Field. In your case, the expression would look something like what you mentioned. Aggregates are not supported in Calculated field expressions, though.

Your query would not have a reference to this calculation, so you would simply remove it from the field list.

Ian

Full-text search does not automatically update index when CHANGE_TRACKING AUTO

On Sql Server 2005 Standard if I insert a new row into SomeTable that has a full-text index:

insert SomeTable(keywords)values('this is a test')

and then query:

SELECT * FROM Message WHERE Contains(keywords, ' "test" ');

I get the expected rows all rows that have "test" in the keyword.

On Sql Server 2005 Express new rows are not returned unless I rebuild the catalog.

Is this a known limitation of Express?

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++

CREATE TABLE [dbo].[SomeTable](
[key] [int] IDENTITY(1,1) NOT NULL,
[keywords] [nvarchar](255) NOT NULL
CONSTRAINT [PK_SomeTable] PRIMARY KEY CLUSTERED

CREATE FULLTEXT CATALOG ft AS DEFAULT;

CREATE FULLTEXT INDEX ON [dbo].[SomeTable] KEY INDEX [PK_SomeTable] ON [ft] WITH CHANGE_TRACKING AUTO
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ADD ([keywords])
ALTER FULLTEXT INDEX ON [dbo].[SomeTable] ENABLE

Are you rebuilding the catalog everytime to get the new rows?