nu stiu cat e de corect dar am facut asa:
am facut un view care-mi intoarce exact ce am eu nevoie....cu conditia IF ( DATEDIFF(dd,@DataFinalizareContract,@GETDATE()) = 4 )
am facut view pt ca problema sa complicat pe parcurs si nu mai era vorba doar de o tabela cum am crezut eu initial.
care arata asa:
create view vw_ListaMail
as
SELECT pc.DataLivrarii,pc.DataEfectLivrare,su.InternalEmailAddress,vc.EUltimaVersiune,ncv.new_name
FROM dbo.PRODUSCONTRACT pc
INNER JOIN dbo.VERSIUNECONTRACT vc ON pc.VersiuneContractID = vc.VersiuneContractID
INNER JOIN dbo.CONTRACT c ON vc.ContractID = c.ContractID
INNER JOIN mscrm..New_ContractVanzari ncv on c.new_contractvanzariid = ncv.new_contractvanzariid
INNER JOIN mscrm..systemuser su on ncv.ownerid = su.systemuserid
WHERE pc.DataEfectLivrare is null
and datediff(day,pc.DataLivrarii,getdate()) = 4
and su.InternalEMailAddress is not null
and vc.EUltimaVersiune = 1
dupa care am facut procedura de trimitere mail (pe care o voi pune intr-un job sa se execute zilnic) astfel:
create proc spr_TrimiteMailAvertizare_ContractTEST
as
DECLARE @DataLivrarii datetime, @InternalEmailAddress nvarchar (100), @new_name nvarchar(100)
declare @Mesaj varchar (250)
DECLARE contract_cursor CURSOR FOR
SELECT DataLivrarii,InternalEmailAddress,new_name FROM vw_ListaMail
OPEN contract_cursor
FETCH NEXT FROM contract_cursor
INTO @DataLivrarii, @InternalEmailAddress,@new_name
WHILE @@FETCH_STATUS = 0
BEGIN
set @Mesaj = @new_name -- se modifica pe parcurs
exec _SMTPemail 'alex@net.ro', @InternalEmailAddress,'Important',@Mesaj
FETCH NEXT FROM contract_cursor
INTO @DataLivrarii, @InternalEmailAddress,@new_name
END
CLOSE contract_cursor
DEALLOCATE contract_cursor
GO
cum vi se pare.....mai trebuie adaugat ceva....stiu ca se poate si mai bine? :)
multumesc mult
alex.