Display Results Based On Percentage Match

May 5, 2008

Dear Pals,

I have small requirement in my project.
I need to display the results of the WHERE clause based on percentage/ranking of exact match.

I mean the result set should be displayed based on percentage match.

For example i have the below table.

create table test
(
id int identity(1,1) primary key,
ename varchar(10)
)

insert into test(ename) select 'REG'
insert into test(ename) select 'xyz'
insert into test(ename) select 'abc'
insert into test(ename) select 'Reg'
insert into test(ename) select 'Regsxysn'
insert into test(ename) select 'psReg'

I need the output something similar as below

REG
Reg
Regsxysn
psReg

I have tried out with full text indexing but i could'nt get the required output.

Any suggestions would be appreciated.

Thanks in Advance.

View 3 Replies


ADVERTISEMENT

Display Results Based On Percentage Match

May 5, 2008

Dear Pals,

I have small requirement in my project.
I need to display the results of the WHERE clause based on percentage/ranking of exact match.

I mean the result set should be displayed based on percentage match.

For example i have the below table.

create table test
(
id int identity(1,1) primary key,
ename varchar(10)
)

insert into test(ename) select 'REG'
insert into test(ename) select 'xyz'
insert into test(ename) select 'abc'
insert into test(ename) select 'Reg'
insert into test(ename) select 'Regsxysn'
insert into test(ename) select 'psReg'

I need the output something similar as below

REG
Reg
Regsxysn
psReg

I have tried out with full text indexing but i could'nt get the required output.

Any suggestions would be appreciated.

Thanks in Advance.

View 1 Replies View Related

Display Results Based On Query

Mar 3, 2005

Hi,

I don't know if anyone has encountered this before but here goes:

I've a select clause below:
result = "Select * from person where LocalName LIKE N'" + queryLocalName + "'"

queryLocalName is an input field that allows the user to search for non-English characters in the database.

What I'm wondering is what kind of effect is the N in the where clause is having?

I can't seem to get it to work when doing it via the web. I've tested in the database itself, got it to work using the SQL Analyser but when testing on the web, it can't find because ? are appearing in the result.

View 2 Replies View Related

Need To Display Results Of A Query, Then Use A Drop Down List To Filter The Results.

Feb 12, 2008

Hello. I currently have a website that has a table on one webpage. When a record is clicked, the primary key of that record is transfered in the query string to another page and fed into an sql statement. In this case its selecting a project on the first page, and displaying all the scripts for that project on another page. I also have an additional dropdownlist on the second page that i use to filter the scripts by an attribute called 'testdomain'. At present this works to an extent. When i click a project, i am navigated to the scripts page which is empty except for the dropdownlist. i then select a 'testdomain' from the dropdownlist and the page populates with scripts (formview) for the particular test domain. what i would like is for all the scripts to be displayed using the formview in the first instance when the user arrives at the second page. from there, they can then filter the scripts using the dropdownlist.
My current SQL statement is as follows.
SelectCommand="SELECT * FROM [TestScript] WHERE (([ProjectID] = @ProjectID) AND ([TestDomain] = @TestDomain))"
So what is happening is when testdomain = a null value, it does not select any scripts. Is there a way i can achieve the behaivour of the page as i outlined above? Any help would be appreciated.
Thanks,
James.

View 1 Replies View Related

Work Out A Discount Based On A Percentage?

Sep 30, 2005

Hi all,

Hopefully a nice easy one to begin with. I have an access database which I use to build my web pages - I use sql to extract products which meet certain criteria then the pages are automatically generated. I can extract the data without any problem. An example I use is shown below:

===================================================
SELECT * FROM products WHERE products.group = 'LCD-HDTV' AND status = 'LIVE' AND tradeprice > 0 AND description3 > '' ORDER BY diagonal, tradeprice, description3
===================================================

I now want to get it to work out a discount of 1% on the products shown using the tradeprice field. I've tried many times using the SELECT and SUM functions but am not getting the syntax correct. Any help would be very much appreciated.

TIA

Jackmac

View 2 Replies View Related

Transact SQL :: Update Incoming Rows Based On Percentage Calculation

Apr 2, 2015

This is on SQL Server 2008. Please find a detailed description and the file of the data, that I am working on.

Requirements:

1.
If
'Channel'
is
not
equal to "Omnibus"
where
the 'Trans Description'is
equal to "Purchase"
and
"Redemption"
for
one purchase and
one redemption that match on 'System'
,
'Account TA Number'
,
'Product Name'
,
'Settled Date'
,
and
where
the 'Trade Amount'
of the purchase and
redemption is
within 5%,
then
display those set of records.

2.
If
deemed wash trades,
allow user to update the purchase and
redemption pair 'Trans Description'
from
"Purchase"
to "Exchange In"
and
'Trans Description'
from
"Redemption"
with
"Exchange out"

System Channel Dealer Name Firm Name Product Cusip Product Name Product Share Class Trade ID Settled Date Account TA Number Trans Description  Trade Amount 

SCHWABPORTAL US - ASG MILLIMAN MILLIMAN 64128K777 Strategic Income Fund A 29806259 30-Jan-15 000BY00F2RW Redemption  $      25,68,458.15

[Code] .....

View 36 Replies View Related

Give Results Even If Two Tables Used Don't Match

Aug 12, 2014

I have a table of customer information (ci), but I have to link to another table in order to get the street address (sa).

If I exclude the street address I get 33 results. If I make the link to street address I will only get 30 results.

What I found is that if the ci table field for country is blank and I try linking to sa table then that result will not be shown.

Is there a way to get all 33 results while linking the two tables?

View 4 Replies View Related

SQL Server 2008 :: Update Incoming Rows Based On Percentage Calculation

Mar 13, 2015

I have below records coming in from source files

ProdName Amount TranType
P1 100 A
P1 100 S
P2 200 A
P2 205 S

In case the ProdName is same, and Amount = or (within +/- 5%) of Amount, I have to update the TranType column as IN/OUT respectively as shown below in the tables.

I am okay with using 2 different tables if needed as in the records comes in one table and then i can reference that table to upload the values in another.

ProdName Amount TranType
P1 100 IN
P1 100 OUT
P2 200 IN
P2 205 OUT

The order of the records coming in can be different order, they need not be subsequent.

This is happening in SQl Server 2008.

View 3 Replies View Related

SQL Server 2012 :: Calculation Based On Count Function To Format As Decimal Value Or Percentage

Dec 4, 2014

I'm trying to get a calculation based on count(*) to format as a decimal value or percentage.

I keep getting 0s for the solution_rejected_percent column. How can I format this like 0.50 (for 50%)?

select mi.id, count(*) as cnt,
count(*) + 1 as cntplusone,
cast(count(*) / (count(*) + 1) as numeric(10,2)) as solution_rejected_percent
from metric_instance mi
INNER JOIN incident i
on i.number = mi.id
WHERE mi.definition = 'Solution Rejected'
AND i.state = 'Closed'
group by mi.id

id cnt cntplusone solution_rejected_percent
-------------------------------------------------- ----------- ----------- ---------------------------------------
INC011256 1 2 0.00
INC011290 1 2 0.00
INC011291 1 2 0.00
INC011522 1 2 0.00
INC011799 2 3 0.00

View 5 Replies View Related

SQL 2012 :: Query To Match Multiple Results And Average

Mar 3, 2014

I work for a school district and new requirement we were just given for scoring some student scores.

Everything will currently be in one table to keep track of students test scores for various things. This table will contain students information and a student will be shown more than once in the table. The Student ID is what we will key off of to find the multiple instances of the student. The table contains the following columns: studentName, StudentId, teacherName, focus1, controllingIdeas1, reading1, development1, organization1, conventions1, and contentUnderstanding1. All of the columns with a 1 at the end will be numeric values with possible decimal values.

What we need to be able to do is some how perform a search for these multiple entries of each student and when found, average the 2 scores for each 7 test categories. The result needs to be a single line for each student that gives the student name, student id, and the 7 test category averages exported to an csv file.

View 9 Replies View Related

