Best Way To Compare Two ENTIRE Rows In Seperate Tables?
Apr 12, 2006
Hi folks, I've got a fairly easy one here me thinx. I'm looking for the best way to compare two entire rows from two seperate tables which have the same primary key.
Here's the basic lowdown:
I get some data every night from an external system (cache') via DTS. This is more or less my "master" data which drives my application. I have just been informed of an interesting constraint. If any of the data changes in the external system, those changes do not become effective until the first day of the ensuing fiscal quarter.
I'm solving this by running the DTS as normal, but populating a "duplicate" table which I will evaluate once per quarter for any changes. This is also the preferred solution because they would like to see a snapshot between current cache' data and the data my application is currently working with.
So, I end up with two identically structured tables. both tables have the same primary key and can be linked by an id field with relative ease. What I would like to do is a full row comparision once this join is established.
Right now I explicitly check the value of each column. ie:
WHERE t1.field1 <> t2.field1 OR t1.field2 <> t2.field2 OR t1.field3 <> t2.field3 ... etc
I'm hoping there is something buried in TSQL that I just don't know about that can handle comparing entire rows and tell me if they're different. Or perhaps there's another approach all-together.
Any thoughts?
View 5 Replies
ADVERTISEMENT
Nov 16, 2011
I need to compare two tables in each row. the tables are as follows:-
Table a:
Code:
IDFirst_Name Last_name Birthdate
1Shradha Deshkmukh 1981-12-25 00:00:00
2Shradha Verma 1981-05-11 00:00:00
3Divya Dutta 1982-07-21 00:00:00
4Matthew Palmer 1983-12-28 00:00:00
table d:-
Code:
idfnlndob
1ShradhaTiwari1981-12-25 00:00:00
2DivyaDutta1983-07-21 00:00:00
3SulabhManesar1975-09-11 00:00:00
4MatthewPalmer1983-12-28 00:00:00
5SamuelMaxwell1984-05-22 00:00:00
I want to compare the tables using first name, and I have a log variable which I want to have the value as per the differences in the table that is if the first name matches and second name and dob dont match it shows log value for that FN as 'LN and DOB dont match'.
similarly if First name matches and dob matches then @log is 'LN not match'.
And in case all three match it should show 'match'as log value.The query I use is a s follows:-
Code:
USE testing
GO
DECLARE @NR int
DECLARE @log varchar(200)
SELECT @NR = COUNT(*) FROM a
WHILE @NR>0
[code]...
the result I am getting is :-
Code:
fnlndob (No column name)
ShradhaTiwari1981-12-25 00:00:00match
ShradhaTiwari1981-12-25 00:00:00match
DivyaDutta1983-07-21 00:00:00match
MatthewPalmer1983-12-28 00:00:00match
I have tried using CASE but that doesnt work either.
View 3 Replies
View Related
Jan 9, 2008
Rows in table A that have a business status of 'open' are written to table B (on a different db) on a daily basis.
I have managed to extract the relevant rows in table A and table B.
What I now need to do is compare table A, row N with table B row N and return them both if the values differ.
What is the best way of doing this
e.g.
Table A
customerId amount
1 200
2 106 *return this row*
3 412
Table B (filtered by MAX(audit date))
customerID amount
1 200
2 100 *return this row*
3 412
?
View 6 Replies
View Related
Dec 18, 2014
I run the script below once a day to keep track of row count over time. I would like to compare the results from today and yesterday to see if anyone deleted more than 20% of data from any given table. How would I do this? I really don't need the data anymore than a day just to compare the results.
Mon - Run script to collect row count
Tues - Run script to collect current row into temp table
,compare all row count in both tables
,purge records from Monday and insert current
Wed - Run script to collect current row into temp table
,compare all row count in both tables
[code]....
View 4 Replies
View Related
May 22, 2008
My boss has asked me to look into this and I haven't been able to find any information on the web. I hope someone can answer this for me. We currently have a single database that is storing all the user information and transactions. Within the same database we are also logging different types of user activity. If both these tables are heavily used, would it make sense to separate it into different database, one for data and one for logging? Is there any pro or cons of having more than one database? Any opinion or suggestion would be greatly appreciated. I'm the closest thing they have to DBA and I'm really new to this. Thanks.
View 5 Replies
View Related
May 10, 2008
I have 4 tables with the respective amount of records
1) 6755
2) 2021
3) 2021
4) 355
They all have the same columns. However, they need to be seperate, or at least when I query them. I'll be accessing this database via the web. i was first afraid that a large database would cause major slow down when accessing the db. So I broke it up into 4 tables. If I combined all 4 tables into one large table and just had a column that differentiated the 4, how significant would be the change in speed when accessing the table? It's not a big deal to keep them seperate, its just that when I have to add or remove a column from one table I have to remove it from all the tables. Furthermore, I'm using a module from DEVEXPRESS, don't know if anyone has heard of it, but when you use a gridview, it loads up the entire table even though your paging (which I think is retarded), so for that reason I was afraid it would slow up my access to the db. Any thoughts?
View 2 Replies
View Related
Jul 20, 2005
Does anyone have any idea how to syncronise two SQL identical tables acrossa network, between servers?We want to run our website from a SQL tables on the same server, but set upso that if the main SQL tables across the network change, or the tables onthe web server change, the two seperate servers willsyncronise/update/trigger updates to the tables on each other?We are running SQL server 2000.AHAJohn
View 2 Replies
View Related
Mar 20, 2008
Hello,
I have two databases in sql server. I'll call them DB1 and DB2. I have a table in DB2 that needs to form a relationship with a table in DB1. When I attempt to add a relationship I only see tables in DB2. Can this be done?
Thanks,
Mark
View 10 Replies
View Related
Apr 13, 2007
Hello,
I don't know if this could be done, but I will present this question...
I have an employee_table with empid, firstName, lastName, phExt columns.
I have another table called location_table that contains locationID, locationName, locPhext.
Here is the dataset from the 2 tables...
From Employee Table....
empid firstName lastName phExt
1 Ann Smith 1234
2 Barb Jones 4567
3 Jeff Teeves 8901
From Location Table
locationID locationName locPhext
1 Computer Room 3245
2 Board Room 1 8745
3 Conference Room 1 4564
Here is the data that I would like to pull in this format...
Ann Smith 1234
Barb Jones 4567
Board Room 1 8745
Computer Room 3245
Conference Room 1 4564
Jeff Teeves 8901
What SQL script could I use to produce the above results?
Thanks in advance
View 3 Replies
View Related
Jan 28, 2008
Hi!
I have a big database which need some separation
in multiple filegroups.
Therefore I plan to move tables to new filegroups.
As I can understand this must be done by
dropping the clustered index and
recreating it on new filegroup, then data will move.
1. best way to get a list of all clustered indexes (sys.indexes)?
2. how to get index scripts? (using SSMS, right click is manual task, but I need scripts for than 100 tables)
3. when having the scripts it should be easy to replace filegroup part
4. after movement: just shrinking old files?
Thanks for help ;-)
View 1 Replies
View Related
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 1 Replies
View Related
May 26, 2000
Hi Guys!
This SQL statement, though carefully written to delete only selected rows, deletes the entire A_Shift_Times table:
DELETE FROM A_Shift_Times
WHERE EXISTS (
SELECT 1
FROM
Users
WHERE
(A_Shift_Times.time_in >= CONVERT(DATETIME, '2000-05-29 00:00:00', 102)) AND
((Users.User_Password LIKE N'mrr%') OR (Users.User_Password LIKE N'work%')))
What gives? What am I doing wrong here?
View 1 Replies
View Related
Nov 2, 2015
I have a table and one of the column have tab delimited value and I need to separate the tab delimited values and keep them in separate rows.
ID      Name                 State     Country
1      Scott, Ricky           NSW      AUS
2       Martin                  VIC        AUS
3   James, Peter,John    WA       AUS
ID column is not a primary key. I want the output columns to identify the comma (,) and put them in separate rows as below
ID      Name                 State     Country
1      Scott                    NSW      AUS
1      Ricky                     NSW      AUS
2       Martin                  VIC        AUS
3     James                     WA       AUS
3     Peter                      WA       AUS
3     John                      WA       AUS
How to strip the camma(,) in SQL?
View 3 Replies
View Related
Mar 24, 2007
Hello.
I just created separate tables for each of my categories and wanted to know how to return them all to be viewed on one page using the SQL Datasource (or whatever) This is for user accounts. I just need to know that part.
Sincerely,
Computergirl
View 1 Replies
View Related
Dec 11, 2004
Hi all,
I want to produce some output for Mainframe application. For that I want to insert values from multiple table as source to a single column (huge in size)of a different table (Destination table). There may be same related records in all of the source tables with the primary key. When I export values from the source tables , each related records should be insterted to the destination table's field (multiple entries for each table). Please advise.
Thanks
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
May 22, 2008
i have two tables. 1st table gets the information from as400, then moves the data to table 2 at sql 2000 server. I need to see when as400 updateschanges or inserts new information to table 1 how to updatechange or insert into table 2?
View 4 Replies
View Related
Nov 17, 2005
Hi
i have this variable called order1 in the table called datetable and i want to display out the duplicate orders in sql. how do i do that? do i need to use group by ?
thanks in advance.! any help?
well i actually get the answers by doin this...
select order1 from datetable group by order1 having count(*) > 1
however now i want to compare the values date1 in the first duplicate orders to the second duplicate order...like compare aaaa of the first row and aaaa of the second row ...how to do that? i need to compare the date1 in minutes..i only wan duplicate orders with minutes difference less than 30mins
eg
id Order1 date1
1 aaaaa 20051001 12:45
2 aaaaa 20051001 12:10
3 bbbbb 20031002 15:05
4 bbbbb 20031002 15:50
View 9 Replies
View Related
Jul 30, 2007
Hi
I€™ve a table like this (in SQL Server 2000).
v1 v2
2 8
7 10
11 15
13 17
v1 value of a row must be grater than v2 value of previous row, other wise I need to display that row, in above example, I need to display 2nd & 4th rows.
please advise
Thanks
View 9 Replies
View Related
Feb 12, 2008
I running a query that returns a information from many tables. I need a way to compare the row returned to the previous row. This query currently returns the requested information for everytime an item placed. I need the query to return only the most recent order information. I can do this by doing a basic loop comparing the jobmatl.item and po.order_date. If the jobmatl.item match between the current row and last row compare the dates and keep the oldest.
Declare
@assembly ItemType
SET @assembly = '1110-2014'
SET @assembly = ISNULL(@assembly, '%')
SELECT
DISTINCT
jobmatl.item
, item.description
, item.drawing_nbr
, jobmatl.matl_qty
, itemloc.qty_on_hand
, vendaddr.name
, po.order_date
FROM
jobmatl with (nolock)
inner join item with (nolock) on item.item = jobmatl.item
inner join job with (nolock) on jobmatl.job = job.job
inner join poitem with (nolock) on item.item = poitem.item
inner join po with (nolock) on poitem.po_num = po.po_num
inner join vendaddr with (nolock) on vendaddr.vend_num = po.vend_num
inner join itemloc with (nolock) on itemloc.item = item.item
WHERE
jobmatl.job < ' 990000'
AND
job.item LIKE @assembly
ORDER BY
jobmatl.item
, po.order_date
View 4 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
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
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
May 8, 2008
I have the following variables VehicleID, TransactDate, TransactTime, OdometerReading, TransactCity, TransactState.
VehicleID is the unique vehicle ID, OdometerReading is the Odometer Reading, and the others are information related to the transaction time and location of the fuel card (similar to a credit card).
The records will be first grouped and sorted by VehicleID, TransactDate, TransactTime and OdometerReading. Then all records where the Vehicle ID and TransactDate is same for consecutive rows, AND TransactCity or TransactState are different for consecutive rows should be printed.
I also would like to add two derived variables.
1. Miles will be a derived variable that is the difference between consecutive odometer readings for the same Vehicle ID.
2. TimeDiff will be the second derived variable that will categorize the time difference for a particular vehicle on the same day.
My report should look like:
VehID TrDt TrTime TimeDiff Odometer Miles TrCity TrState
1296 1/30/2008 08:22:42 0:00:00 18301 000 Omaha NE
1296 1/30/2008 15:22:46 7:00:04 18560 259 KEARNEY NE
Can someone please help me here?
Thanks,
Romakanta
View 2 Replies
View Related
Nov 17, 2015
I would like to compare rows from 2 tables.
For ex:
Table1
Col1Â Col2 Col3
1      a    aa
2     b    bb
Table2
Col1Â Col2 Col3
1       a   aa
2      b    bb
Notice Table2.Col2 first row has an extra space " a", where Table1.Col2 value is "a".I need to compare all the columns, not just 1 column (any of the column can be different). Do we use CHECKSUM for this, or is there a better way to compare the 2 tables (in this case resulted in Table2 row 1 col 2 being the difference ?
View 10 Replies
View Related
Mar 2, 2011
I need to determine if a value for a group of rows is the same or different and I am not sure where to begin. I am fairly new to writing T-Sql code. I am working with SQL Server 2005. This is a work assignment; I am not a student.
I have two tables: One for Course and another for CourseDays. As the Course table implies, it lists courses for a school. The CourseDays tables has a row for each day a course is held. For instance, one course maybe held on Monday, Wednesday, and Friday, so there would be three rows, one row for each day. A given course could be held in a different room for each day. I am trying to write a query that returns one row for each course, and if the room number is the same for each day the class is held, return that room number in the row, else return 'Various'.
Course table:
CourseID, Desc
1 English
2 History
3 Science
CourseDays table:
CourseID, Day, RoomID
1 M 320
1 W 445
1 F 680
2 T 123
2 Th 123
3 M 514
3 T 514
3 W 521
3 Th 901
3 F 521
Expected results:
CourseID, Desc, RoomID
1 English Various
2 History 123
3 Science Various
I simplified the example, as there are about 30 columns in the table and about half a million rows.
View 14 Replies
View Related
Aug 14, 2014
How would I compare 2 rows to find the difference in the columns?
Example:
ID Column1 Column2 Column3
1 Text1 Text4
2 Text1 Text2 Text3
Result:
Column1: Text2 New
Column3: Text4 Old Text3 New
View 6 Replies
View Related
May 18, 2015
I need to fill the cell background color in the Matrix Table SSRSÂ report based on below requirement -
When
For A resource 'Capacity' equals to 'WorkExcludingCapacityAndAvailbility' and Project Status equals to OPEN then GREEN
When
For A resource 'Capacity' equals to 'WorkExcludingCapacityAndAvailbility' and Project Status equals to PENDING then RED
Here is the below mock-up data -
Looking for solution based on above mockup data how I can compare different rows value by resource considering Project Status Column value in Matrix Table report to set the background color.
View 3 Replies
View Related
Jan 29, 2014
Is there a efficient way to compare two different columns of 2 different rows in a data set as shown below.
For eg: I would like to DateDiff between Date2 of RowID 1 and Date1 of RowID 2 of IDNo 123. After this comparision , if datediff between two dates are <=14 then i want to update 1 else 0 in IsDateDiffLess14 of RowID1 . In below example its 0 because datediff of two dates >=14. So, want to compare the Date2 and Date1 in this sequence for the same IDNo. For RowID 6 there is only 1 row and no other row to compare, in this case IsDateDiffLess14 should be updated with 0.
RowID IDNo Date1 Date2 IsDateDiffLess14
1 123 04/10/2013 04/12/2013 0
2 123 05/10/2013 05/11/2013 1
3 123 05/21/2013 05/25/2013 0
4 112 01/10/2013 01/14/2013 1
5 112 01/27/2013 01/28/2013 0
6 120 03/10/2013 03/12/2013 0
View 4 Replies
View Related
Feb 18, 2015
What I need to be able to find is any records where the Discontinue_Date is greater than the Effective_Date on the next row for a given Customer ID and Part_ID. This is a customer pricing table so the Discontinue_Date of row 53 for example should never be greater than the Effective_Date of row 54130, these are the records I'm looking to find. So I'm looking for a SELECT query that would look for any records where this is true. Obviously the last Discontinue_Date row for a Customer_ID will not have a next row so I wouldn't want to return that.
View 9 Replies
View Related
Apr 8, 2015
I have the following information in a table. What I would like to do is pull out all the visits for each customer that are less than 30 days apart.
Customer# VisitDate
9082012-07-28 00:00:00.000
9082013-09-20 00:00:00.000
9082013-12-23 00:00:00.000
9082014-01-10 00:00:00.000
9082014-01-27 00:00:00.000
9082014-02-16 00:00:00.000
9082014-05-21 00:00:00.000
9082014-05-30 00:00:00.000
9082014-10-01 00:00:00.000
9082015-02-28 00:00:00.000
9082015-03-22 00:00:00.000
9272012-02-16 00:00:00.000
9272014-12-14 00:00:00.000
9272014-12-23 00:00:00.000
View 2 Replies
View Related
Nov 9, 2015
We write to a log file each time a job runs. We give each job a unique batchid. I want to compare the run times of each step/record between two batch ids: '20150101888' and '20150101777'. Column Mins in the number of minutes each step ran. I am having trouble comparing the rows that have generic process and stepname – Trans Switch in this example. A new process within a batchid starts with a 'XX', 'Load'.
So I want to compare CA's Trans to CA's Tran Switch and ER's Trans Switch to ER's, etc. There can be multiple Trans Switch per process. There should be the same number between each batch, but no guarantees that something might change. Also, Trans Switch is not the record right after the new process (CA, ER) in production.
I have just made a very simplified example.
/**
Want to compare 20150101888 to 20150101777 and end up with this result set. Notice that the duplicate process/step within a process has the process (CA and ER in this example) and a sequential number added to it: 'CA Trans 1'. Need this to pull out the largest time differences.
Time difference, process, step, mins1, mins2, batchid1, batchid2
-6, CA, Load, 17, 23, 20150101888, 20150101777
0, CA Trans 1, Switch, 8, 8, 20150101888, 20150101777
-6, CA Trans 2, Switch, 9, 15, 20150101888, 20150101777
-4, ER, Load, 7, 11, 20150101888, 20150101777
-4, ER Trans 1, Switch, 7, 11, 20150101888, 20150101777
**/
[Code] ....
View 4 Replies
View Related
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
