I was doing some reporting recently, where I needed to compare dues payments by member category. I thought that since I was doing historical reporting, the activities table was the place to go. It contained the transaction date, amount, product code. I started using the category field from the name table (which i joined to activities), however I soon realised that I needed the historical member category, not the current one. To my disillusionment I found that iMIS does not populate the Category field in the activity table (Even though there is a space for it!). We did however have historical category information in the name_log table. So I set about to create a SQL script to back-populate the category field in the activities table based on the information in the name_log table.
I thought I would release the script here in case it is useful to others.
The script works as follows:
1. Parse the name_log table and extract out the the category change information.
2. Populate the category field in the activities table based on the current category information
3. Update the category information in the activities table based dates the category changed and dates entered in the activity table
4. Repeat step 3 until the number of rows updated each cycle does not change (you need to rerun the bottom section of this code repeatedly)
create table #category_temp (
id varchar(10),
date_time datetime,
[from] varchar(100),
[to] varchar(100)
)
insert into #category_temp (id, date_time, [from], [to])
select id,
date_time,
replace(replace(PARSENAME(replace(substring(log_text, 16, len(log_text)),'-> ','.'),2), ' ->', ''), ' ', '') as [from],
replace(replace(PARSENAME(replace(substring(log_text, 16, len(log_text)),'-> ','.'),1), ' ->', ''), ' ', '') as [to]
from name_log
where LOG_TEXT like 'Name.CATEGORY:%'
update #category_temp set [from] = [to], [to] = '' where [from] is null -- do a bit of fixing up
update activity set category = coalesce((select n.category from name n where n.id = activity.id),'')
/**
* Re-run this section several time until the number of rows affected does not change
*/
update activity set category =
(select top(1) t.[from] from #category_temp t
where t.id = activity.id
and activity.transaction_date
Going forward
I then created a stored procedure which runs every 24hrs and populates the category field so the data will be current going forward.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Aaron Fulton
-- aaron.fulton@vets.org.nz
-- Create date: 5 Feb 2009
--
-- This stored procedure adds in the missing member
-- category into the Activity table.
-- This stored procedure should be scheduled to run
-- on a daily basis.
-- =============================================
CREATE PROCEDURE sp_update_activity_category
AS
BEGIN
UPDATE [activity] SET category = (select category from [name] where [name].id = [activity].id)
WHERE [activity].category = ''
AND [activity].transaction_date > dateadd(day, -1, getdate())
END
GO