Reporting Services :: Cannot Get Results In Pivot To Match Excel

Jul 1, 2015

is it possible to replicate this in SSRS I wonder??I have included the code of the fields used and a snapshot of some data, and also how the Pivot looks in Excel.

SELECT
TARNSubmissionID,
ISSBand,
BPTLevelAchieved,
FinancialYearOfDischargeOrDeath,
FinancialQuarterOfDischargeOrDeath,
FinancialMonthOfDischargeOrDeath,
CalendarMonthNameOfDischargeOrDeath,

[code]...

View 4 Replies View Related

SQL 2012 :: DQS Term Based Relations Partial Match Not Working

Feb 11, 2015

Why terms are not being corrected on a partial match? I thought this was the point of term based relation rules.

I am testing in an email address (nvarchar) field?

E.g. trying to correct @@ to @ and .con to .com (just to test)

And everything passes.

If I test with an entire value, the field is corrected.

E.g. 605688878@@qq.com corrects to 605688878@qq.com if I enter those exact values as term based relations.

View 0 Replies View Related

Search Query - Analysis On Duplicate Records Based Off Of Several Match Keys

Jun 7, 2014

I'm trying to do some analysis on duplicate records based off of several match keys. I have a data set of approximately 30,000 people and the goal is to determine how many duplicate matches are in the system.

