Posted by Otter at 4:40pm Apr 21 '10Ok guys,
I'm a regular SysAdmin kinda guy and not much of a DBA but I'm being asked to do some basic DBA work and I have a minor roadblock. I bet this would be an easy one for any kind 4k DBA who likes to help puzzle things out.
For this example please know the following:
* I do not have control over the separation or contents of the source tables, only destination tables and views.
* I will be fabricating column names with similar meaning to my actual problem.
Let's say I have
2009 schools
2008 schools
2007 schools
2006 schools
... going all the way back to 1980 in *separate tables*
each table has like variables. In particular each table has a 'SchoolId' along with other info info about the institution.
FYI - these are annual surveys (some of you may figure out what my data set is, that's ok!)
As schools open, close, or change names over time they get added or removed to that year's table. A SchoolId though once used, is never used again.
So 2009 schools will have new schools that 2008 didn't have, or update data for the same school listed in 2008. 2008 or 2007 or any previous year will have schools that 2009 doesn't have because they closed in 2008 and weren't included in the 2009 survey.... and so-on recursively.
I need to see a list of ALL schools over time, whether opened or closed... showing the most recent data for any *like* SchoolId (in case info was updated).
So I want a T-SQL view or set of views that does this:
Gives me all the results from the 2009 table
as well as
Gives me only the results from the 2008 table that don't appear at all in 2009 (as found by schoolid)
as well as
Gives me only the results from the 2007 table that don't appear in 2008 or 2009.
...
Gives me only the results from the 2006 table that don't appear in 2007, 2008, or 2009... and so-on going all the way back to 1980.
I can see clearly how these will be increasingly smaller sets of data over time. I can also see how this will be a lot of work for my server... that's ok because I intend to make a table out of it and do it only once a year (whenever a new survey comes in!)
I'm using MS-SQL 2008.
halp!
I'm a regular SysAdmin kinda guy and not much of a DBA but I'm being asked to do some basic DBA work and I have a minor roadblock. I bet this would be an easy one for any kind 4k DBA who likes to help puzzle things out.
For this example please know the following:
* I do not have control over the separation or contents of the source tables, only destination tables and views.
* I will be fabricating column names with similar meaning to my actual problem.
Let's say I have
2009 schools
2008 schools
2007 schools
2006 schools
... going all the way back to 1980 in *separate tables*
each table has like variables. In particular each table has a 'SchoolId' along with other info info about the institution.
FYI - these are annual surveys (some of you may figure out what my data set is, that's ok!)
As schools open, close, or change names over time they get added or removed to that year's table. A SchoolId though once used, is never used again.
So 2009 schools will have new schools that 2008 didn't have, or update data for the same school listed in 2008. 2008 or 2007 or any previous year will have schools that 2009 doesn't have because they closed in 2008 and weren't included in the 2009 survey.... and so-on recursively.
I need to see a list of ALL schools over time, whether opened or closed... showing the most recent data for any *like* SchoolId (in case info was updated).
So I want a T-SQL view or set of views that does this:
Gives me all the results from the 2009 table
as well as
Gives me only the results from the 2008 table that don't appear at all in 2009 (as found by schoolid)
as well as
Gives me only the results from the 2007 table that don't appear in 2008 or 2009.
...
Gives me only the results from the 2006 table that don't appear in 2007, 2008, or 2009... and so-on going all the way back to 1980.
I can see clearly how these will be increasingly smaller sets of data over time. I can also see how this will be a lot of work for my server... that's ok because I intend to make a table out of it and do it only once a year (whenever a new survey comes in!)
I'm using MS-SQL 2008.
halp!
DBAs to the rescue! Please! - Otter