USP_GET_INACTIVERECIPIENT_LIST

CREATE procedure dbo.USP_GET_INACTIVERECIPIENT_LIST
 @INACTIVERECIPIENTFROMMONTHS int=0
as
begin

declare @CURRENTDATE as date=getdate()

if @INACTIVERECIPIENTFROMMONTHS =0 
begin
  select @INACTIVERECIPIENTFROMMONTHS=value from setting where id=148
end

select EJR. EmailAddress, max(EMA.CONSTITUENTID) as constituentId,max(openedDate) as lastOpenedDate
from EmailJob_Recipient EJR
left join EMAILADDRESS EMA on EJR.EmailAddress=EMA.EMAILADDRESS 
where EJR.Sent=1 and OpenedDate is null
group by EJR.EmailAddress having Datediff(MONTH,max(SentDate),@CURRENTDATE)>@INACTIVERECIPIENTFROMMONTHS
union all
select EJR. EmailAddress, Max(EMA.CONSTITUENTID) as constituentId,max(openedDate) as lastOpenedDate
from EmailJob_Recipient EJR
left join EMAILADDRESS EMA on EJR.EmailAddress=EMA.EMAILADDRESS 
where EJR.Sent=1 and OpenedDate is not null
group by EJR.EmailAddress having datediff(MONTH,max(SentDate),max(openedDate))>@INACTIVERECIPIENTFROMMONTHS

end