How would I write an SQL statement that looks for the following pieces of information. (I'm not using one person as an example; I need to do an analysis on the entire data set)

First name (exact match)
Last name (exact match)
Address line 1 (exact match)
Postal code/zip (exact match)

First Initial (exact match)
Last name (exact match)
DOB exact match
Postal code/zip (exact match)

View 1 Replies View Related

Display MDX Results In Asp.net 2.0

May 25, 2007

How to connect to a analysis server 2005 cube,execute a mdx query and display result in a aspx page?

Please guide me

View 1 Replies View Related

Getting Results To Display On A Table

Jun 10, 2006

Hi this is the first time i am using sql and i have a problem with displaying the data in the way i want.

i have the following table:

EmpID Cust_Name

1 Mary
1 John
2 David
2 John
Annd i am trying to get it to display like this on the UI

EmpID Cust_Name
1 Mary, John
2 David, Andrew

is there anyway to do this?

View 1 Replies View Related

What's The Best Way To Programatically Modify SQL Results For Display?

Oct 5, 2006

Lets say you have to retrieve some SQL.Then you have to go through those records and delete some items, change some values, and even add columns and rows...based on logic that depends on the data you got.Thanks.

View 2 Replies View Related

SQL Results Table Won't Display In Netscape

Mar 13, 2001

Hi,
I made a table to display information from northwind database. It displays fine on IE5.0 but will only show the first line in netscape.
Do you know what it may be???

View 2 Replies View Related

Display Query Results Horizontally One ID/row

Feb 6, 2003

I have a table where the ID column is a forign key as shown below:

select * from Joel_Table

IDID_Value
1One
2Two
3Three
1Four
2Five
3Six
1Seven
2Eight
3Nine
1Ten

Now, I order the result set based on the ID column:

select ID, ID_Value from Joel_Table
group by ID, ID_Value
order by ID, ID_Value

IDID_Value
1Four
1One
1Seven
1Ten
2Eight
2Five
2Two
3Nine
3Six
3Three

I want though to order the result set as one ID per row:
ID ID_Value ID_Value ID_Value ID_Value
1 Four One SevenTen
2 Eight Five Two
3 Nine Six Three

What is the best way of accomplishing this task?

Thank you much in advance -

View 2 Replies View Related

Change Results In Column To Display Different

Jan 30, 2004

I have a column in the following select startement called displayvalue. Displayvalue is a varchar column. Primary everything entered into this column is numeric, but there are times when NR is entered. In the select query if it sees NR, I would like to have NR changed in the resultset to NULL or blank. NR doesn't come up all of the time, but I have not idea how to do this in the select statment.

Here goes..

SELECT SAMPLE.SAMPLEID, SAMPLE.U_WORKORDERNUMBER, SAMPLE.U_SAMPLEDATETIME, SDI.PARAMID, SDI.DISPLAYVALUE
FROM SAMPLE SAMPLE, SDIDATAITEM SDI
WHERE ( (SAMPLE.SAMPLEID = SDI.KEYID1) AND
(SAMPLE.U_WORKORDERNUMBER = '0060') AND (SAMPLE.U_SAMPLEDATETIME > '31-DEC-2003') AND (SDI.PARAMID = 'BOD') )
ORDER BY SAMPLE.SAMPLEID ASC, SDI.PARAMID ASC

View 1 Replies View Related

No Query Results - Display Message

Feb 26, 2008

Hi,

If the Database query returns no rows how do I display a message in the Report Viewer using expressions??

Thanks.

View 4 Replies View Related

Different Display Based On Text

Jan 30, 2004

I have an HTMP form and for some reason, when I use a regular TextBox to display a field, Via an ASP conneciton to the database, it cuts it off when it gets to a " (A quote)

Example:

Field: Title
Data: Where do "rabbits" come from?

If I try to pull that field into a regular html textbox it dispays,

Where do

Then if I change the textbox to a textarea it displays the entire field

Where do "rabbits" come from?

Is this a setting that causes this in SQL Server on the table.

I am new to SQL Server and am trying to learn these little differences.

Any help would be greatly appreciated.

Thanks,

View 2 Replies View Related

Display The Number Of Results Found For A Subcategory

Oct 13, 2004

Hello,

I am working on a project that displays images. Before you can get to the image, you have to select a category that the image may be in. After that, you select the sub categories. I am trying to display a count of the number of records that the subcategories contain. Here is an example:

The user can make a selection from the categories listed below:

Geographic Area
Time Period
Topic
Record Type


If the user selects time period, he/she is taken to a list of subcategories. I would like to display the subcategories with a count of the number of records that will be displayed if it is selected. Listed below is an example of what this would look like:

Colonial (10) -----the number in parenthesis is the number of records that will be displayed if selected------
Gilded Age (12)
Revolutionary (9)
Progressive Era (22)


Is there a way to display the number in parenthesis using ASP.Net and SQL Server 2000? Any clues will be greatly appreciated.

Thanks

View 6 Replies View Related

Have Query Results Display Vertical Instead Of Horizontal?

Apr 15, 2015

I have a query that calculates sales by sales person, but it displays horizontally across my query window. Is their a way in SQL Server to have the data display vertically down the window instead?

This is my current query

Code:

Select
count(case when salesman Like 'Geo%' then id else null end) As [George]
,count(case when salesman Like 'Li%' then id else null end) As [Lisa]
,count(case when salesman Like 'Jor%' then id else null end) As [Jorge]
,count(case when salesman Like 'Ri%' then id else null end) As [Richard]
,count(case when salesman Like 'Geo%' then id else null end)+count(case when salesman Like 'Li%' then id else null end) As [Team 1 Sales]
,count(case when salesman Like 'Jor%' then id else null end)+count(case when salesman Like 'Ri%' then id else null end) As [Team 2 Sales]
from sales.southeastregion

Which of course shows the results as such

George --- Lisa --- Jorge --- Richard --- Team 1 --- Team 2
100 50 10 90 150 100

And I want the data to be displayed like

George - 100
Lista - 50
Jorge - 10
Richard - 90
Team 1 - 150
Team 2 - 100

This is SQL Server 2008 if that matters.

View 5 Replies View Related

SQL 2012 :: Display Results As Vertical String - Use PIVOT?

Jun 9, 2014

I have a single column returned from a select statement. How can I have this returned as a vertical string? I looked into using PIVOT but my scenario seems too simple to use Pivot. I'm not requiring any aggregate functions or anything.

So, I want to turn this:

SELECT CountyNames + ',' FROM Counties
---------------------
County1 ,
County2 ,
County3 ,
..etc

TO:

County1, County2, County3...etc

Please note that the list of rows is unknown. Could be 1. Could be 50.

View 2 Replies View Related

What Collation To Have Ascii Character 160 Display In Query Results

Feb 11, 2008

I have an issue with some data that has a leading ASCII char 160 (the "a" with the accent mark) but it shows in query results as a space.


... where customername like char(160) + '%'

returns 2 rows but shows the customer name with a leading space. How would I change the collation or do otherwise to get this character to display correctly in the results?

Thanks!

-Dave

View 6 Replies View Related

Display Report Based On User Role

Oct 30, 2007

I have created reports using SSRS 2005 and deployed in ReportServer.

I calling these reports form my web application.Users have different roles based on their login into web application.I need check these user roles and display the reports based on their roles. There could be 10 reports in total, but for this user i should display only 4 out of them. Its pretty urgent. Can somebhelp on this?

View 1 Replies View Related

Based On Conditions Display Only One Particular Value In COLB Against ColA

Nov 12, 2007

Hi all sql experts,
I have a table that has columns 1)ProductName and 2)qualification.
The reporting scenario is i have to display the highest qualification among all qualifications for the particular product name:
Suppose 3 qualifications(B,P,D)
D__lowest
B__Medium
P__highest
So i need P always if P is there among the group.
The different conditions would be
1)If the ProductName has only only type of qualification then display that.
2)If both B and D, then display only B.
3)If both P and D, then display only p.
4) If p,B and D then display only P.

