Doug,
After seeing your intended output the query will need to change a bit,
but I think I understand what you are after.
Could you do something like this: (beware this SQL psudeo-code it's
untested)
declare @User_modify table(
UserID int,
FieldName nvarchar(50),
NewValue nvarchar(50)
)
declare @col sysname
declare colList cursor local fast_forward for
select COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'USER_UPDATED'
open colList
-- loop over the column list @col will hold the current column
fetch next from colList into @col
while (@@fetch_status = 0) begin
-- insert matching records into the temp table
insert into @User_Modify(UserId, FieldName, newValue)
SELECT userid, @col,
cast(@col as sysname) as newValue
FROM users where @col > <cfqueryparam type="cf_sql_date"
value="#since#">
fetch next from colList into @col
end
close colList
deallocate colList
select * from @user_modify
This way you could offload a lot of the processing onto the SQL server.
HTH,
Rich Kroll
> -----Original Message-----
> From: Doug Bezona [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, January 09, 2007 1:59 PM
> To: CF-Talk
> Subject: RE: Getting changed fields from database using an audit table
>
> Thanks
>
> Unfortunately, simply having the two tables joined doesn't help.
>
> The issue isn't getting the correct data, it's simply that to get the
> output I need, I have to loop over each column in each record to see
> which column has been updated in a given time period, and output the
> info about that column.
>
> USERS has a single "lastupdated" column, just so we know when it was
> updated. USER_UPDATED has most of the same columns as USERS, but each
> column just holds a date stamp of the last time the corresponding
field
> was updated in USERS
>
> The data looks something like this:
>
> USERS:
>
> UserID Firstname Lastname
> ------ --------- -----------
> 1 John Doe
> 2 Jane Smith
>
>
> USER_UPDATE:
>
> UserID Firstname Lastname
> ------ ---------- -----------
> 1 07/08/2005 10/31/2006
> 2 05/10/2006 7/15/2006
>
>
> The output I am looking for is this, given an input date of
01/01/2006:
>
> UserID FieldName Value
> ------- ----------- -------
> 1 Lastname Doe
> 2 Firstname Jane
> 2 Lastname Smith
>
>
> The code produces the exact output I need, so the basic logic is fine
> (if not as fast as I need it to be).
>
> The queries run in a few milliseconds, so they aren't the bottleneck.
>
> It's the looping through each column in each record of the resultset
to
> check the updated date of each field when it's 12000 records x 89
> columns that's the bottleneck.
>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Create robust enterprise, web RIAs.
Upgrade & integrate Adobe Coldfusion MX7 with Flex 2
http://ad.doubleclick.net/clk;56760587;14748456;a?http://www.adobe.com/products/coldfusion/flex2/?sdid=LVNU
Archive:
http://www.houseoffusion.com/groups/CF-Talk/message.cfm/messageid:266098
Subscription: http://www.houseoffusion.com/groups/CF-Talk/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.4
