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


ADVERTISEMENT

Compare Two Table And Update - Join Statement

Apr 27, 2015

I compare two table with JOIN statement. Now I would like to update one of them base on result. How it to do?

View 2 Replies View Related

Transact SQL :: Compare A Table Against Multiple Tables

Jul 16, 2015

I downloaded a day's worth of data into a single table first and named it as Full_Day_Data_Table.(10,000 records)

Later for some other purpose, downloaded the same day's data into five small tables.

Now when I compare the total row count of  Full_Day_Data_Table vs Sum of row counts of five small tables, the row count does not match. 

So, I would like to know the records that exists in Full_Day_Data_Table and not in small tables.

View 5 Replies View Related

DB Engine :: Compare Two Tables And Log Difference In New Table With Fields?

Apr 21, 2015

I want to compare two tables and log the difference in new table with the fields as (old value,new value, column name). The column name should be the changes value column.

View 10 Replies View Related

SQL 2012 :: Compare Two Tables A And B - Copy Matching Values To Table C

Mar 31, 2014

I have 3 table

table_A
table_B
table_C

TABLE_A
SNO NAME ID
1 RAJU 070491
2 VAMSHI 089767
3 ARUNA 068908

TABLE_B

SNO NAME ID
2 RAJU 070491
4 JKLKJ 098766

I WANT COMPARE TWO TABLES(TABLE_A & TABLE_B) SELECT TABLE_A MATCHING VALUES COPY IN TABLE_C

EX-

SNO NAME ID
1 RAJU 070491

View 3 Replies View Related

SQL Server 2008 :: Join Two Tables - Compare Amounts (Rent)

Jun 20, 2015

Refer to my query thread on msdn SQL site, how I can achieve the result.

[URL] .....

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

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

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

Compare Data And Populate In Same Table

Feb 10, 2015

I have the data as below,in which owner is assigning some task to another user.

INPUT
#########

OWNER ID TASK_ID TASK_ASSIGNEE

user1 11user2
user112user3
user1 13user4

PRIVILEGE table
#########

USER_DETAIL PRIVILEGE_ID

user110
user111
user112
user28
user35
user46

OWNER has one set of privilege details in privilege table. I need to check privilege for user2,user3 and user4 in privilege table, if privilege not matches with the user1 then i want to populate the data output as below

NEEDED OUTPUT
###########

OWNER ID TASK_ID TASK_ASSIGNEE

user1 11user2
user112user3
user1 13user4
user211user2
user312user3
user413user4

I am populating this data in the view.

View 5 Replies View Related

Compare Based On Table Values.

Sep 10, 2007

HiBelow is a table with sample data to explain what I want to achieve.trackID member markerallele1------- ------ -------------------------------------------------------------------------------------------------------4734 4577 01-D8S1179 13.54734 4577 02-D21S11 12.64734 4577 03-D7S820 2.04734 4577 04-CSF1PO 3.04734 4577 06-TH01 4.04734 4577 07-D13S317 5.04734 4577 08-D16S539 9.04734 4577 11-vWA 6.04734 4577 12-TPOX 7.04734 4577 13-D18S51 1.04734 4578 01-D8S1179 13.04734 4578 02-D21S11 12.64734 4578 03-D7S820 2.04734 4578 04-CSF1PO 4.04734 4578 06-TH01 3.04734 4578 07-D13S317 5.04734 4578 08-D16S539 9.04734 4578 11-vWA 7.04734 4578 12-TPOX 6.04734 4578 13-D18S51 1.0Okay I am trying to compare allele1 values for differences for two orthree different member having the same trackID. The list of markerswould always be same for all members belonging to a particulartrackID.So in the above sample data. The trackID is same which is 4734. Thereare two members 4577 and 4578. The markers are same for each membersbut the corresponding allele1 values are different.I want to compare these two and state whether they are same or not. Iwould like to do this on a form containing subforms.Feel free to ask for clarification.Thank you.

View 2 Replies View Related

How To Compare 2 Values In Different Table And Sources?

Aug 1, 2007



I got 1 xml source and 1 sql DB table..
Eg: Contact table in xml contains

Email
--------
bbb@yaho.com
aaa@yahoo.com
ccc@yahoo.com

then COntact table in SQL server contains

aaa@yahoo.com
ccc@yahoo.com
zzz@yahoo.com

i want to compare the two table if got same value update the row..

how to compare it in SSIS

thx

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

Audit Table To Compare And Find Difference

Feb 10, 2015

I have a table called [dbo].[co_audit_trail]..All records that get changed in our software gets written to this table.

