T-SQL (SS2K8) :: Table Compare - Getting False Matches

Mar 26, 2014

I have two tables I am trying to compare as I have created a new procedure to replace an old one and want to check if the new procedure produces similar results.

The problem is that when I run my compare I get false matches. Example:

CREATE TABLE #ABC (Acct VARCHAR(10), Que INT);
INSERT INTO #ABC VALUES
('2310947',110),
('2310947',245);

[Code] ....

Which gives me two records when I really do not want any as the tables are identical.

View 2 Replies


ADVERTISEMENT

T-SQL (SS2K8) :: Compare Rows In The Same Table?

Aug 7, 2014

We have a table setup to track changes that are made to another table, for auditing purposes. How do we compare the most recent record in the change table with the previous record in the change table? Particularly, we have a column named DUE_DATE in the change table and want to identify when the most recent change has a different DUE_DATE than the previous change made.

View 8 Replies View Related

T-SQL (SS2K8) :: Compare Next Row With Previous Row Of Same Table?

Sep 16, 2014

I need to compare the next row with the previous row of same table and produce nonidentical column.for eg... say my table has

Row 1 => 1001 Abhas 120 150 180
Row 2 => 1001 Abhas 150 150 180

then my output would be as below:

StudId Name fee1 fee2 fee3
1001 120
1001 Abhas 150 150 150

i.e in first row of resultset, i want to show only those values which are changed alongwith studID and next row should display all values.

View 9 Replies View Related

T-SQL (SS2K8) :: Take ID Value From Maximum ID And Compare Rest ID Value From Table

Jul 2, 2014

--drop table #temp
create table #temp (id int, idvalue int)
insert into #temp(id,idvalue)
select 1095,75

[code]...

I need to take the id value from maximum's id, and compare the rest id value from the table. i need to check the diffrence , if diffrence is more than 18, then i need to raise the flag as failure otherwise the whole test is success. i need to take 63 and compare rest 69,65,61,75.check the diffrence less than 18 or not.

View 3 Replies View Related

T-SQL (SS2K8) :: Compare Data In A Single Table By Month Period?

May 28, 2014

i would like to see the 2014-06 matched results (3rd query), if the same ssn and acctno is exist in 2012-06 and 2013-06 and 2014-06 then eliminate from results, otherwise show it

select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06'
select ssn, acctno From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06'

i have written the below query but it shows only matched across three queries, but i want to display / delete from 2014-06 records if the ssn and acctno is exist in 2012-06 and 2013-06

select c.* from (
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2012-06' ) a join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2013-06' ) b on a.SSN = b.SSN and a.acctno = b.acctno join
(select * From jnj.drgSamples where Channel ='KM' and TrailMonth ='2014-06' ) C on a.SSN = c.SSN and a.acctno = c.acctno join
)

View 4 Replies View Related

T-SQL (SS2K8) :: If Not Exists Returning False When Should Be True

Jul 3, 2014

Actually title should be returns true when should false.

I want to check a table to see if a record already exists, if it doesn't then insert it, else do nothing:

IF NOT EXISTS
(SELECT 1 FROM Table1 WHERE col1 = 'Test')
BEGIN
INSERT INTO Table1 (col1) VALUES ('Test')
END

The value 'Test' is already in the database yet, the code is saying it's not and trying to insert it, resulting in duplicate key errors.

View 9 Replies View Related

Minimizing Penalty (weighted Sum Of False Positives Plus False Negatives)

May 25, 2006

I am using Naive Bayes, Decision Trees, and Neural Net (SSAS 2005) to predict which of two states each record belongs to.

How can I enforce a different penalty for a false positive versus a false negative ?  (I am assuming that in some sense the mining algorithms can then minimize the total penalty).

View 5 Replies View Related

When Have 4 Tables How To Compare Table With 4nd Table? ( Need To Join And Compare With Datatime)

Jul 22, 2007

Table MediaImportLog
column ↘ImportIndex     ImportFileTime            ImportSource
value    ↘80507             20060506001100          815
              80511             20061109120011           CRD                       ? P.S the values type of ImportFileTime 20060506001100 → 2006 -year 05-month 06-day 00-HH 11-minute 00-second】
Table  BillerChain
column↘BillerInfoCode       ChainCode
value   ↘750                      815
value   ↘81162                  CRD
Table   Biller
column↘CompanyCode         BillerCode
value   ↘999                     750
value   ↘81162                  516
TAble DataBackup
column↘CompanyCode         Keepmonth
value   ↘999                     6
value   ↘81162                 12
 
---------------------------------------------------
 
when I'm in MediaImportLog , I want use column ImportSource to compare with column ChainCode in table BillerChain ( so I get BillerInfoCode) and then use the BillerInfoCode I got to compare with column BillerCode in Table Bill ( I get CompanyCode) finally I use CompanyCode to compare with column CompanyCode in table DataBackup so I can get the company's keepmonth
How can I get the keepmonth? can I use parameters ? 
 
thank you very much 

View 3 Replies View Related

How Do I Query A Table For Matches With Keywords From Another Table?

Aug 24, 2007

This is what I wan to do: If I, for example, have a table containing articles, and I want to allow users to return any article that matches a filter, which is defined by any number of keywords, located in another table.


My tables:
Article table: [ArticleID, Headline, Content]
Filter table: [FilterID, Name]
Keyword table: [KeywordID, Word]
FiltersKeywords table: [FilterID, KeywordID]

(The FiltersKeywords table serves as a many-to-many relation table between the Keyword table and the Filter table)


In other words, a filter, which is used to selected articles with, can be made up of any number of keywords. The users would thus be able to build on the filters, adding new keywords as time goes, removing old none relevant etc. I suspect some filters could end having anywhere from 100 to 1000 keywords associated with them.


Using SQL 2005, is there anyway to query for articles in an efficient manner? How would the code look?

Thanks, Egil.

View 3 Replies View Related

Update Table Where It Matches Criteria From Another Table

Aug 20, 2007

I am trying to clean up an old database someone developed. I took the data and grouped the information that matched
I then imported it into a trips table. The matching data is all in the where statement. It shrunk 16k records for just under 2k records. Now I am trying to right a query to relate the 2 tables and update the tripid# to the triplist of people who actually attended.


Declare @Trip varchar(500)

Declare @Destination Varchar(500)

Declare @LeaveDate varchar(500)

Declare @LeaveTime Varchar(500)

Declare @ReturnDate varchar(500)

Declare @ReturnTime varchar(500)

Declare @Comment varchar(500)

Declare @RescheduleDate Varchar(500)

Declare @Rec int


Select @Rec = Rec# From Trips

Select @Trip = Trip from [Trips] where Trips.rec# = @Rec

Select @Destination = Destination from [Trips] where Trips.rec# = @Rec

Select @LeaveDate = LeaveDate from [Trips] where Trips.rec# = @Rec

Select @LeaveTime = LeaveTime from [Trips] where Trips.rec# = @Rec

Select @ReturnDate = ReturnDate from [Trips] where Trips.rec# = @Rec

Select @ReturnTime = ReturnTime from [Trips] where Trips.rec# = @Rec

Select @Comment = Comment from [Trips] where Trips.rec# = @Rec

Select @RescheduleDate = RescheduleDate from [Trips] where Trips.rec# = @Rec


Update TripList

SET tripid# = @Rec

WHERE (Trip = @Trip) and (Destination = @Destination) and (LeaveDate = @LeaveDate) and (LeaveTime = @LeaveTime) and (ReturnDate = @ReturnDate) and (ReturnTime = @ReturnTime) and (Comment = @Comment) and (RescheduleDate = @RescheduleDate)

View 7 Replies View Related

Order By Common Lookup Table Matches

Mar 11, 2013

Basically I have a table of users (id, name), a lookup table preferences (id, title) and the table of user-pref (idUser, idPref).Given a user id X I wanted to select a list of users, ordered by the highest amount of coincidence in the preference table...

something like

SELECT * FROM users WHERE id <> X ORDER BY -number of common preferences-

View 7 Replies View Related

T-SQL (SS2K8) :: Compare Records In Tables?

Mar 3, 2014

There are four tables

1. Matter

MID, CID, RType

001, a, m
002, a, m
003, b, m
004, c, m

2. Category

CID. RType
a, T
b, T
c, T

3. Security assignmnet

RID, RType, GID
001, m, g01
002, m, g01
002, m, g02
002, m, g03
003, m, g01
003, m, g03
a, T, g01
a, T, g02
a, T, g03
b, T, g02
b, T, g03
b, T, g04

4. Group

GID
g01
g02
g03
g04

I'd like to find the record in table #1 "Matter" which has exact record of "GID" in table #3 "Security Assignment" compare with table #2 "Category"

In this case, it is record of "002" bacause "002" in table#1 "Matter" and the record "a" in table #2 "category" both has exact GID records(g01, g02, g03) in table #3, "Security Assignment"

How can I create qury to find all the possible record in the table #2?

View 9 Replies View Related

T-SQL (SS2K8) :: Compare Data Between 2 Rows?

Jun 27, 2014

I have the following recordset:

cmdBatchNbPdsLbsZONE
817159644 1.55320031
817159652 9.09590031
817159679 2.5891806
817159687 5.7123006
817159709 2.3903006
817159733 2.2792006
817159741 2.0647007
817159768 1.2430007
817159784 4.1547006
817159792 3.56576013

I need to extract the corresponding price from the following table:

Zone MaxWeight Price
---------------------- ---------------------------------------
31 1.70 7.14
31 2.20 8.76
31 3.30 9.47
31 4.40 9.69
31 5.50 10.61
31 6.60 11.05
31 7.70 11.49
31 8.80 11.93
31 9.90 12.37
31 11.00 12.81
31 12.10 13.23

In this case, the 2 first rows should give a price of

1) 7.14 (weight between 0 - 1.70)

2) 11.93 (weight between 8.80 - 9.90)

How can I do that with a query?

View 4 Replies View Related

T-SQL (SS2K8) :: Compare And Insert Record

Oct 15, 2014

I am having below two tables:

1) TableA : Which contains 5 columns(Column1,..........Column5)
2)TableB : Which contains 10 columns(Column1,..........Column10)

TableB contains millions of data.Now I want select all 5 columns from tableA but combination of Column1,Column2,Column3 if present in tableB, then i want exclude that records.I am doing as below:

select * from TableA a join TableB b a.column1!=b.column1 and a.column2!=b.column2 and a.column3!=b.column3 )

But query is taking almost 5 minutes. Is there is another approach?

View 4 Replies View Related

T-SQL (SS2K8) :: Compare With Previous Records

Oct 20, 2014

I am having a table which contains data of students like:

StudentID,StudentName,Term,RESult.

Sample data :

StudentID,StudentName,Term,RESult.
1,ABC,Term1,Pass
1,ABC,Term2,Fail
1,ABC,Term3,Pass
1,ABC,Term4,Pass
1,ABC,Term5,Pass

Now i want to compare Result and dislay prevterm where student fail:

Now my output would be as: Now I want to compare latest term i.e. Term5 with prev Terms and if found Mismatch in result then i want to display as below:

studentID PrevFailTerm, CurrentTerm
1,Term2,Term5

View 1 Replies View Related

T-SQL (SS2K8) :: How To Compare Two Rows And Two Different Columns

Oct 22, 2014

I am fairly new to SQL and writing queries so bear with my faults. I am learning on the job, which is good and bad. Below is a query that I have written to obtain some information. The problem arises when we have a patient who goes from Patient Type '1' to Patient Type '2'. This needs to be considered a singular visit and the only way I can think that this may work is if: for any specific medical record a dsch_ts is equal to the Admit TS on the next row.

How to complete something like this and my google searches have been fruitless. I attached a spreadsheet with an example of what I am getting.

SELECT DISTINCT
TPM300_PAT_VISIT.med_rec_no,
TSM040_PERSON_HDR.lst_nm AS 'Last Name',
TSM040_PERSON_HDR.fst_nm AS 'First Name',

[Code] ....

View 6 Replies View Related

T-SQL (SS2K8) :: Compare Two Varchar For Similarities?

Oct 28, 2014

I am asked to compare the address fields (three columns of nvarchar(100) ) of a customer database (around 10,000 records) and find any duplicates. If it is a character by character match, I could have just GROUPed and get the result.

But, I am expected to produce a list with similar addresses which the guys who entered may have use slightly different spelling or more or less characters, or a "." here and there.

is there any way to create a query for this?

View 5 Replies View Related

T-SQL (SS2K8) :: Compare Tables With More Than 4.9 Million Records?

Mar 18, 2014

I want to compare ONLY 1 Column values from 2 tables having more than 4.9 million records. There is a difference of 4000 rows between the 2 tables.

SELECT ID From TABLE1 where ID not in (SELECT DISTINCT ID From TABLE2)

My above query took nearly 4.5 hours to run and I had to cancel it. Is there a better way to write the query . I just want to compare the ID - column values which are missing in TABLE2

View 7 Replies View Related

T-SQL (SS2K8) :: Compare Multiple Column Between Two Tables

Aug 14, 2014

I want to display records from @table1 only when combination of col2,col3 and col4 are present in @table2.In Below case I want output as: below two records only.

'test1', 'need this record', 25, {d '1901-01-01'}
'test3', 'some longer value', 23, {d '1900-01-01'}
declare @table1 table (
col1 varchar(10) not null,
col2 varchar(200) null,
col3 int not null,

[code]....

View 2 Replies View Related

T-SQL (SS2K8) :: Query To Compare Data In 2 Tables

Sep 17, 2015

Table 1 has "Gender" field with "Male" and "Female" in it, table 2 has "Gender" field with "M" and "F" in it. a query to compare data and list the differences.

View 4 Replies View Related

T-SQL (SS2K8) :: How To Compare Data (join) Based On Two Varchar Columns

Mar 15, 2014

-- My first Data

create table #myfirst (id int, city varchar(20))
insert into #myfirst values (500,'Newyork')
insert into #myfirst values (100,'Ediosn')
insert into #myfirst values (200,'Atlanta')
insert into #myfirst values (300,'Greenwoods')
insert into #myfirst values (400,'Hitchcok')
insert into #myfirst values (700,'Walmart')
insert into #myfirst values (800,'Madida')

-- My Second Data

create table #mySecond (id int, city varchar(20),Sector varchar(2))
insert into #mySecond values (1500,'Newyork','MK')
insert into #mySecond values (5500,'Ediosn','HH')
insert into #mySecond values (5060,'The Atlanta','JK')
insert into #mySecond values (7500,'The Greenwoods','DF')
insert into #mySecond values (9500,'Metro','KK')
insert into #mySecond values (3300,'Kilapr','MK')
insert into #mySecond values (9500,'Metro','NH')

--Third Second Data

create table #myThird (id int, city varchar(20),Sector varchar(2))
insert into #myThird values (33,'Walmart','PP')
insert into #myThird values (20,'Ediosn','DD')
select f.*,s.Sector from #myfirst f join #mySecond s on f.city = s.city
/*
idcitySector
500NewyorkMK
100EdiosnHH
*/

i have doubt on two things

1) How Can i compare the City names, by eliminating 'The ' at the beginning (if there is any in second tale city) between first and second

2) after comparing first and second if there is no match found in second them want to compare with third table values for those not found

--i tried below to solve first doubt, it is working but want to know any other wasys to do it

select f.*,s.Sector from #myfirst f join #mySecond s on replace (f.city, 'THE ','')= replace (s.city, 'THE ','')

--Expected results wull be

create table #ExpectResults (id int, city varchar(20),Sector varchar(2))
insert into #ExpectResults values (200,'Atlanta','JK')
insert into #ExpectResults values (100,'Ediosn','HH')
insert into #ExpectResults values (300,'Greenwoods','DF')
insert into #ExpectResults values (500,'Newyork','MK')
insert into #ExpectResults values (700, 'Walmart','PP')
insert into #ExpectResults values (800, 'Madidar','')

[code]....

View 1 Replies View Related

T-SQL (SS2K8) :: Finding Last Cost By Article And Compare With Invoice Line Cost?

May 28, 2015

I need to build TSQL query to return the Last unit Cost from my table of movement of goods SL (on CTE) but the MAX(Datalc) must be Less or Equal to my HeaderInvoice.

This is my script:

With MaxDates as (
SELECT ref,
MAX(epcpond)[Unitcostprice],
MAX(datalc) MaxDate
FROM sl

[code]....

the problem I have right now is that the Unitcostprice of my table of goods movements has a top date greather than the date of my bill.

Example:

invoice date : 29.01.2015 unitcost on invoice line = 13,599722
Maxdate (CTE) : 19.03.2015 unitCost from my table of movement of goods = 14,075

That ´s not correct because the MAxdates > invoice date and the unitCost of 14,075 is the cost on 19.03.2015 and not just before my invoice date.

View 4 Replies View Related

SQL Server 2012 :: Compare Two Table Data And Insert Changed Field To Third Table

Aug 12, 2014

I want Compare two Table data and insert changed field to the third table ...

View 9 Replies View Related

Compare A SQL Server Table To An Oracle Table

Jul 26, 2007

How do I compare a SQL Server table to an Oracle table? Looking to compare table structure and data. Is there a tool that I can use to perform this?

View 1 Replies View Related

Several Matches In If

Apr 17, 2007

i dont know why this has slipped my mind but how would i say,

if @test = 'a' or @test = 'b' or @test = 'c'

in a shortted way?

View 1 Replies View Related

Compare Data And Add To Table

Mar 4, 2004

Hi all,

Newbie here. I was wondering if any of you gurus could answer a question for me. Here is what I need to do (and I stress need):

I have 2 tables.

Table A has 3 columns, column 1 is unique customer numbers, column 2 is ticket numbers, column 3 is empty records.

Table B has 2 columns, column 1 is unique customer numbers (same numbers, although not the same order as Table A) , column 2 is invoice numbers.

I need to compare Table A where records in column 1 match records in column 1 in Table B. Where the records do match, I need to copy the records from Table B, column 2 to Table A column 3.

Can anyone here help me with this, please? It would really get me out of a jam with this, since it is the last step I have to take to finally get this new app rolled out.

Thanks a lot.

Mark

View 3 Replies View Related

Compare Data In Two Table

Jun 13, 2006

Hi all,
i have a question regarding data comparison in two tables in same database with same table structure.

my table structure is like this

CREATE TABLE xxgfs_gen_text_lookups_new (
lookup_type VARCHAR2(200) NOT NULL,
region_code VARCHAR2(30),
nongfs_value1 VARCHAR2(200),
nongfs_value2 VARCHAR2(50),
nongfs_value3 VARCHAR2(50),
gfs_value1 VARCHAR2(200),
gfs_value2 VARCHAR2(50),
gfs_value3 VARCHAR2(50),
retain_nongfs_for_dflts VARCHAR2(1),
retain_gfs_for_dflts VARCHAR2(1),
comments VARCHAR2(500),
created_by NUMBER(15),
creation_date DATE,
last_updated_by NUMBER(15),
last_update_date DATE,
last_update_login NUMBER(15),
Source_description varchar2(300),
Oracle_description varchar2(300),
defaults varchar2(300)
)



ALTER TABLE xxgfs_gen_text_lookups ADD CONSTRAINT xxgfs_gen_text_lookups_uq_1
UNIQUE (lookup_type,region_code,nongfs_value1,nongfs_value2,nongfs_value3);


i have some data in excel which i have uploaded using sql*loader using control card.Now i want to compare the data in both tables having same table structure only


do any body having idea how to compare the data using storeprocedure.
thanks in advance


regs,
Rajnish kumar

View 2 Replies View Related

SQL Compare Column In Table

Aug 15, 2006

I have table with the following structure :
ID Result1 Reusult2 NewResult1 NewResult2 FinalResult
-1----10------6-----------7------------9----
-2----32------16----------32-----------16---
-3----12------2-----------1------------9----
-4----50------6-----------9------------7----
-5----90------9-----------90-----------9----
-6----18------2-----------3------------2----


My problem :

I want to compare each record in column Result1 and NewResult1,Result2 and NewResult2
Such as my above table :
With ID=2 and ID=5 : I need to update column "Final Result" with value equal 1 ,otherwise any records with value equal zero.
Conclusion :
If ((Result1=NewResult1) and (Result2=NewResult2)) --> Final Result =1
else
Other Records -->Final Result=0
How to write sql statement ?
Thank you very much !

View 5 Replies View Related

To Compare Fields Between Two Table

Jul 23, 2005

Hello,I would like to create a stored procedure that would compare the fieldsof two tables and their types. If they are different the user iswarned.How can I do that ?thx

View 3 Replies View Related

How To Compare Two Table With Alerts?

Apr 1, 2008

hi,

I have two tables with data.

I want to compare first table of price column and second table of AVG(PRICE) column.

i.e price > AVG(PRICE).

And dispaly the alert message if the condition the true.

how to fid out if any one help this...

View 6 Replies View Related

How To Use WHERE To Find Various Matches

Apr 26, 2008

Hello.I have a select that returns some SubCategories.SELECT SubCategoryID from SubCategories SCATINNER JOIN Categories CAT on CAT.CategoryId = SCAT.ParenCategoryIdWHERE SCAT.ParentCategoryId = X Now i will to retrieve all rows from a Table called Items Where Items.SubCategoryId will be any of the previous SubCategoryId's returned by the above SELECT query.What code i need to write toI think .. Select * from Items AS IT WHERE IT.Subcategory = ???I don't know, anyone can help meThanks

View 4 Replies View Related

Repeating Matches

May 18, 2007

Hi. I am new to SQL.I hope you veteran out there to help me solve the simple problem i met.

CREATE TABLE BASKET(
B# NUMBER(6) NOT NULL,
ITEM VARCHAR(6) NOT NULL,
CONSTRAINT BASKET_PKEY PRIMARY KEY(B#, ITEM) );

My statement

SELECT DISTINCT L1.ITEM, L2.ITEM,COUNT(L1.B#)
FROM BASKET L1 ,BASKET L2
WHERE L1.B# = L2.B#
AND L1.ITEM <> L2.ITEM
GROUP BY L1.ITEM,L2.ITEM;

the result is

ITEM ITEM COUNT(L1.B#)
------ ------ ------------
BEER MILK 5
BEER BREAD 4
BEER BUTTER 2
MILK BEER 5
MILK BREAD 6
MILK BUTTER 5
BREAD BEER 4
BREAD MILK 6
BREAD BUTTER 5
BUTTER BEER 2
BUTTER MILK 5
BUTTER BREAD 5

The problem is how to get rid those repeating group like (BEER,MILK) and (MILK,BREAD)?

View 3 Replies View Related

IN Operation That Matches ALL Instead Of ANY?

Mar 25, 2008

A standard IN operation is like doing a series of OR statements in your WHERE clause. Is there anything like an IN statement that is like using a series of AND statements instead?

I tried looking into the ALL operator but that didn't seem to do it or else I just couldn't figure out how to use it correctly.

View 7 Replies View Related







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