USP_EMAIL_PROCESSCOUNTADD

create procedure dbo.USP_EMAIL_PROCESSCOUNTADD
(
  @PROCESSTYPE nvarchar(50),
  @THREADNAME nvarchar(50),
  @ISACTIVE bit,
  @JOBSPROCESSED integer = null
)
as
begin
    set nocount on;

  merge into dbo.EMAILPROCESSTRACKING as TARGET
  using (select @PROCESSTYPE, @THREADNAME, @ISACTIVE, @JOBSPROCESSED) as SOURCE(PROCESSTYPE, THREADNAME, ISACTIVE, JOBSPROCESSED)
  on (TARGET.PROCESSTYPE = SOURCE.PROCESSTYPE) and (TARGET.THREADNAME = SOURCE.THREADNAME)
  when matched then
    update set ISACTIVE = SOURCE.ISACTIVE, 
      THREADSTARTDATE = case SOURCE.ISACTIVE when 1 then getutcdate() else TARGET.THREADSTARTDATE end, 
      THREADSTOPDATE = case SOURCE.ISACTIVE when 0 then getutcdate() else null end, 
      CHUNKSPROCESSED = coalesce(SOURCE.JOBSPROCESSED, TARGET.CHUNKSPROCESSED, 0)
  when not matched then
    insert (PROCESSTYPE, THREADNAME, ISACTIVE, THREADSTARTDATE, THREADSTOPDATE, CHUNKSPROCESSED)
    values (SOURCE.PROCESSTYPE, SOURCE.THREADNAME, SOURCE.ISACTIVE, getutcdate(), null, 0);


end