CREATE TABLE [dbo].[co_audit_trail](
[seq_no] [bigint] IDENTITY(1,1) NOT NULL,
[create_complete] [tinyint] NULL,
[user_name] [varchar](60) NULL,
[db_event] [varchar](16) NULL,
[date_of_change] [datetime] NULL,

[code]....

1. It looks like a 'ª' delimiters between fields. I need to pull out the second column in the above example it is - 9999999999. This is the place reference, so I need that in a separate field.
2. I need a way to compare the two fields and report back the change. So in the above example shows EAST. The After image shows WEST - I need the before image of EAST in one column and the WEST in another column.

View 5 Replies View Related

Column Compare In Same Table And Show Only Changed Value

Aug 18, 2014

How can I compare column in the same table and show only column and value that has been changed.

declare @t table (GroupID CHAR(6),Text1 VARCHAR(MAX),Text2 VARCHAR(MAX),Text3 VARCHAR(MAX))

insert into @t
SELECT '11111','Text1','Text2','Text3'

insert into @t
SELECT '11111','Text1','Text2','Text4'

END RESULTS:

Column Name Old New
Column 3 |'Text3' |'Text4'

View 9 Replies View Related

SQL Server 2008 :: Compare If Row-Combination Exists In Other Table

Mar 10, 2015

I've the following two tables:

create table #temp1 (name varchar(5), id int)
insert into #temp1 (name, id)
(
select 'a', 5
union
select 'a', 8

[code]....

As a result I would need every name from #temp2, where both searchred id's (5 & 8) from #temp1 are included.In this example i would like to get 'e' as a result, because in #temp2 'e' has the id's 5, 8 and 25.I've tried using cross apply, but cross apply returns every Name that have one of the ids... in this case it would also return 'c'...

Selectdistinct b.name
from(
Selectdistinct name
, id
from#temp1
wherename = 'a'
) as a
cross join(
Selectdistinct name
, id
from#temp2
) as b
wherea.id = b.id

View 3 Replies View Related

SQL Server 2012 :: Compare A Table With Physical Path

Mar 31, 2015

I have a table with two columns

id | filepath
--------------------------------------------------
1| D:Doc filesThe BestHHT.JPG
2| D:Doc filesThe Bestsealed_pack.txt
3| D:Doc filesThe Bestlsbom.JPG
4| D:Doc filesThe Bestmoc.png
5| D:Doc filesThe Beststock.txt
6| D:Doc filesThe Bestdepot.JPG

And in a physical system there are more files than the table.

D:Doc filesThe BestHHT.JPG
D:Doc filesThe Bestsealed_pack.txt
D:Doc filesThe BestJKSlsbom.JPG
D:Doc filesThe Bestmoc.png
D:Doc filesThe Beststock.txt
D:Doc filesThe BestGDNdepot.JPG

D:Doc filesThe BestCASA.JPG
D:Doc filesThe BestSO.txt
D:Doc filesThe BestBA.JPG

I want to compare the filepath column in table with physical drive files and get the details of files which in table and not in physical and viceversa...

View 3 Replies View Related

SQL Server 2008 :: Compare Dates In Rows Of A Table?

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

Query To Compare Table Data Between Test And Production?

Jul 23, 2005

I am debugging one of our programs and ran the fix in Test. I would liketo compare table 1 between Production and Test. I want the query to outputcolumn 1 if Production <> Test output.What is the best way to achieve this?jeff--Message posted via http://www.sqlmonster.com

View 2 Replies View Related

Transact SQL :: Compare Two Table With Multiple Date Comparison

Oct 20, 2015

I need to take all records from table @A where ID = 1. Also i need to process the records with datewise from table @A. Here is the table structure

DECLARE @A TABLE (ID INT, ACCOUNT VARCHAR(10), EFFDT DATE)
INSERT INTO @A VALUES (1,'AAA','2015-10-01')
INSERT INTO @A VALUES (1,'BBB','2015-10-01')
INSERT INTO @A VALUES (1,'CCC','2015-10-01')
INSERT INTO @A VALUES (1,'AAA','2015-10-05')
INSERT INTO @A VALUES (1,'DDD','2015-10-01')
INSERT INTO @A VALUES (2,'AAA','2015-10-02')
INSERT INTO @A VALUES (2,'BBB','2015-10-02')
INSERT INTO @A VALUES (2,'CCC','2015-10-02')
INSERT INTO @A VALUES (2,'DDD','2015-10-02')

[code]...

how to achieve this in SQL query, i cannot use CTE or temp table as i need to use this code in another tool, it has to be single query, can use subquery or join would be better.

View 4 Replies View Related

Check Constraint - Compare Sum Of Two Values In One Table Against Values Located In Another Table

Jul 26, 2014

I am relatively new to SQL and as a project I have been asked to create the SQL for a simple database to record train details. I want to implement a check constraint which will prevent data from being inserted into a table if the weight of the train is more than the maximum towing weight of the locomotive. FOr instance, I need to add the unladen weight and maximum capacity of each wagon (located in the wagon type table) and compare it against the locomotive maximum pulling weight (the locomotive class table). I have the following SQL but it will not work:

check((select SUM(fwt.unladen_weight+fwt.maximum_payload) from
hauls as h,freight_wagon as fw,freight_wagon_type as fwt,train as t where
h.freight_wagon_serial_number = fw.freight_wagon_serial_number and
fw.freight_wagon_type = fwt.freight_wagon_type and
h.train_number = t.train_number) <
(select lc.maximum_towing_weight from locomotive_class as lc,locomotive as l,train as t where
lc.locomotive_class = l.locomotive_class and l.locomotive_serial_number = t.locomotive_serial_number))

The hauls table is where the constraint has been placed and is the intermediary table between train and freight wagon.

I may not have explained this very well; but in short, i need to compare the sum of two values in one table against a values located in another table...At present I keep getting a message telling me the sub query cannot return more than one row.

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

SQL Server 2008 :: Compare Data Between Identical Table Structure?

Jun 18, 2015

I have two identical table structure with at least 60 or more columns!!

Table A
ID numeric(18, 0) Primary Key,
RefID numeric(18, 0),
CoNum Varchar(15),
PrevCode Varchar(15),
DispCode VArchar(15),
EffDate Date,
Sal numeric(18, 0),
SDeposit numeric(18, 0) Etc....

I need to compare Data between two table and provide result like this!

RefId ID, CoNum, PrevCode, DispCode, EffDate, Sal, SDeposit
1, ,No Match, ,20,5200,0

So condition like. If table A Column Data does not Equal Table B Column data then

If datatype is Date then display Difference of Days.

if Datatype is Numeric then display Difference between value.( it display 0 if doesn't have difference)

if Datatype is Varchar then display No Match word.

View 0 Replies View Related

Integration Services :: How To Compare SSIS Variable And Column In Table

Apr 21, 2015

My Requirement IS : 1<sup>st</sup>run: if the record does not exist in the table insert the record (file_name, last_modified_file_date) and create a copy in the archive folder with file_name_currentdate.csv

Daily run: retrieve the last_modified_file_date from the input file and check if the retrieved date is greater than the last_modified_file_date in the table:

If true: create a copy of the input file in the archive folder and update the last_modified_file_date in the table with the retrieved date

If false don’t do nothing because the file has been archived in one of the previous runs.I have already retrieving the modified date and File Nae iserting into Filename Table: (That table has 2 columns which are FileName and FileDate) so In script task everytime the variable getting Modified date(retrieve the last_modified_file_date from the input file). How I can Compre the existing table record and variable. I have already imported the all Filenames and Modified into table like below.

View 3 Replies View Related

SQL Server 2008 :: Compare Data / Loop Through Dates And Insert Into Table

Sep 21, 2015

I have three tables:

"PaymentsLog"
"DatePeriod"
"PaidOrders"

As per below

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[PaymentsLog](

[Code] ....

Is there a way to look at the DatePeriod table and use the StartDtae and EndDate as the periods to be used in the select statement and then cursor through each date between these two dates and then insert the data in to the PaymentsLog table?

View 3 Replies View Related

Compare Data-type Xml In A Temp/variable/physical Table In MSSQL 2000

May 22, 2008

Does abyone know how to compare data-type xml in a temp/variable/physical table in MSSQL 2000?

I tried this works in MSSQL 2005,



Code Snippet
create Table #t1 ([c1] int identity(1,1) not null, [c2] text)
create Table #t2 ([c1] int identity(1,1) not null, [c2] text)
Insert into #t1
Values('This is a test')
Insert into #t2
Values('This is a test')
Select * from #t1
Select * from #t2
Select * from #t1 where [c2] LIKE (Select [c2] from #t2)
drop table #t1
drop table #t2


but not MSSQL 2000.

Server: Msg 279, Level 16, State 3, Line 12
The text, ntext, and image data types are invalid in this subquery or aggregate expression.


Is this true (from BOL)?




Code SnippetIn comparing these column values, if any of the columns to be compared are of type text, ntext, or image, FOR XML assumes that values are different (although they may be the same because Microsoft® SQL Server„¢ 2000 does not support comparing large objects); and elements are added to the result for each row selected.

View 1 Replies View Related







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