This is for an audit table solution where you are saving the old version of the row as in version 1 from Triggers_2_Creating_Audit_Trails.html.

The table being audited is mytbl.
The Audit table name is mytbl_au.

The requirement is to find all rows that had a number of fields changed on a single day.
To do this you need to find all rows that were changed on that day.
Then find the values that the fields were updated to. This will be either the next version of the row in the audit table or if there is none then the current row in the source table.
Then compare the old with the new version to find any changes - allowing for changes to and from null.

Note to run this code on any sizeable audit table you will need to place indexes on that table - which may slow the souce table updates.
A reasonable index to place would be
UpdateDate, [pk fields]

To check for fields forename, surname, town, postcode in mytbl, audit table mytbl_au.

The code we are trying to generate follows:
t hold the pk of all rows that were changed on the relevant date.
t1 holds the data which was changed on the relevant date.
t2 holds the next data in the audit trail table.
t3 hold the curent version of the row - used if no t2 row.

You will need to change:
	The population of #TestFlds for the columns you are testing for change.
	@sqlfrom for the primary key columns.
	@sqlsel for the primary key columns.
	
	select *
	from	(
		select	t.pk1 ,
			t.pk2 ,
			old_forename = t1.forename ,
			new_forename = coalesce(t2.forename, t3.forename)
			old_surname = t1.surname ,
			new_surname = coalesce(t2.surname, t3.surname) ,
			old_surname = t1.town ,
			new_surname = coalesce(t2.town, t3.town) ,
			old_surname = t1.postcode ,
			new_surname = coalesce(t2.postcode, t3.postcode)
		from
			(select pkfld1, pkfld2, UpdateDate = min(UpdateDate) from mytbl_au where UpdateDate between @date and @date + 1 group by pkfld1, pkfld2) t
				join mytbl_au t1 
					on t.pkfld1 = t1.pkfld1 and t.UpdateDate = t1.UpdateDate
				left join (select pkfld1, UpdateDate = min(UpdateDate) from mytbl_au where UpdateDate > @date group by pkfld1) tx
					on t.pkfld1 = tx.pkfld1 and t.pkfld2 = tx.pkfld2 and t.UpdateDate = tx.UpdateDate
				left join mytbl_au t2 
					on t2.pkfld1 = tx.pkfld1 and t2.pkfld2 = tx.pkfld2 and t2.UpdateDate = tx.UpdateDate
				join mytbl t3 
					on t.pkfld1 = t3.pkfld1 and t.pkfld2 = t3.pkfld2
			) a
            where    ((old_forename <> new_forename) or (old_forename is null and new_forename is not null) or (old_forename is not null and new_forename is null))
            or         ((old_old_surname <> new_old_surname) or (old_old_surname is null and new_old_surname is not null) or (old_old_surname is not null and new_old_surname is null))
            or         ((old_town <> new_town) or (old_town is null and new_town is not null) or (old_town is not null and new_town is null))
            or         ((old_postcode <> new_postcode) or (old_postcode is null and new_postcode is not null) or (old_postcode is not null and new_postcode is null))


set nocount on
-- Place the fields required into a temporary table
drop table #TestFlds
go
create table #TestFlds (s varchar(200))
insert #TestFlds select 'forename'
insert #TestFlds select 'surname'
insert #TestFlds select 'town'
insert #TestFlds select 'postcode'


declare @sqlsel varchar(8000)
declare @sqlfrom varchar(8000)
declare @sqlwhere varchar(8000)

declare @tab varchar(1) ,
	@crlf varchar(2)
	select 	@tab = '	' ,
		@crlf = char(13) + char(10)
	
	select 	@sqlsel = 	coalesce(@sqlsel + ' ,' + @crlf, '')
				+ @tab + @tab + @tab + 'old_' + s + ' = t1.' + s + ' ,'
				+ @crlf  + @tab + @tab + @tab + 'new_' + s + ' = coalesce(t2.' + s + ', t3.' + s + ')' ,
		@sqlwhere =	coalesce(@sqlwhere + @crlf + @tab + @tab + 'or', @crlf + @tab + @tab + 'where')
				+ @tab + '((old_' + s + ' <> new_' + s + ') or (old_' + s + ' is null and new_' + s + ' is not null) or (old_' + s + ' is not null and new_' + s + ' is null))'
	from	#TestFlds
	
	select @sqlsel = 	@tab + 'select *'
				+ @crlf + @tab + 'from' + @tab + '('
				+ @crlf + @tab + @tab + 'select' + @tab + 't.pk1 ,'
				+ @crlf + @tab + @tab + @tab + 't.pk2 ,'
				+ @crlf + @sqlsel
	
	select @sqlfrom =	+ @crlf + @tab + @tab + 'from'
			+ @crlf + @tab + @tab + @tab + '(select pkfld1, pkfld2, UpdateDate = min(UpdateDate) from mytbl_au where UpdateDate between @date and @date + 1 group by pkfld1, pkfld2) t'
			+ @crlf + @tab + @tab + @tab + @tab + 'join mytbl_au t1'
			+ @crlf + @tab + @tab + @tab + @tab + @tab + 'on t.pkfld1 = t1.pkfld1 and t.UpdateDate = t1.UpdateDate'
			+ @crlf + @tab + @tab + @tab + @tab + 'left join (select pkfld1, UpdateDate = min(UpdateDate) from mytbl_au where UpdateDate > @date group by pkfld1) tx'
			+ @crlf + @tab + @tab + @tab + @tab + @tab + 'on t.pkfld1 = tx.pkfld1 and t.pkfld2 = tx.pkfld2 and t.UpdateDate = tx.UpdateDate'
			+ @crlf + @tab + @tab + @tab + @tab + 'left join mytbl_au t2' 
			+ @crlf + @tab + @tab + @tab + @tab + @tab + 'on t2.pkfld1 = tx.pkfld1 and t2.pkfld2 = tx.pkfld2 and t2.UpdateDate = tx.UpdateDate'
			+ @crlf + @tab + @tab + @tab + @tab + 'join mytbl t3'
			+ @crlf + @tab + @tab + @tab + @tab + @tab + 'on t.pkfld1 = t3.pkfld1 and t.pkfld2 = t3.pkfld2'
			+ @crlf + @tab + @tab + @tab + ') a'
	
	select @sqlsel + @sqlfrom + @sqlwhere