March 9, 2011 at 7:40 pm
What exactly does this mean "bump our data against their data and see what falls out"?
My new boss tells me to do this. But what Exactly does that mean in more specifics terms than compare them together.
She is a (tsql veteran) I am a jack of many trades master of none. But I am trying to atleast become advanced by the and of the year in tsql.
Is that saying common across the industry or just her way of saying it?
It is like you will need to import their data thats in a flat file they sent us to a table. In her ways it means
alter tableTheirs add ColumnInCommon int
Then we join both our table and their table on lets say ssn number to populate their column we added on.
update a
set a.OurColumnInCommon = b.theirColumnInCommon
from OurTable a inner join TheirTable b
on a.ssn = b.ssn
So great..but she leaves for meetings and I do not know exactly what she is looking for.
I can't ask her what she means, I feel like I should just know exactly what she wants.
I mean "and see what falls out" I guess I would need a left join or something and see what we have in our data that that the other company does not or vise versa basically the difference.
I guess if our table looked like below and theirs below
--tblOurs
----
--ID | ColumnInCommon | ssn | columnInquestion
-- 1 1 123 230.782
-- 2 2 124 900.21
-- 3 3 125 770.10
--tblTheirs
------
--ID | ColumnInCommon | ssn | columnInquestion
-- 1 1 123 230.78
-- 2 2 124 null
-- 3 3 125 800.56
Do you do a query like:
select * from tblOurs a inner join tblTheirs b
on a.columnInquestion <> b.columnInquestion
I guess what I am saying is what is a good utility query to see the differences 2 data sets?
And the other thing I want to know is this a common way to say we need to compare the differences in our table and their table, or is it just her way? "bump the 2 tables together and see what falls out.."
It may seem like a silly question but it bugs me to not know precisely what she means, some clarification on just what to do when getting these frequent ad-hoc query assignments.
Thanks in advance
March 9, 2011 at 9:30 pm
If she's a veteran then she should know that that's not a very good set of instructions to start from. You could always try approaching it with some form of humor (e.g. "Sorry - Unregulated Interoffice data bumping is specifically prohibited by our HR rules") and see if you can shake something more specific loose.
That said - if you're trying to do some basic cross-matahing to see what is common and what is different:
- start by doublechecking that your common columns are in fact common:
Select [my common column list] from mytable
except
Select [my common column list] from theirtable
Select [my common column list] from theirtable
except
Select [my common column list] from mytable
Select [my common column list] from theirtable
intersect
Select [my common column list] from mytable
Assuming they ARE common, then 1 and 2 and empty, 3 has all of the rows. If there's nothing in common, either the data isn't common, or - the tables are logical partitions of each other (i.e. they represents chunks of a bigger decentralized "table")
Once you can ascertain what is really in common - then start adding in column you don't think are common, and see how fast things start falling into the EXCEPT queries.
Depending on how fancy you need to get with "bumping", it might require an actual data comparison tool (RedGate has one which has been useful in the past to our DBA's.)
----------------------------------------------------------------------------------
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?
March 9, 2011 at 10:51 pm
adam spencer (3/9/2011)
It may seem like a silly question but it bugs me to not know precisely what she means, some clarification on just what to do when getting these frequent ad-hoc query assignments.
Why don't you ask her? (by mail if she wondered off)
Seriously, if a manager tells you to do something and you don't understand, ask for clarification from the manager. Only they know exactly what they want.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 11, 2011 at 7:28 am
In the meantime, yes... import their data into a table and do a FULL OUTER join between your table and theirs. Things with NULL on one side or the other will be an indication as to what "falls out" (ie: the differences between the two).
--Jeff Moden
Change is inevitable... Change for the better is not.
March 11, 2011 at 8:48 am
Like Gail suggested, you really need to ask you manager for clarification.
Some managers tend to use turbo language and invent their own slang to try to get things done.
It's up to you to get them to explain exactly what they want so you can translate it to your job.
In some cases such an interview results in them seeing they actually don't know what they intended at that time and they drop/alter their request.
Another advantage is they get back with their feet on the ground.
Back in the days, we had a manager requesting to alter our client numbering algorithm to be able to support a merge of client systems. Because we asked for clarification, he realized he had spoken before his turn and he intended only a theoretical investigation, no actual modification needed at that time.
Actually the whole brainfart was dropped later on.
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
March 11, 2011 at 9:30 am
I'd do what I could to find table and data differences between the two datasets, summarize them, and present that.
Are there column differences? (Like, you have first and last name, they have first, last and middle, or anything like that.)
Within the matching columns, what are the data differences? Not specifics, but just a summary. Like, they have 2,000 more rows of data than you do, or something of that sort.
Start simple, summarize broadly, present that, and get more specific questions from that.
Might look like:
They have a Middle Name column that we don't have in our data, and we have a Place of Birth column they don't have.
They have 2,217 rows of data we don't have, based on comparisons of SSNs, and we have 1,710 rows they don't have.
A full data merge, based on those differences, would be easy to accomplish in an afternoon/morning of work.
Are there specifics you'd like checked beyond that?
Data mining starts broad and then narrows down. That's how I'd treat it.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply