Information générale
Forum:
Microsoft SQL Server
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 */
Suivant
Répondre
Voir le fil de ce thread
Voir le fil de ce thread à partir de ce message seulement
Voir tous les messages de ce thread
Voir tous les messages de ce thread à partir de ce message seulement