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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
Aug 12, 2014
I want Compare two Table data and insert changed field to the third table ...
View 9 Replies
View Related
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
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
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
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
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
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
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
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
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
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
