I encounter one problem when I want to implement my thought. My thought
is that user want to search a record of someone but maybe user would
type wrong name or spell name wrong. I wish to compare the string which
user inputed to the database column using "Socre Method". "Score
Method" has a variable "grade" to accumulate the score. I want to
convert the string to char array, and compare the char one by one. If
the string is more accurate , the grade is more high. At last, I choose
the most higher score record to show. How to do this thought with tsql?
Could give me some tips or guide to learn? I will appreciate your
kindness, thanks.
Hi! can some one help me guide to knwo as to how do we compare teh database schemas of two databases.. i mean in case i have to compare the schema of 2 ole dbs or 2 sql databases then i can do it using teh XML schema and compare but then in case i need to compare a SQL schema and a oledb schema then how do i go about it.. since the long datatype in ms access ( using ole db) is text in SQL and tiny int is int16... there are some other discripancies liek that. So how do i go about checking teh database schemas for such disperate DBs. Can soem one tell me some site or give soem guidance in this regard. Please help me as i need it for my project where i need to compare teh schema and alert when they are not compatible.. Please help...thanks in advance..
Assuming a table with a column defined char or varchar. I have a SQL query like this :
Select * from table1 where column1='Building'
It returns the same result that
Select * from table1 where column1='BUILDING'
It is my understanding SQL Server (verison 7 or 2000, I tried on both) is lower/capital insensitive by default when it is installed. If I want SQL Server to be case-sensitive with my char or varchar columns, where can I set it?
Is it at database level or server level I can find this setting . What is the setting that control it?
Best Regards, Alain Gagne, Lead DBA gagnea@msagroup.com
Our dev team wants to introduce a complex key, which is made up of  EventDate        datetime2(5)     = ‘2015-10-22 10:19:59.12345’ ConsumerID     bigint               = 1234 SiteID              tinyint              = 15
EventDate will be converted to a bigint and then to a hex value
The hex values will then be concatenated together and stored as a string (varchar). The application will handle the creation of the complex key
  = ‘1BF714C8A0D4F699-4D2-F’
I am trying to argue against this approach and get them to store the values in their native form, in separate columns as a natural key.
To make matters worse, we need to use partitioning, where the partition boundary will be on a hexed datetime2(5) at weekly intervals. I was really hoping a proof of concept would show that hex string comparison could potentially put a row under a wrong partition, but so far, on an 8 million rowset, it is working fine.
For example, in the proof of concept, right partition boundary:
= '1BF398C373960580-2FAF3003-2'  (2014-12-14 23：59：00.00000-800010243-2）
The prior partition boundary is 1
= 'BF3983242B9C000' (2014-12-08 00:00:00.00000) Â How is this working, given that the string lengths (varchar) are different and the row value contains dashes and the partition boundaries are smaller in length without dashes? How varchar string comparison is working here?
I'm working on a program that reads in zip codes and then does some checking on them. Occasionally, the end user typos or transposes digits. It's easy enough to identify bad zip codes, but I would like to offer up a single recommendation. The problem is that there are often multiples to choose from. Is there a function or has anyone tried to figure out the delta between two strings?
I'm trying to come up with a formula that will calculate the number of lines where two conditions are true.First, SLA must be either breached or achieved.And the second condition must be that the "country" and SLO group must be the same (these two values are located in different tables. So far I have only accomplished the first....
I have tried adding FIND, EXACT or USERELATIONSHIP to the formula to no avail.... I keep running into the same error."The value for 'SLO Group' cannot be determined. Either 'SLO Group' doesn't exist, or there is no current row for a column named 'SLO Group'."
What i need is to create a function that compares 2 strings variables and if those 2 variables doesn't have at least 3 different characters then return failure , else return success.
Hi all,I have created this simple Stored procedure. But it gives me wrong result when I pass a parameter to it. But if I hard code it, it gives me the right result. The I check if the field value of 'Email' is exactly equal to the parameter '@Email'. The field 'Email' is varchar, and CID is integer. CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = @Emailreturn Instead, if I check the value directly, it gives me correct answer. The the following code works fine when I typethe Email directly in the code. CREATE PROCEDURE EmailExists @Email varcharASSELECT CIDFROM CustomersWHERE Customers.Email = 'tomyseba@yahoo.com' return Can anyone tell me the reason for it. Thanking you in advance
DECLARE @Teams AS TABLE(Team VARCHAR(3)) INSERT INTO @Teams SELECT 'IND' UNION SELECT 'SA' UNION SELECT 'AUS' select Team from @Teams where Team > 'AUS'
[code]....
co-relation between comparison operators in WHERE Clause and the respective output.
how SQL 2012 would treat a literal string for a comparison similar to below. I want to ensure that the server isn't implicitly converting the value as it runs the SQL, so I'd rather change the data type in one of my tables, as unicode isn't required.
Declare @T Table (S varchar(2)) Declare @S nvarchar(255) Insert into @T Values ('AR'), ('AT'), ('AW') Set @S = 'Auto Repairs' Select * from @T T where case @S when 'Auto Repairs' then 'AR' when 'Auto Target' then 'AT' when 'Auto Wash' then 'AW' end = T.STo summarise
in the above would AR, AT and AW in the case statement be treated as a nvarchar, as that's the field the case is wrapped around, or would it be treated as a varchar, as that's what I'm comparing it to.
I want to do something like this in a stored proc:------Create Procedure dbo.GetPatients@PatientIdList varchar(200) -- comma separated list of PatientIDsAsSelect *From PatientsWhere PatientId In (@PatientIdList)------I know the above won't work, but of course what I want is if@PatientIdList = '1,2,3' then I want Patient records with PatientIds1, 2, and 3 returned.It looks like the only way to do this is to build the SQL statement asa string within the stored procedure ... which pretty much defeats theusefulness of using precompiled sprocs as I understand it (better offbuilding a dynamic query against a View in that case).Thoughts?Joel Thornton ~ <groups@joelpt.eml.cc>
How can i read in my C# client application values returned from sqlpipe send(string)/send(reader method from CLR stored procedure.....following is example of a stored procedure.I want to read datetime string in my C# client
I have a table called enablers , with the following data
title Raiser Assignedto book Fred John Apple Peter Peter Orange Bill Roger Cup John Fred
For each time a users name appears in the raiser column they get 1 point, for each time a users name appears in the Assignedto column they get 1 point , but if their name appears in both Raiser and Assignedto for a particular row they only get 1 point not 2 points, I then need a count of raiser points plus a count of assignedto points to give a total points score ( raised plus assignedto)..I am looking how to get the output like below
Name Total Points Fred 2 Peter 1 Bill 1 John 2 Roger 1
I've got a SPROC that generates a recordset of user vote tallies (they're calculated in a separated SPROC). The user submissions are grouped by a GUID value so as to remain unique for a user's submission (each user can have multiple submissions.
The problem is that the recordset returned displays ALL the users, and I'd like to only select the highest score for each user. So, if I have 500 submissions from 3 users (User1 and User2 submit once each and User3 submits 497 times), the total recordset will have 3 rows - being the highest score per user, discounting the others.
Here's my base query:
SELECT a.UserID,a.Name AS [Name],SUM(b.TotalTally) AS [TotalPoints] FROM Users a INNER JOIN Ballots b ON a.UserID = b.UserID GROUP BY a.UserID, a.Name,b.SubmissionGUID ORDER BY [TotalPoints] DESC,[Name] ASC
...and I've been able to get the highest vote per user, discounting duplicate entries, by using this:
SELECT a.UserID,MAX(b.TotalTally) AS [TotalPoints] FROM Users a INNER JOIN Ballots b ON a.UserID = b.UserID GROUP BY a.UserID
How can I write combine the two in a nested subquery to display only the top score per user?
I have recently decided to dedupe my data but i am having a problem after running fuzzy grouping with the query on updating which duplicate to keep
_key_in is unique, _key_out is the duplicates so for example:
_key_in , _key_out , name , score , dedupe 1 , 1 , ron , 10 , purge 2 , 1 , ronn , 15 , keep 3 , 3 , john , 5 , keep 4 , 4 , matt , 15 , keep 5 , 4 , mat , 10 , purge 6 , 4 , matt , 15 , purge
I want to keep the _key_out with the higher score by setting the field de_dupe to 'keep' and the remainder to 'purge'. The score can also be the same within a duplicate so in the case it is the same i just need to keep one it doesnt matter which one. The query i have below nearly works but it marks duplicates with the same score as keep.
Code: UPDATE b SET b.dedupe_result = 'keep' FROM [BusinessListings].[dbo].[MongoOrganisationACTM1Destination] b INNER JOIN
Hi,I am trying to write a method which needs to call a stored procedure and then needs to get the response of the stored procedure back to the variable i declared in the method. private string GetFromCode(string strWebVersionFromCode, string strWebVersionString) { //call stored procedure } strWebVersionFromCode = GetFromCode(strFromCode, "web_version"); // is the var which will store the response.how should I do this?Please assist.
I have a table with score info for each group, and the table also contains historical data, I need to get the ranking for the current week and previous week, here is what I did and the result is apparently wrong:
select CurRank = row_number() OVER (ORDER BY cr.CurScore desc) , cr.group_name,cr.CurScore , lastWeek.PreRank, lastWeek.group_name,lastWeek.PreScore from (select group_name, Avg(case when datediff(day, asAtDate, getdate()) <= 7 then sumscore else 0 end) as CurScore
[Code] ....
The query consists two parts: from current week and previous week respectively. Each part returns correct result, the final merged result is wrong.
Hi, I just have a Dataset with my tables and thats it I have a grid view with several datas on it no problem to get the data or insert but as soon as I try to delete or update some records the local machine through the same error Unable to find nongeneric method... I've try to create an Update query into my table adapters but still not working with this one Also, try to remove the original_{0} and got the same error... Please help if anyone has a solution
I bought the book €œData Mining with SQL Server 2005€?, but I can€™t find the solution to a problem I have.
I want to retrieve from C# the logistic regression Attribute Value (AV) Scores for the Logistic Regression Algorithm. I can see the Scores from the Microsoft Logistic Regression Viewer (the same of Neural Network Viewer), but I cannot retrieve them via DMX, OLEDB or similar.
Otherwise, is there a formula that I can use to compute that score from the coefficient, support, or probability values of the Attribute Value pair (I can read this values from DMX)? I can access to them via DMX:
NODE_DISTRIBUTION -> SUPPORT and PROBABILITY ATTRIBUTE_VALUE...
with a query like
SELECT FLATTENED (SELECT ATTRIBUTE_NAME, ATTRIBUTE_VALUE FROM NODE_DISTRIBUTION WHERE VALUETYPE = ... ) FROM [MyModel].CONTENT WHERE NODE_TYPE ....
Hi, I have just run a simple data set through a model to predict a simple true or false value (i.e. binary output) The Lift Chart/Mining Legend in Analysis Services shows three results €“ Score, Population Correct (%), and Predict Probability (%)
Population Correct I beleive is the percentage of predictions it got right out of the total number of predictions it tried to make. Is this correct?
However, I can€™t work out how the other two are derived in particular the 'SCORE'. To give a live example the scores were as follows:
Model Score Pop Correct Pred Probability Decision Trees 0.83 76.59% 54.28% Neural Network 0.75 67.63% 50.05% Ideal Model 100.00%
Can anyone help with this and give a detailed explanation?
This question is regarding the LogRegHelper - "A scorecard for Logistic Regression models" example in sqlserverdatamining Tips and Tricks page. I launched TestLogReg (Analysis Services Database associated with the project) and ran Logistic Regression over that. While the LogReg shows the highest score for IQ (107 - 121), a score of 558, the Logistic Regression shows that Parent Encouragement has the highest score for the case College Plans = 'Plans to Attend'. Can someone verify this and clarify?
I have a few other questions with LR
- In SQL Server 2005 LR Mining Model Viewer "favors" chart, what algorithm is used for generating Scores?
- Can I use this score as a feature selector? Higher score => stronger predictor (input)
- Is the coefficient weight algorithm used in LogReg wrong ?
I am new to the topic T-SQL. I am trying to use T-SQL to merge the content of two tables (table1 and table2) into one table making sure there are no duplication.
I wonder if any body can let me have a simple code.
Hey guys what would be the easiest way to create a report of value changes for particular records from one day to the next..... ? Any suggestions would be greatly appreciated....
Hi there! can anyone help me out??? I need to compare a date from the database and the system date which will be coded in a store procedure in SQL... HELP!!!!!
I have a table with a field with a bit datatype. When I execute the stored procedure line if @bitvalue = 1 begin ... and the value is passes as 0 the statements beneath the begin execute. What am I doing wrong?
Is there a way to compare two similar tables? I'm more interested in finding out if the data content is exactly the same or not between the two tables.
We are converting our project into new version. They have done lot of changes in new version including normalization/denormalization. I need to compare the old and new database. Do you have any standard script or procedure like SQL Compare software?. Let me know what are the possibilties we need to check. Your help appreciated. Thanks, Ravi
