Changed column counts - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Changed column counts

Author

Topic

kimbelj
Starting Member

13 Posts

Posted - 2005-09-20 : 17:53:15

We have six months of data in tables containing 110 columns. I want to know from month to month the number or records that have changed for any given record by column. Using a brute force approach and my basic knowledge of SQL I can do something like:

SELECT COUNT(*) Month1, Month2 WHERE Table1.keyfield=Table2.keyfield
AND Month1.Column1 <> Month2.Column1

I can repeat this query for all 110 columns (Column2, Column3,...,Column110) and then all pairs of consecutive months resulting in 5*110=550 queries and a whole lot of typing and cutting and pasting. I'd like to avoid this.

I know there has to be something much more elegant and far less time consuming. I thought of using the column metadata and generating the queries on the fly and executing them, but I didn't get too far with this. Any ideas?

Thanks,

-- Jeff

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-09-20 : 18:04:42

providing stuff described here:
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

would be very helpfull as i for one have no idea what you need.

Go with the flow & have fun! Else fight the flow

kimbelj
Starting Member

13 Posts

Posted - 2005-09-20 : 19:11:55

Sorry, it seemed clear to me, but of course it's my problem. I'll follow the guidelines as best I can.

There are six tables each representing one month's worth of data containing 110 columns. For example:

CREATE TABLE Month1(keyfield int, Column1 varchar(20),Column2 varchar(20),...,Column110 int)
CREATE TABLE Month2(keyfield int, Column1 varchar(20),Column2 varchar(20),...,Column110 int)

For two records (of 3.6 million or so):

INSERT INTO Month1 (keyfield, Column1, Column2,...Column110) VALUES (1, 'data','more data',...,5)
INSERT INTO Month2 (keyfield, Column1,Column2,...Column110) VALUES (1, 'data','different data',...,5)

etc.

I want to be able to compare the Month1 table with Month2 (for example) and determine for a given column how many values have changed from Month1 to Month2 matched on keyfield for all records.

For the above example with one record in each table I can use the query:

SELECT COUNT(*) Month1, Month2 WHERE Table1.keyfield=Table2.keyfield
AND Month1.Column1 <> Month2.Column1

Expected Result: 0

Then:

SELECT COUNT(*) Month1, Month2 WHERE Table1.keyfield=Table2.keyfield
AND Month1.Column2 <> Month2.Column2

Expected Result: 1
.
.
.

SELECT COUNT(*) Month1, Month2 WHERE Table1.keyfield=Table2.keyfield
AND Month1.Column110 <> Month2.Column110

Expected Result: 0

I can repeat this query for all 110 columns, then duplicate it for each of the 4 other pairs of consecutive months (Month2 vs Month3, Month3 vs Month4, etc.) but as I said it will require a lot of typing and cutting and pasting. It would be nice to interate through all the columns programatically or through a single query. A single query would be expected to return 110 values each corresponding to a column and the number of changed records.

I hope this clarifies my question.

Thanks,

-- Jeff

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources