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


ADVERTISEMENT

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 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) :: 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

Compare Records Of Two Tables?

Jul 31, 2007

Hi

I am trying for compare two tables records and if which records are not match insert in to both table and at end both table records will be same.

using stored procedure & I need also pass server name database name.

thanks

View 4 Replies View Related

T-SQL (SS2K8) :: Get Delta Records Between 2 Tables With Same Structure

Dec 30, 2011

I need to get the replacement records between the 2 tables. I have table A and table B with same structure. I have 5 fields. Table A has 50,000 records and table B has 20,000 records. I have fields id , name, address,meter_flag,end_Date.

Some of the records in Table B are just replacement records of table A. I mean for example I have records like this in Table A

id name address meter_flag end_date

23 john 1201 salt lake dr no 2011-12-28

24 tom 1222 gibson ln yes 2011-12-16

25 alex 1334 qayak dr no 2011-12-17

In Table B

23 john 1344 mc kinney st yes 2011-12-18

24 tom 1222 gibson ln yes 2011-12-16

56 gary 1335 pruitt rd no 2011-12-18

25 alex 1334 qayak dr no 2011-12-17

So here in Table B i have an update for john with id 23 in table A in address field and meter_flag has changed to yes. There is new record with id 25 in table b but that is not in table A. so I need to find all these difference records by querying these 2 table

View 9 Replies View Related

T-SQL (SS2K8) :: Finding Last Records Inserted Into All Tables In A Database

Jul 27, 2015

I have a CRM database that has a lot of tables and would like to be able to extract the last 'x' records in descending order from each table based on a common a field 'modifiedon' that is in every table and is auto populated by the system.

View 4 Replies View Related

T-SQL (SS2K8) :: Delete And Merge Duplicate Records From Joined Tables?

Oct 21, 2014

Im trying to delete duplicate records from the output of the query below, if they also meet certain conditions ie 'different address type' then I would merge the records. From the following query how do I go about achieving one and/or the other from either the output, or as an extension of the query itself?

SELECT
a1z103acno AccountNumber
, a1z103frnm FirstName
, a1z103lanm LastName
, a1z103ornm OrgName
, a3z103adr1 AddressLine1
, A3z103city City
, A3z103st State

[code]...

View 1 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 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) :: 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) :: 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) :: Renumbering Remaining Records In A Table After Some Records Deleted

Dec 3, 2014

I have a table with about half a million records, each representing a patient in my county.

Each record has a field (RRank) which basically sorts the patients as to how "unwell" they are according to a previously-applied algorithm. The most unwell patient has an RRank of 1, the next-most unwell has RRank=2 etc.

I have just deleted several hundred records (which relate to patients now deceased) from the table, thereby leaving gaps in the RRank sequence. I want to renumber the remaining recs to get rid of the gaps.

I can see what I want to accomplish by using ROW_NUMBER, thus:

SELECT ROW_NUMBER() Over (ORDER BY RRank) as RecNumber, RRank
FROM RPL
ORDER BY RRank

I see the numbers in the RecNumber column falling behind the RRank as I scan down the results

My question is: How to convert this into an UPDATE statement? I had hoped that I could do something like:

UPDATE RISC_PatientList_TEMP
SET RRank = ROW_NUMBER() Over (ORDER BY RRank);

but the system informs that window functions will only work on SELECT (which UPDATE isn't) or ORDER BY (which I can't legally add).

View 5 Replies View Related

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 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

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

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) :: 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

SP To Compare Records - Generate Output In Attachment

Jul 8, 2014

I have a table that have different groups in it, All the records that belong to one Group have the same GroupID and each GroupID has same columns. I need a query to generate the output in the attachment.

When the user input one GroupID then all the records belong to that group will pull out,

When the user input two GroupIDs then the user need to select another set of parameter (duplicate records or different records or nonoverlap records) to display

Intersect of the two group or different records or except records

when user input three GroupIDs then the user need to display the overlaps among all three groups or different among three groups or dinstinct records among the three groups

Of course user can input other filters.

View 2 Replies View Related

Compare Two Tables

Dec 8, 2000

I need to compare two identical tables and return the rows that are different,they are not having any primary key. Anyone to help with the query?
Thanks.

View 1 Replies View Related

Can I Compare Two Tables

Jan 14, 2000

Is it possible (easily) to compare two identical tables (one from server1 'SQL7', one a download from an Oracle DB into SQL7) to see if the Oracle table has changed, what is the changed row and place said row into another table to do some other process on those changed rows?

Thanks,
Bill

View 2 Replies View Related

How To Compare 2 Tables ??

Jul 1, 2004

Hi,

Need help with the structuring of the following query:
I've got 2 tables nl :tableA, tableB
I need to do a query to determine wat DISTINCT values of a column(cIdNo) in tableA is not present in column (bIdNo) in tableB.

tableA contains Id numbers of people who still has to pay their bills, and tableB contains Id numbers of people who already paid. So I want a query to determine who still needs to pay.

I've tried the following but doesn't work, not to sure if it's the right wayto do it:


PHP Code:




 SELECT  distinct(cIdno) as IdNumber 
FROM tableA 
LEFT JOIN tableB
ON tableA.cIdNo != tableB.bIdNo 






PLEASE HELP !! Thanks in advance !!!

View 3 Replies View Related

How To Compare Each Row In Two Tables

Jul 10, 2012

I have two the same structure tables. One is the source table (table1), another one is the destination table (table2).

Now I want to compare these two table row by row.

For example,

If table1 row1 is the same as table2 row1, go to next
If table1 row2 is not the same as table2 row2, update table2 row2 to make it the same as table1 row2.
If table1 row3 is not existed in table2 row3, insert table1 row3 into table2 blank new row.

How to create this script?

View 3 Replies View Related

Compare 2 Tables

Jun 8, 2006

I have 2 tables that are exactally the same.

Table 1 - Is named LIVE
Table 2 - Is name EOM (End of Month)

The 2 tables have these fields
MEMBER
STREET

I want to build a query that will return any member who has changed their address (ex: member's eom address is "123 main st", and now their live address is "345 apple st")

But I do not want to display any addresses that are different because that member wasn't a member last month (not in the eom table)

Note: Member Numbers Never Change, only addresses will.

Thanks in advance for any help/suggestions.

View 2 Replies View Related

How To Compare 2 Tables

Dec 17, 2007

need to know how to check both table data and return only data that doesn't match the other table data


select distinct dept_name
from bb_guide_party_dept d, binbox_dept e where d.dept_id = e.dept_id
and e.active_Flag = 1

union
select distinct dept_name
from bb_guide_party_dept a, binbox_dept b where a.dept_id = b.dept_id
and e.dept_name not the same as b.dept_name

View 3 Replies View Related

How To Compare Different Tables

Jan 31, 2008

hi, I'm pretty new to ssis so this may be an absolute beginner question but i couldn't find a solution yet. the task is to compare data from tow tables which are of the same structure. table1 contains data before some operations and table2 contains the results. now i have to find out which datasets have been changed, which one are new and which one are gone.

i've done some googlin and found this TableDifference http://www.sqlbi.eu/Projects/TableDifference/tabid/74/Default.aspx component, but to me it seems to be kind of buggy, so I had the idea of trying to set an sql statement on both ole db sources where i tried to select only the datasets that are not contained in the other table. so i tried something like this:

select * from lib.table_1 except select * from lib.table_2

but this doesn't work either. so i would be really thankful if someone could help me.

View 1 Replies View Related

How To Compare Data In Tables

Sep 13, 2001

I know this sounds simple, but I haven't seen it in bol. I need to compare two tables, and list what rows are unique to each table. Thanks for the help!

rb

View 2 Replies View Related

Compare Two Tables...URGENT!

Dec 7, 2000

Can anyone give me the query that I can use to to compare the data between two similar tables.Data from col1,col2,col3,col4 needs to be compared. Col1 is primary key in both the tables.It should return with zero rows if they are same.
Thanks for any help.

View 1 Replies View Related

Compare/filter 2 Tables...help!!!

May 13, 2004

Would appreciate if someone could help!

I have 2 tables , table 1 and table 2. Exact copies of some records from table 1 also exist in table 2. What i need to do is display records that exist in table 1 but do not exist in table 2.

Thanks for your help!

View 3 Replies View Related

Query To Compare Two Tables

Aug 4, 1999

Hi,

I am trying to write a query to compare the same column in each table with "not equal" expression.

My query is like this:

select tableOne.empl_ser_no from tableOne, tableTwo
where tableOnel.empl_ser_no <>(select empl_ser_no from tableTwo)

I am getting the following message from SQL Server:

Msg 512, Level 16, State 1
Subquery returned more than 1 value. This is illegal when the subquery follows =, !=, <, <= , >, >=, or when the subquery is used as an expression.
Command has been aborted.

View 1 Replies View Related







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