January 7, 2014 at 10:08 am
I have a query with 53 Columns that I'm doing a compare on that shows the difference between two tables. I've been tasked with writing further sql that will go back into the sql code and do a loop on each column within the query and display the count difference between each individual column. I'm pretty new to SQL and am not sure of the best way to approach this. Should I attempt my first function? Would writing further sql code be a better solution? Thank in advance.
With New_Code as(
Select
new.primarykey,
new.column1,
new.column2
from database.schema.table1_with_new_Code new),
Old_code as (
Select
Old.primerykey,
Old.column1,
Old.column2
from database.schema.table2_with_Old_code Old)
Select 'Old_Code' as Code,x.* from (Select * from Old_code except Select * from New_Code) x
Union All
Select 'New _Code' as Code,y.* from (Select * from new_code except Select * from old_Code) y
order by primarykey;
January 7, 2014 at 10:59 am
Not really sure what you are looking for here. Please take a few minutes and read the first article in my signature for best practices when posting questions.
I would recommend that you change your habit about sorting by ordinal position. You should always sort by column name. If somebody else comes along and changes the order of the columns in the select statement your ordering will be wrong unless they also change the order by.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
January 7, 2014 at 11:17 am
Is it fair to say that the providerID would NOT change? In order to compare an item in one of the buckets to an item in the other bucket - you'd need to have a common key that relates the two.
In any case - I don't think that a function will help you. Stick to more SQL (which won't require any looping, since you can run in batch).
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
January 7, 2014 at 11:21 am
Yes provider ID would stay consistent and be the key to every other column.
January 7, 2014 at 11:26 am
Sean Lange (1/7/2014)
Not really sure what you are looking for here. Please take a few minutes and read the first article in my signature for best practices when posting questions.I would recommend that you change your habit about sorting by ordinal position. You should always sort by column name. If somebody else comes along and changes the order of the columns in the select statement your ordering will be wrong unless they also change the order by.
My apologies I'll read through the link. What I'm looking for is the best way to go through each column and return a result showing the number of differences between the two tables for that particlar column. I want the query to go through each column individually and return a result based on my except clause from the query.
Ex below with each number showing a difference between the data between the two colums.
|Provider Code|
6
January 7, 2014 at 1:26 pm
assuming you reuse you definitions of new and old (recommend you turn these into temp tables), then you could use something ilke the following:
with new as ( etc...),
old as ( etc...)
select Colname,count(*)
from
(
(select providerID, 'ProviderCode' Colname, ProviderCode from new except
select providerID, 'ProviderCode' Colname, ProviderCode from old)
union all
(select providerID, 'ProviderTypeID' Colname, ProviderTypeID from new except
select providerID, 'ProviderTypeID' Colname, ProviderTypeID from old)
union all
(select productID, 'ProviderTypeGroup' colname, ProviderTypeGroup from new except
select productID, 'ProviderTypeGroup' colname, ProviderTypeGroup from old)
etc...
) k
group by Colname
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply