I have one database with several tables in it (table 1, table2, table3). In each table is two colums (colum1 = a number (201220) and colum2 = a number (0.50). Now, both tables will have rows with the same data in colum 1, but colum two will have different numbers (different prices). My goal is to run a query that will compare both colums in all three tables, take the lower of the three based on colum 2 and spit out the row. Obviously, this would output all rows (around 175k). The point is to create a least cost spreadsheet (csv) file based on evaluating all three tables.
1 st column 'A1Ctest' 2 nd column 'diagnoising heart disease' and my 3 rd column is combination of both columns 'A1Ctest for diagnoising heart disease'.Here i need to comapre 'A1Ctest' from 1st column and 'diagnoising heart disease' from 2 nd column
I have two table with the same name that reside in different databases. The two database have the same name, but reside in two linked servers TABLE A: [ServerA].[ProdDB].dbo.[Orders] TABLE B: [ServerB].[ProdDB].dbo.[Orders]
How do i find out if the two tables have the same number of columns or if the a column that exists on TableA does not exist on TableB.
Does any one have a script that could help me with this task. Thanks
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.
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.
Table A IdName 101Dante 102Henry 103Harold 104Arnold
Table B NumberName 102Dante 107Gilbert 109Harold 110Arnold 106Susan 112Marian
I want the result in table 3 like below, if value exists in Table A and not exists in Table B then the record should enter in table 3 with table name in new column, and vice versa.
Table C Col1Col2 HenryTable A Gilbert Table B Susan Table B Marian Table B
using below logic to get the values from tables..
select t1.columnA , t2.* from table1 t1 join table2 t2 on t2.columnB = t1.columnA
using below script to compare two tables and get the values.
how to get the count of 'Table A' , 'Table B' , 'Table A & Table B' using below script.
Ex: 'Table A' -- 150 'Table B' -- 300 'Table A & Table B' -- 150 SELECT Col1 = ISNULL(a.name,b.name), Col2 = CASE WHEN ISNULL(a.name,'') = '' THEN 'Table B' WHEN ISNULL(b.name,'') = '' THEN 'Table A' ELSE 'Table A & Table B' END FROM #tableA a FULL JOIN #tableB b ON a.name = b.name;
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
I have a database project where objects have been pulled in from the database using schema compare.
Unfortunately CDC tables which are referenced in stored procedures on the database have not been pulled in by the schema compare & hence I cannot build the project and deploy changes back to the database.
TRANAMT being the amount paid & TOTBAL being the balance due per the NAMEID & RMPROPID specified.The other table includes a breakdown of the total balance, in a manner of speaking, by charge code (thru a SUM(OPENAMT) query of DISTINCT CHGCODE
Also with a remaining balance (per CHGCODE) column. Any alternative solution that would effectively split the TABLE1.TRANAMT up into the respective TABLE2.CHGCODE balances? Either way, I can't figure out how to word the queries.
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 ?
I am trying to compare values from two columns but I don't know what I am doing, I tried select custom5,skipcount substr( custom5, 1, 4) as 'mynumber' from songs where custom5 > '0000%' where skipcount > cast( substr( custom5, 1, 4) as int ); But that is obviously wrong?
Hi,I have three columns and want to update the third column based on the comparison of first two columns in the same table.For instance:Col1 - val1, val2, val3, val4, val5......Col2 - NULL, NULL, val1, NULL, val2....Col3 - TRUE, TRUE, FALSE, FALSE, FALSE....Thanks for any help.
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',
Hello, I have the following table with 4 columns....firstname, lastname1, lastname2, EMAILTable has user names and email, I would like to generate a 5th columncalled DisplayName.The email Id is sometimes firstname.lastname1.lastname2@ and othersjust firstname.lastname1@I would like to generate the display name exactly like the email egfirstname.lastname1.lastname2@ displayName = firstname lastname1lastname2......so for james.smith display name = James Smith and forjames.earl.smith displayName = James Earl Smith etc etcIs there a way that I can check/compare email Id (before the @ part)with firstname, lastname1 and lastname2 and generate a display namebased on what was used for the email address?I hope I've explained this well :-)Many thanks in advance for any help/adviseYas
I have a problem. I have two feilds in a table. Both of them are (int). I want to be able make sure the same number is not put in both columns for each row. Both colums have to be a different number, and also if one is null, the other cannot be. How do I go about enforcing this policy?
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','')
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.
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?
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
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.
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.
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.
A customer has messed up while moving their databases. After working for a week they found that data is missing in the database.I have two backups, one from the old server and one from the new server today, they have been working in the new one for a week.
I need to compare these two databases and then update the new database with all data that is in the old one but not in the new database. Join the data in the two databases so to say. Both databases are from the same application so they use the same users, schema and so on.
I am trying to reproduce a problem that is probably related to how a particular SQL server is configured. Is there a tool or best practice that is useful to compare all of the configuration settings between two SQL Servers (same version)?
I'm rewriting a huge FOR XML EXPLICIT procedure to use FOR XML PATH, and need to compare previous output to the refactored one, so i didn't mess up XML structure.
The thing is, i'm not sure that SQL Server will always generate exactly same xml **string**, so i'd rather not compare by:
WHERE CAST(@xml_old AS NVARCHAR(MAX)) = CAST(@xml_new AS NVARCHAR(MAX))
nor do i want to manually validate every node, since the generated xml-structure is quite complex.