The current table would be like:
ProductName Qualification Col3 Col4
P1 B
P1 P
P1 D
P2 B
P2 D
P3 P
P3 D
P4 D
P5 B
P5 B
P5 B

The expected Result:
ProductName Qualification Col3 Col4
P1 P
P2 B
P3 P
P4 D
P5 B

Any sort of help would be highly appreciated

View 3 Replies View Related

SQL Server 2008 :: Display List Of Indexes - Add Database Name In Results

Apr 6, 2015

I written a proc to display the list of Indexes But I needed to print the database where the objects do belong to. How I should write the Dynamic script to add the database Id? I thought to use derived table kind of stuff, but unable to find a solution.

ALTER PROC [dbo].[USP_INDEXCHECK]
AS
DECLARE @sql NVARCHAR(max)
DECLARE @DB VARCHAR(max)
DECLARE databasecursor CURSOR FOR

[Code] .....

View 2 Replies View Related

SQL Server 2012 :: Calculate Elapsed Time And Display As Row In Results

Jun 19, 2015

I am trying to calculate the time difference between the value in the row and the min value in the table. So say the min value in the table is 2014-05-29 14:44:17.713. (This is the start time of the test.) Now say the test ends at 2014-05-29 17:10:17.010. There are many rows recorded during that start and end time, for each row created a time stamp is created. I am trying to calculate the elapsed time and have it as a row in the results.

min(timestamp) - timestamp(value in row) = elapsed time for that test
where Channel = '273'

Here is the table DDL

CREATE DATABASE SpecTest;
USE SpecTest
GO

