Hi All,

I'm trying to assign a "reliability of information" value to my contacts table. The stored procedure below selects the values I want. How do I apply these values to the contacts table using an "Update". The field, cContactID, is the primary key. iRely is the field I want to update.

TIA!

Bill

CREATE PROCEDURE sp_ageContacts AS
declare @d1Year datetime, @d2Years datetime

select @d1Year = getdate() - 365
select @d2Years = getdate() - 365 * 2

select 'Reliablity' =
case
when dUpdated >= @d1Year or max(actions.dDate) >= @d1Year then 1
when dUpdated >= @d2Years and max(actions.dDate) >= @d2Years then 2
when dUpdated >= @d2Years and (max(actions.dDate)) IS NULL then 3
when dUpdated < @d2Years and (max(actions.dDate)) >= @d2Years then 3
when dUpdated < @d2Years and max(actions.dDate) < @d2Years then 4
when dUpdated < @d2Years and (max(actions.dDate)) IS NULL then 5
when dUpdated is NULL and (max(actions.dDate)) IS NULL then 5
else NULL
end
from contacts left join relts on cContactID = relts.cSecID
left join actions on relts.cPriID = actions.cActionID
group by contacts.cContactID

/* end of procedure */