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 RepliesFor example, I have 2 tables that have very similar fields
Table 1 Table 2
ShipRpt ShipRpt
invoice invoice
total sold total sold
Is it possible to compare each of these fields and return any record that has non matching data? I would join the tables on the shipping report or invoice and only compare the total sold field. If the invoices match and the total sold is different in each table, can I just return the 1 record?
I have the following cursor that I am comparing 2 tables, theproduction table and a copy of the production table, I want results ofall address's that are like the address1 field...the problem is...myresults are giving me every field and if there is more than one, it isputting it in a grid....I only want to see results if they are 1 for the same address fieldthis is what I have so far....declare @address1 char(61),@city char(61)declare address_cursor CURSOR FORSELECT address1,city FROM test.dbo.testaddOPEN address_cursorfetch next from address_cursor into @address1,@citywhile @@fetch_status = 0BEGINselect * from testadd where @address1 like '%' + address1 + '%' and@city = cityFetch next from address_cursor into @address1,@cityPrintENDCLOSE address_cursorDEallocate address_cursor
View 4 Replies View RelatedI 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.
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
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 RelatedHow can I create a Table whose one field will be 'tableid INT IDENTITY(1,1)' and other fields will be the fields from the table "ashu".
can this be possible in SQL Server without explicitly writing the"ashu" table's fields name.
I am trying to multiple update records in Table B from a single record in Table A. To identify the records that need to be updated I used this:
Select
Table1.cfirstnameas'Table
1 First',Table1.clastnameas'Table
1 Last',Table1.ifamilyid,CR.icontactid,CR.lLiveswithStudent,
Table2.cfirstnameas'Table
2 First',Table2.cLastNameas'Table
2 Last',Table2.ilocationidas'Table
2 Location',Table1.iLocationIDas'Table
1 Location'
fromTable1
JoinTable3
CRonTable1.istudentid=CR.istudentid
JoinTable2
onCR.icontactid=Table2.iContactID
whereCR.lLivesWithStudent=1
andTable1.ifamilyid>0
andTable1.ilocationid<>Table2.iLocationIDandTable1.lCurrent=1
I need to update the ilocationid from Table 1 to all Table 2 records related to Table 1but there is no direct relation from Table 1 to Table 2. I needed Table 3 to make the connection from Table 1 to 2.
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.
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
Is it possible to send a sql statement to update column 1 where the current time is 10 mins older than the time in col 2.
Column 1 is holding a true statement that we need to update to false if over 10 mins old...
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
hi friends i am reading a csv file and storing the data in table i have created the tables like this:
coloumn names datatype constraints
start_date_time varchar(50) allownull = true
ani varchar(50) allownull=true
dialed_digit varchar(50) allownull=true
actual_dur varchar(50) allownull=true
rounded_dur varchar(50) allownull=true
cost varchar(50) allownull=true
and the second table as
coloumn names datatype constraints
country varchar(50) allownull = true
code varchar(50) allownull=true
rate/min varchar(50) allownull=true
now i want to relate and join the table so that i can create a view with the following coloumns such as
start_date_time, dialed_digits, rounded_digits, cost, country, code,rate/min so can any body help me please i have tried outerjoin but it gave me rudundant data so please help me plz its urgent...
why we will go to install named instance as we having default instance already installed. Is there any advantage of named instance.
View 6 Replies View RelatedHello, I am wondering how to select the max date from a database view containing multiple date fields for a particular record. I am rusty at SQL and can't seem to get started. Here's an example of the data:
PRCL_IDPROJ_STRT_DTORD_DT ...
1242/3/20063/5/2006
5206/3/20068/2/2006
6412/31/2005
1872/14/20063/16/2006
I need to be able to compare the max date for each PRCL_ID record ("where prcl_id="). In the example above, prcl_id 124 has a max date of 3/5/2006, and prcl_id 520 has a max date of 8/2/2006, etc. The results of the SQL query should return the PRCL_ID and the max date.
Example query results:
PRCL_ID ORD_DT
124 3/5/2006
Please let me know if you can help or if I can provide more info.
Thanks,
Alexkav
Hello,
How can I solve this problem.
For example, I have two databases. db1 and db2.
I want to compare two float fields from tables from db1 and db2 and the present the difference between these fields in a column in reporting services.
ex. loop1
value from db1.table_db1
4
value from db2.table_db2
6
diff
2
ex. loop2
value from db1.table_db1
2
value from db2.table_db2
2
diff
0
..continues...
Should I create 2 datasets and 2 datasources? Must I write custom code for this?
Regards
Can anyone please help me to find the diffence between two fields.
Field1 - 16:12:27:123
Field2 - 16:12:36:750
I need to find the difference between field2 and field1??
There is a stored procedure. It uses linked server. As we will be migrating to amazon cloud, our architect instructed not to replace linked server with openquery.
View 8 Replies View RelatedI had a post a week or so ago with this issue, I found the cause, but cannot figure out how to fix it... The problem is the comparison on the NVarChar(Max) fields when one of them exceeds 4000 chars. If I comment out the following lines of the stored proc, everything works. I tested without using COALESCE and it still does not work. COALESCE(Comments, '') = COALESCE(@o_Comments, '') ANDCOALESCE(SpecialNotes, '') = COALESCE(@o_SpecialNotes, '') ANDCOALESCE(IAppComments, '') = COALESCE(@o_IAppComments, '') ANDCOALESCE(MgmtNotes, '') = COALESCE(@o_MgmtNotes, '') AND set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[pe_updateAppraisal]
-- Add the parameters for the stored procedure here
@OrderId INT,
@FileNumber NVarChar(25),
@OrderDate DateTime,
@ClientID NVarChar(25),
@ClientFileNumber NVarChar(25),
@PropertyTypeID INT,
@EstimatedValue money,
@PurchaseValue money,
@LoanOfficer NVarChar(50),
@ReportID INT,
@ReportFee money,
@FeeBillInd bit,
@FeeCollectInd bit,
@CollectAmt money,
@Borrower NVarChar(50),
@StreetAddrA NVarChar(50),
@StreetAddrB NVarChar(50),
@City NVarChar(50),
@CountyID INT,
@StateID INT,
@Zip NVarChar(50),
@ContactName NVarChar(50),
@PhoneA NVarChar(50),
@PhoneB NVarChar(50),
@PhoneC NVarChar(50),
@ApptDate DateTime,
@ApptTime NVarChar(25),
@AppraiserID INT,
@InspectionDate DateTime,
@DateMailed DateTime,
@TrackingInfo NVarChar(50),
@ReviewedBy INT,
@PreNotesID INT,
@PostNotesID INT,
@StatusID INT,
@Comments NVarChar(MAX),
@SpecialNotes NVarChar(MAX),
@MgmtName NVarChar(50),
@MgmtContactName NVarChar(50),
@MgmtAddress NVarChar(50),
@MgmtPhone NVarChar(50),
@MgmtFax NVarChar(50),
@MgmtFee money,
@CheckNumber NVarChar(25),
@MgmtNotes NVarChar(MAX),
@SentAppraiser DateTime,
@InfoReceived DateTime,
@CheckReceived DateTime,
@CheckMailed DateTime,
@INumFamilies NVarChar(10),
@IStyle NVarChar(15),
@IUnit NVarChar(15),
@IConstruction NVarChar(15),
@IBasement NVarChar(10),
@IBFinished NVarChar(10),
@IGarage NVarChar(10),
@INumCars NVarChar(2),
@IGarageType NVarChar(10),
@IContactHas NVarChar(10),
@IAvailable NVarChar(10),
@IInformedAmt NVarChar(5),
@IRealtorContract NVarChar(10),
@IContractContact NVarChar(50),
@IPermitCO NVarChar(10),
@ICORenewal NVarChar(10),
@IRenewalInt NVarChar(15),
@IAppComments NVarChar(MAX),
@IKitchen1 NVarChar(5),
@IKitchen2 NVarChar(5),
@IKitchen3 NVarChar(5),
@IKitchen4 NVarChar(5),
@IKitchenB NVarChar(5),
@IBedroom1 NVarChar(5),
@IBedroom2 NVarChar(5),
@IBedroom3 NVarChar(5),
@IBedroom4 NVarChar(5),
@IBedroomB NVarChar(5),
@IBathroom1 NVarChar(5),
@IBathroom2 NVarChar(5),
@IBathroom3 NVarChar(5),
@IBathroom4 NVarChar(5),
@IBathroomB NVarChar(5),
@AppraiserPerc NVarChar(6),
@AppraiserFee money,
@o_OrderId INT,
@o_FileNumber NVarChar(25),
@o_OrderDate DateTime,
@o_ClientID NVarChar(25),
@o_ClientFileNumber NVarChar(25),
@o_PropertyTypeID INT,
@o_EstimatedValue money,
@o_PurchaseValue money,
@o_LoanOfficer NVarChar(50),
@o_ReportID INT,
@o_ReportFee money,
@o_FeeBillInd bit,
@o_FeeCollectInd bit,
@o_CollectAmt money,
@o_Borrower NVarChar(50),
@o_StreetAddrA NVarChar(50),
@o_StreetAddrB NVarChar(50),
@o_City NVarChar(50),
@o_CountyID INT,
@o_StateID INT,
@o_Zip NVarChar(50),
@o_ContactName NVarChar(50),
@o_PhoneA NVarChar(50),
@o_PhoneB NVarChar(50),
@o_PhoneC NVarChar(50),
@o_ApptDate DateTime,
@o_ApptTime NVarChar(25),
@o_AppraiserID INT,
@o_InspectionDate DateTime,
@o_DateMailed DateTime,
@o_TrackingInfo NVarChar(50),
@o_ReviewedBy INT,
@o_PreNotesID INT,
@o_PostNotesID INT,
@o_StatusID INT,
@o_Comments NVarChar(MAX),
@o_SpecialNotes NVarChar(MAX),
@o_MgmtName NVarChar(50),
@o_MgmtContactName NVarChar(50),
@o_MgmtAddress NVarChar(50),
@o_MgmtPhone NVarChar(50),
@o_MgmtFax NVarChar(50),
@o_MgmtFee money,
@o_CheckNumber NVarChar(25),
@o_MgmtNotes NVarChar(MAX),
@o_SentAppraiser DateTime,
@o_InfoReceived DateTime,
@o_CheckReceived DateTime,
@o_CheckMailed DateTime,
@o_INumFamilies NVarChar(10),
@o_IStyle NVarChar(15),
@o_IUnit NVarChar(15),
@o_IConstruction NVarChar(15),
@o_IBasement NVarChar(10),
@o_IBFinished NVarChar(10),
@o_IGarage NVarChar(10),
@o_INumCars NVarChar(2),
@o_IGarageType NVarChar(10),
@o_IContactHas NVarChar(10),
@o_IAvailable NVarChar(10),
@o_IInformedAmt NVarChar(5),
@o_IRealtorContract NVarChar(10),
@o_IContractContact NVarChar(50),
@o_IPermitCO NVarChar(10),
@o_ICORenewal NVarChar(10),
@o_IRenewalInt NVarChar(15),
@o_IAppComments NVarChar(MAX),
@o_IKitchen1 NVarChar(5),
@o_IKitchen2 NVarChar(5),
@o_IKitchen3 NVarChar(5),
@o_IKitchen4 NVarChar(5),
@o_IKitchenB NVarChar(5),
@o_IBedroom1 NVarChar(5),
@o_IBedroom2 NVarChar(5),
@o_IBedroom3 NVarChar(5),
@o_IBedroom4 NVarChar(5),
@o_IBedroomB NVarChar(5),
@o_IBathroom1 NVarChar(5),
@o_IBathroom2 NVarChar(5),
@o_IBathroom3 NVarChar(5),
@o_IBathroom4 NVarChar(5),
@o_IBathroomB NVarChar(5),
@o_AppraiserPerc NVarChar(6),
@o_AppraiserFee money
AS
BEGIN
UPDATE Orders
SET FileNumber = @FileNumber,
OrderDate = @OrderDate, ClientID = @ClientID,
ClientFileNumber = @ClientFileNumber, PropertyTypeID = @PropertyTypeID,
EstimatedValue = @EstimatedValue, PurchaseValue = @PurchaseValue,
LoanOfficer = @LoanOfficer, ReportFee = @ReportFee,
FeeBillInd = @FeeBillInd, FeeCollectInd = @FeeCollectInd,
CollectAmt = @CollectAmt, Borrower = @Borrower,
StreetAddrA = @StreetAddrA, StreetAddrB = @StreetAddrB,
City = @City, CountyID = @CountyID, StateID = @StateID, Zip = @Zip,
ContactName = @ContactName, PhoneA = @PhoneA, PhoneB = @PhoneB,
PhoneC = @PhoneC, ApptDate = @ApptDate, ReportID = @ReportID,
ApptTime = @ApptTime, AppraiserID = @AppraiserID,
InspectionDate = @InspectionDate, DateMailed = @DateMailed,
TrackingInfo = @TrackingInfo, ReviewedBy = @ReviewedBy,
StatusID = @StatusID, Comments = @Comments,
SpecialNotes = @SpecialNotes, CheckNumber = @CheckNumber,
MgmtName = @MgmtName, MgmtContactName = @MgmtContactName,
MgmtAddress = @MgmtAddress, MgmtPhone = @MgmtPhone,
MgmtFax = @MgmtFax, MgmtFee = @MgmtFee, MgmtNotes = @MgmtNotes,
CheckMailed = @CheckMailed, CheckReceived = @CheckReceived,
InfoReceived = @InfoReceived, SentAppraiser = @SentAppraiser,
PreNotesID = @PreNotesID, PostNotesID = @PostNotesID,
INumFamilies = @INumFamilies,
IStyle = @IStyle, IUnit = @IUnit, IConstruction = @IConstruction,
IBasement = @IBasement, IBFinished = @IBFinished,
IGarage = @IGarage, INumCars = @INumCars,
IGarageType = @IGarageType, IContactHas = @IContactHas,
IAvailable = @IAvailable, IInformedAmt = @IInformedAmt,
IRealtorContract = @IRealtorContract, IContractContact = @IContractContact,
IPermitCO = @IPermitCO, ICORenewal = @ICORenewal,
IRenewalInt = @IRenewalInt, IAppComments = @IAppComments,
IBedroomB = @IBedroomB, IBedroom1 = @IBedroom1, IBedroom2 = @IBedroom2,
IBedroom3 = @IBedroom3, IBedroom4 = @IBedroom4, IKitchenB = @IKitchenB,
IKitchen1 = @IKitchen1, IKitchen2 = @IKitchen2, IKitchen3 = @IKitchen3,
IKitchen4 = @IKitchen4, IBathroomB = @IBathroomB, IBathroom1 = @IBathroom1,
IBathroom2 = @IBathroom2, IBathroom3 = @IBathroom4, IBathroom4 = @IBathroom4,
AppraiserPerc = @AppraiserPerc, AppraiserFee = @AppraiserFee
WHERE OrderID = @o_OrderId AND
COALESCE(FileNumber, '') = COALESCE(@o_FileNumber, '') AND
COALESCE(OrderDate, 01/01/1900) = COALESCE(@o_OrderDate, 01/01/1900) AND
COALESCE(ClientID, 0) = COALESCE(@o_ClientID, 0) AND
COALESCE(ClientFileNumber, '') = COALESCE(@o_ClientFileNumber, '') AND
COALESCE(PropertyTypeID, 0) = COALESCE(@o_PropertyTypeID, 0) AND
COALESCE(EstimatedValue, 0) = COALESCE(@o_EstimatedValue, 0) AND
COALESCE(PurchaseValue, 0) = COALESCE(@o_PurchaseValue, 0) AND
COALESCE(LoanOfficer, '') = COALESCE(@o_LoanOfficer, '') AND
COALESCE(ReportID, 0) = COALESCE(@o_ReportID, 0) AND
COALESCE(ReportFee, 0) = COALESCE(@o_ReportFee, 0) AND
COALESCE(FeeBillInd, 0) = COALESCE(@o_FeeBillInd, 0) AND
COALESCE(FeeCollectInd, 0) = COALESCE(@o_FeeCollectInd, 0) AND
COALESCE(CollectAmt, 0) = COALESCE(@o_CollectAmt, 0) AND
COALESCE(Borrower, '') = COALESCE(@o_Borrower, '') AND
COALESCE(StreetAddrA, '') = COALESCE(@o_StreetAddrA, '') AND
COALESCE(StreetAddrB, '') = COALESCE(@o_StreetAddrB, '') AND
COALESCE(City, '') = COALESCE(@o_City, '') AND
COALESCE(CountyID, 0) = COALESCE(@o_CountyID, 0) AND
COALESCE(StateID, 0) = COALESCE(@o_StateID, 0) AND
COALESCE(Zip, '') = COALESCE(@o_Zip, '') AND
COALESCE(ContactName, '') = COALESCE(@o_ContactName, '') AND
COALESCE(PhoneA, '') = COALESCE(@o_PhoneA, '') AND
COALESCE(PhoneB, '') = COALESCE(@o_PhoneB, '') AND
COALESCE(PhoneC, '') = COALESCE(@o_PhoneC, '') AND
COALESCE(ApptDate, 01/01/1900) = COALESCE(@o_ApptDate, 01/01/1900) AND
COALESCE(ApptTime, '') = COALESCE(@o_ApptTime, '') AND
COALESCE(AppraiserID, 0) = COALESCE(@o_AppraiserID, 0) AND
COALESCE(InspectionDate, 01/01/1900) = COALESCE(@o_InspectionDate, 01/01/1900) AND
COALESCE(DateMailed, 01/01/1900) = COALESCE(@o_DateMailed, 01/01/1900) AND
COALESCE(TrackingInfo, '') = COALESCE(@o_TrackingInfo, '') AND
COALESCE(ReviewedBy, 0) = COALESCE(@o_ReviewedBy, 0) AND
COALESCE(PreNotesID , 0) = COALESCE(@o_PreNotesID, 0) AND
COALESCE(PostNotesID, 0) = COALESCE(@o_PostNotesID, 0) AND
COALESCE(StatusID, 0) = COALESCE(@o_StatusID, 0) AND
/*COALESCE(Comments, '') = COALESCE(@o_Comments, '') AND
COALESCE(SpecialNotes, '') = COALESCE(@o_SpecialNotes, '') AND*/
COALESCE(CheckNumber, '') = COALESCE(@o_CheckNumber, '') AND
COALESCE(MgmtName, '') = COALESCE(@o_MgmtName, '') AND
COALESCE(MgmtContactName, '') = COALESCE(@o_MgmtContactName, '') AND
COALESCE(MgmtAddress, '') = COALESCE(@o_MgmtAddress, '') AND
COALESCE(MgmtPhone, '') = COALESCE(@o_MgmtPhone, '') AND
COALESCE(MgmtFax, '') = COALESCE(@o_MgmtFax, '') AND
COALESCE(MgmtFee, '') = COALESCE(@o_MgmtFee, '') AND
/*COALESCE(MgmtNotes, '') = COALESCE(@o_MgmtNotes, '') AND*/
COALESCE(SentAppraiser, 01/01/1900) = COALESCE(@o_SentAppraiser, 01/01/1900) AND
COALESCE(InfoReceived, 01/01/1900) = COALESCE(@o_InfoReceived, 01/01/1900) AND
COALESCE(CheckReceived, 01/01/1900) = COALESCE(@o_CheckReceived, 01/01/1900) AND
COALESCE(CheckMailed, 01/01/1900) = COALESCE(@o_CheckMailed, 01/01/1900) AND
COALESCE(INumFamilies, '') = COALESCE(@o_INumFamilies, '') AND
COALESCE(IStyle, '') = COALESCE(@o_IStyle, '') AND
COALESCE(IUnit, '') = COALESCE(@o_IUnit, '') AND
COALESCE(IConstruction, '') = COALESCE(@o_IConstruction, '') AND
COALESCE(IBasement, '') = COALESCE(@o_IBasement, '') AND
COALESCE(IBFinished, '') = COALESCE(@o_IBFinished, '') AND
COALESCE(IGarage, '') = COALESCE(@o_IGarage, '') AND
COALESCE(INumCars, '') = COALESCE(@o_INumCars, '') AND
COALESCE(IGarageType, '') = COALESCE(@o_IGarageType, '') AND
COALESCE(IContactHas, '') = COALESCE(@o_IContactHas, '') AND
COALESCE(IAvailable, '') = COALESCE(@o_IAvailable, '') AND
COALESCE(IInformedAmt, '') = COALESCE(@o_IInformedAmt, '') AND
COALESCE(IRealtorContract, '') = COALESCE(@o_IRealtorContract, '') AND
COALESCE(IContractContact, '') = COALESCE(@o_IContractContact, '') AND
COALESCE(IPermitCO, '') = COALESCE(@o_IPermitCO, '') AND
COALESCE(ICORenewal, '') = COALESCE(@o_ICORenewal, '') AND
COALESCE(IRenewalInt, '') = COALESCE(@o_IRenewalInt, '') AND
/*COALESCE(IAppComments, '') = COALESCE(@o_IAppComments, '') AND*/
COALESCE(IKitchen1, '') = COALESCE(@o_IKitchen1, '') AND
COALESCE(IKitchen2, '') = COALESCE(@o_IKitchen2, '') AND
COALESCE(IKitchen3, '') = COALESCE(@o_IKitchen3, '') AND
COALESCE(IKitchen4, '') = COALESCE(@o_IKitchen4, '') AND
COALESCE(IKitchenB, '') = COALESCE(@o_IKitchenB, '') AND
COALESCE(IBedroom1, '') = COALESCE(@o_IBedroom1, '') AND
COALESCE(IBedroom2, '') = COALESCE(@o_IBedroom2, '') AND
COALESCE(IBedroom3, '') = COALESCE(@o_IBedroom3, '') AND
COALESCE(IBedroom4, '') = COALESCE(@o_IBedroom4, '') AND
COALESCE(IBedroomB, '') = COALESCE(@o_IBedroomB, '') AND
COALESCE(IBathroom1, '') = COALESCE(@o_IBathroom1, '') AND
COALESCE(IBathroom2, '') = COALESCE(@o_IBathroom2, '') AND
COALESCE(IBathroom3, '') = COALESCE(@o_IBathroom3, '') AND
COALESCE(IBathroom4, '') = COALESCE(@o_IBathroom4, '') AND
COALESCE(IBathroomB, '') = COALESCE(@o_IBathroomB, '') AND
COALESCE(AppraiserPerc, 0) = COALESCE(@o_AppraiserPerc, 0) AND
COALESCE(AppraiserFee, 0) = COALESCE(@o_AppraiserFee, 0)
END
I have a query that ranks. Once I get the ranked fields is there a way to compare the 2 total_income fields?
Here's the query:
select * from
(
select cardholderid, appcnum, total_income ,Rank() over (PARTITION BY A.APPCNUM
ORDER BY A.EFFSTARTDATE DESC) as Rank
from
TBL_EPIC_BILLSTATUS A
) tmp
where Rank in (2,3) and CARDHOLDERID = '704355'
--------------and rank ((2),total_income) <> rank (3),total_income))
Looking to do something like this to see if the income is different
Hi All,
I am totally new to SSIS and im in the learing phase. I have a requirement as below,
I have two flat files (mainframe files), the structure i have given below,
File1:
070113
12345johnk
23456james
1st row is header record which has got date in YYMMDD format and remaining rows have emp no and emp name
File2:
070113
070113
070113
070113
contains 4 records which are dates.
The requirement is to compare the header date in file1 with the 4 dates in file2, if they are equal then it should load all the records in file1 except the header into a table and if they donot match then it should log an err msg. Please could someone provide a lead on this.
The files have same record length and fixed field delimited.
Thanks in advance
raj
Hello and Thank You in advance:
I would like to Use the SQL Tab in the Filter Selection of the View-Modify Current View Option in Outlook 2003.
I am specifically trying to create a statement that only displays those Contacts that have a Modified Date that is different than the Created date. In other words I want to view all the records that have been modified at any time after the date of their creation.
I have been unable to create a SQL statement that compares the Created and Modified fields in the Contacts. Would you please explain and show what the correct syntax is in order to compare the values of two fields within a given Contact?
These are all examples of what I have tried and that have not worked. I cannot find documentation for this anywhere only small articles here and there that are not enough for me to develop a complete answer from:
(("urnchemas:calendar:created" >= '1/1/2001 12:00 AM' AND
"urnchemas:calendar:created" <= '12/31/2001 12:00 AM')
AND ("urnchemas:calendar:created" <> "urnchemas:calendar:lastmodified" ))
("urnchemas:calendar:created" <> "urnchemas:calendar:lastmodified" )
(("urnchemas:calendar:created" >= '1/1/2001 12:00 AM' AND
"urnchemas:calendar:created" <= '12/31/2001 12:00 AM') AND
("DAV:getlastmodified" >= '1/1/2001 12:00 AM' AND
"DAV:getlastmodified" <= '12/31/2007 12:00 AM'))
("urnchemas:calendar:created" <> "DAV:getlastmodified")
("DAV:getlastmodified" <> "urnchemas:calendar:created")
("DAV:getlastmodified" <> 'urnchemas:calendar:created')
("urnchemas:calendar:created" <> 'DAV:getlastmodified')
I have two nvarchar fields with time data 12:34:34 and the second one 12:34 I want to calculate the difference in Hours. The first field is called (OTIM) the second field is called (ReportedTime) if the name matters. I tried substring to trim the OTIM, I am unable to make it work.
View 3 Replies View RelatedI 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.
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
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 !!!
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?
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.
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
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.
Hi im trying to find the difference between 2 silmilar tables in sql 2000. 1 table has 279,042 records and the other has 279,003 records, I ideally want to difference so to enable me to merge the two tables
I would really appreciate any help
Hi,We have 2 tables as follows,TableA------------doc_id ver_id ref_dateTableB------------doc_id ver_id ref_min_dateWe need to find out the records that exists in TableA but not in TableBand also to get the minimum of TableA.ref_date from the result.For example,TableA has the following rowsdoc_id ver_id ref_date100 1 10-AUG-2006100 2 12-AUG-2006100 2 13-AUG-2006100 2 14-AUG-2006100 2 15-AUG-2006200 1 17-AUG-2006300 1 18-AUG-2006TableB has the following rowsdoc_id ver_id ref_date100 1 10-AUG-2006200 1 10-AUG-2006Result should be as followsdoc_id ver_id ref_date100 2 12-AUG-2006300 1 18-AUG-2006Please help us in writing the queryThanksAshok
View 1 Replies View Relatedwhich is more efficient...which takes less memory...how is the memory allocation done for both the types.
View 1 Replies View Related