CREATE TABLE [dbo].[Spec1](
[Spec1ID] [int] IDENTITY(1,1) NOT NULL,
[Channel] [int] NOT NULL,

[Code] ....

Here is some dummy data to use

INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 14:44:17.713', 800, '-64.91');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)
VALUES(273, '2014-05-29 15:05:09.507', 800, '-59.11');
INSERT INTO Spec1 (Channel, Timestamp, Lambda, Power)

[Code] ....

Example desired results (I hope the formatting works)

Channel | Timestamp | Lambda | Power | Elapsed_Time
______________________________________________________________
273 | '2014-05-29 14:44:17.713', | 800, | '-64.91' | 0
273 | '2014-05-29 15:05:09.507', | 800, | '-64.91' | 00:20:51
273 | '2014-05-29 15:26:00.520', | 800, | '-64.91' | 00:41:42
273 | '2014-05-29 16:28:34.213', | 800, | '-64.91' | 01:44:16
273 | '2014-05-29 16:49:25.853', | 800, | '-64.91' | 02:05:08
273 | '2014-05-29 17:10:17.010', | 800, | '-64.91' | 02:25:59

View 9 Replies View Related

Need Gridview To Display Different Columns Based On New SELECT Statement

Apr 23, 2008

I have built an Advanced Search page which allows users to select which columns to return (via checkbox) and to enter search criteria for any of the selected columns (into textboxes). I build the SQL statement from the properties of the controls. Works great.
My problem is getting my gridview control to play nicely. At first I used a SqlDataReader and bound the gridview to it, thus giving me the ability to run new SQL statements through it (with different columns each time). Worked nicely. But, per Microsoft, sorting can only be done if the gridview is bound to a datasource control like the SqlDataSource. So I wrote the code to handle sorting. No big deal; worked nicely. But I could not adjust the column widths programmatically unless bound to a datasource control like the SqlDataSource. And could not figure out a work around.
So, I decided to use the SqlDataSource. Works great. Except, I cannot figure out how to run a new SELECT statement through the SQLDataSource and have the gridview respond accordingly. If I try to return anything other than the exact same columns defined declaratively in the html, it pukes. But I need to be able to return a new selection of columns each time. For example, first time through the user selects columns 1,2,3,4 – the gridview should show those 4 columns. The second time the user selects columns 2,5,7 – the gridview should those 3 columns (and ONLY those 3 columns). Plus support selection and sorting.
I am desperate on this. I've burned 2.5 days researching and testing.  Does anyone have any suggestions?
Thanks,
Brad

View 9 Replies View Related

Data Display In Reports Based On Windows Authentication ID

Aug 30, 2007

Hi Experts,

I have a reporting scenario, where the reports are fetched from Analysis Services.
The reports should display data only spcecific to that user.
All users except those in admin roles should be validated using the Windows Authentication ID and data specific to them has to be displayed.

Any pointers/suggestions on how to implement this in Reporting services/ Analysis Services 2005 would be highly appreciated

Thanks,

View 1 Replies View Related

How Can Display Items In The Report Based On This Dataset. (urgent)

May 21, 2007

Hi,



I have a web form which has 5 check boxes and i storing the values 1 - 5 for each check box the user clicks . I want to design part of a report in this fashion,

if the user clicks on the first checkBox i want A to appear in the report, and if the user clicks on the second i want B and so forth.



If the user clicks on A& B i want the data to be displayed as A,B. This is my sproc i am using.





Select

laa.PlanId,

laa.LoanId, ( 1-5 values are stored)

los.Name

From

LoansAttriApplied laa

Inner Join LoanOptions los on laa.LoanId = los.LoanId

Where

PlanId = @PlanId



So based on this Query if the user select 4 check boxes for plan No, 104 , I will get 4 rows. So based on the dataset i get

can I display the data in A,B or 1,2,3 instead of

1

2

3



Can someone please give me some insight into this.



Regards

Karen

View 11 Replies View Related

DISTINCT Results Based On The Value Of One Col?

Jun 19, 2008

Here's one thats had me and a coworker puzzled. Hopefully it's something simple:

TABLE A: (log of messages)
userid, int
date, datetime
message, varchar(50)

Table A Data:
1, 6/18/2008 @ 2:32:41, This is my message
1, 6/18/2008 @ 2:31:02, This is my message
1, 6/17/2008 @ 7:34:26, This is another message
2, 6/18/2008 @ 2:32:41, This is not his message
2, 6/16/2008 @ 11:21:32, This is my message


TABLE B: (List of users)
userid, int
name, varchar(100)

Table B Data:
1, John
2, Mike

I want to extract the most recent message logged per user, i.e.:

name | date | message
--------|---------------------|-------------------------
John | 6/18/2008 @ 2:32:41 | This is my message
Mike | 6/18/2008 @ 2:32:41 | This is not his message.

I have been unable to come up with a query that can return just the one value. I've tried variants of:

SELECT DISTINCT b.name, a.date, a.message
FROM a INNER JOIN
b ON a.userid = b.userid

including sub-queries and even played with the visual diagrams trying to design this in Enterprise Manager but none of the combinations I tried work. Is there an easy way to do this via a query? I don't have experience with stored procedures. Would that be necessary?

TIA

View 4 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved