Column Comparison

Hi,
I work at a university and am dealing with government data. We
obtain a recordset from them called ISIRS. These are in a fixed length
field format that I put into a table. This table has upwards of 450
fields (there was no better way to break it up, no relations exist).
Each student can have multiple ISIR records, each with a transaction
number always incrementing. I was hoping someone could help me out
here...
I need to be able to compare the current ISIR with the students
previous ISIRs in T-SQL. I could do this in code(.NET), but I am using
SQL Server Reports to present this data to the user and need a stored
procedure to do a column by column comparison and indicate the
differences in the row it returns.
psuedo-code:
returnRow = requested isir row
for each row in student's isirs
for each column in row's column
if returnRow[column] <> row[column] then
returnRow[column] = returnRow[column] + '#'
end if
end for each
end for each
I hope this makes sense. If we were using SQL Server 2005, I could do
this using CLR SP's, but we are on 2000.
Thanks for your help.Even with SQL Server 2005, I wouldn't reccomend doing this in a stored
procedure, becuase it would still involve hard coding the column names.
With 450 columns, it would make more sense to write a client side
application that iterates through each column programatically using the
column object reference number. The pseudo code you provided is similar to
the actual ADO.NET needed to do this.
http://support.microsoft.com/defaul...kb;en-us;310107
You said that each student can have multiple records, so you will need to
identify a primary key (ex: SSN + CourseNumber) to join each new records
with the appropriate new record.
"craiggwilson" <craiggwilson@.gmail.com> wrote in message
news:1140533890.693887.38640@.g14g2000cwa.googlegroups.com...
> Hi,
> I work at a university and am dealing with government data. We
> obtain a recordset from them called ISIRS. These are in a fixed length
> field format that I put into a table. This table has upwards of 450
> fields (there was no better way to break it up, no relations exist).
> Each student can have multiple ISIR records, each with a transaction
> number always incrementing. I was hoping someone could help me out
> here...
> I need to be able to compare the current ISIR with the students
> previous ISIRs in T-SQL. I could do this in code(.NET), but I am using
> SQL Server Reports to present this data to the user and need a stored
> procedure to do a column by column comparison and indicate the
> differences in the row it returns.
> psuedo-code:
> returnRow = requested isir row
> for each row in student's isirs
> for each column in row's column
> if returnRow[column] <> row[column] then
> returnRow[column] = returnRow[column] + '#'
> end if
> end for each
> end for each
> I hope this makes sense. If we were using SQL Server 2005, I could do
> this using CLR SP's, but we are on 2000.
> Thanks for your help.
>|||That's what I was afraid of. The front end to this is an ASP.NET /
WindowsForms application, but the report is straight from a SQL Query.
I appreciate your help.|||I have had some success generating brute force SELECTs to do this sort
of thing by writing queries against the system tables (or
INFORMATION_SCHEMA views).
Start by writing a SELECT that tests one column of the table and
returns the key and both the current and prior values. What you need
is 450 such SELECTS with just the column names changed.
So then write a SELECT against syscolumns (or
INFORMATION_SCHEMA.COLUMNS) that creates one long string matching the
SELECT you just wrote, but concatenating in the column name in place
of the one you used in the example.
Obviously this is just a starting point, but I have used such
techniques successfully.
Good luck!
Roy
On 21 Feb 2006 06:58:10 -0800, "craiggwilson" <craiggwilson@.gmail.com>
wrote:

>Hi,
> I work at a university and am dealing with government data. We
>obtain a recordset from them called ISIRS. These are in a fixed length
>field format that I put into a table. This table has upwards of 450
>fields (there was no better way to break it up, no relations exist).
>Each student can have multiple ISIR records, each with a transaction
>number always incrementing. I was hoping someone could help me out
>here...
>I need to be able to compare the current ISIR with the students
>previous ISIRs in T-SQL. I could do this in code(.NET), but I am using
>SQL Server Reports to present this data to the user and need a stored
>procedure to do a column by column comparison and indicate the
>differences in the row it returns.
>psuedo-code:
>returnRow = requested isir row
>for each row in student's isirs
> for each column in row's column
> if returnRow[column] <> row[column] then
> returnRow[column] = returnRow[column] + '#'
> end if
> end for each
>end for each
>I hope this makes sense. If we were using SQL Server 2005, I could do
>this using CLR SP's, but we are on 2000.
>Thanks for your help.