Stored procedure invokes SQL mail intermittently

Question

I have a stored procedure that looks at a table for new entries with particular criteria and invokes SQL mail if found. A job executes the sp every 15 minutes. Sometimes it emails the new entries, but other times the SQL job runs successfully, but no email is generated--despite the fact that I run queries in SSMS and find new entries in the table during that 15 minute range. I have run the procedure manually after adjusting/widening the time parameters and it successfully generates the email that was not originally sent when the scheduled job ran, but reported success running.

I use similar code for notifications from other tables and they appear to be generating emails consistently, but another sp looking at the same table for different criteria also is intermittent with generating emails.

I have tweaked my code by moving all my arguments into the WHERE statement instead of using GROUP BY and HAVING for some of them. I have also shifted the time range looked at back 15 more mins (<=31 and >=15) or removed the minimum time argument in one case with no resolutions yet.

I'm not sure if the problem is in my code, the job executing the sp, SQL mail, or the table I'm querying (the date column I'm using is datetime datatype), SQL version, patch level, etc. Any ideas where to start looking why it invokes mail sometimes and not others?

ANSI_NULLS and QUOTED_IDENTIFIER are ON

BEGIN
set nocount on
if EXISTS
(select v.vst_ext_id
 from ptdata pd (nolock)
      join fndmst fnd (nolock) on pd.find_code = fnd.find_code
      join VISIT v (nolock) on pd.vst_int_id = v.vst_int_id
 where grp_code in ('BRST500')
     and category_code in (select CAT_CODE from COLUMN_LIST where CAT_CODE like 'REST%')
     and v.dschrg_ts is null
     and DATEDIFF(MINUTE,pd.entered_date,getdate()) <= 16
     and DATEDIFF(MINUTE,pd.entered_date,getdate()) >= 0  
)
begin
execute msdb.dbo.sp_send_dbmail
@profile_name = 'profile',
@query_result_header = 0,
@recipients = '[email protected]',
@subject = 'Charting Alert',
@body = 'New Charting for ',
@execute_query_database = 'database',
@query ='set nocount on
(select v.vst_ext_id
    + SPACE(5)
    + "Location: "
    + bd.loc_ds 
    + SPACE(5)
    + "Entered for Date:  "
    + convert(varchar(10),entered_for_date, 101) + " " + CONVERT(varchar(5),entered_for_date, 108)
    + SPACE(5)
    + "Charted: "
    + fnd.find_description        
from t_ptdata pd (nolock)
      join t_fndmst fnd (nolock) on pd.find_code = fnd.find_code
      join TPM300_PAT_VISIT v (nolock) on pd.vst_int_id = v.vst_int_id
      join TSM950_LOCATION_REF bd (nolock) on v.loc_lvl_5_id = bd.loc_int_id

where grp_code in ("BRST500")
     and category_code in (select CAT_CODE from t_COLUMN_LIST where CAT_CODE like "REST%")
     and v.dschrg_ts is null
     and DATEDIFF(MINUTE,pd.entered_date,getdate()) <= 16
     and DATEDIFF(MINUTE,pd.entered_date,getdate()) >= 0  )'

END
END
GO

Here is an example that seems to consistently generate the emails (actually within the same stored procedure) when new records are added:

--================================
--Begin Orders
--================================
BEGIN

set nocount on


if EXISTS

(select v.vst_ext_id 
from ORDER o (nolock)
     join ORDER_DETAIL od (nolock) on o.ord_int_id = od.ord_int_id
     left join ORDER_CODE oc (nolock) on od.order_code_int_id = oc.order_code_int_id
     left join VISIT v (nolock) on o.vst_int_id = v.vst_int_id
     join LOCATION_REF bd (nolock) on v.loc_lvl_5_id = bd.loc_int_id

where od.order_code_int_id in (select order_code_int_id from ORDER_CODE (nolock) where upper(order_code_desc1) like '%RESTRA%') 
      and  v.dschrg_ts is null 
      and DATEDIFF(MINUTE,o.entered_datetime_ts,getdate()) <= 16
      and DATEDIFF(MINUTE,o.entered_datetime_ts,getdate()) >= 0   
   )
begin
execute msdb.dbo.sp_send_dbmail
@profile_name = 'profile',
@query_result_header = 0,
@recipients = '[email protected]',
@subject = 'Order Alert',
@body = 'New Order for ',
@execute_query_database = 'database',
@query ='set nocount on
(select v.vst_ext_id
    + SPACE(5)
    + "Location: "
    + bd.loc_ds 
    + SPACE(5)
    + "Ordered for Date:  "
    + convert(varchar(10),o.start_datetime_ts, 101) + " " + CONVERT(varchar(5),o.start_datetime_ts, 108)
    + SPACE(5)
    + "Order: "
    + oc.order_code_desc1  
from ORDER o (nolock)
     join ORDER_DETAIL od (nolock) on o.ord_int_id = od.ord_int_id
     left join ORDER_CODE oc (nolock) on od.order_code_int_id = oc.order_code_int_id
     left join VISIT v (nolock) on o.vst_int_id = v.vst_int_id
     join LOCATION bd (nolock) on v.loc_lvl_5_id = bd.loc_int_id

where od.order_code_int_id in (select order_code_int_id from ORDER_CODE (nolock) where upper(order_code_desc1) like "%RESTRA%") 
      and  v.dschrg_ts is null 
      and DATEDIFF(MINUTE,o.entered_datetime_ts,getdate()) <= 16
      and DATEDIFF(MINUTE,o.entered_datetime_ts,getdate()) >= 0   
   )'
END
END
GO

Here is code from another one that is also intermittent with generating emails, but uses the same main table--without all the joins:

Begin
set nocount on
if EXISTS
(select visit_id 
 from ptdata
 where grp_code = 'NUSC1' and find_code = 'K_TOTAL'
   and
    DATEDIFF(MINUTE,entered_date,GETDATE()) < 16
    and value > 1
)
begin
execute msdb.dbo.sp_send_dbmail
@profile_name = 'profile',
@query_result_header = 0,
@recipients = '[email protected]',
@subject = 'Screen Score',
@body = 'Screening Score above 1 for ',
@execute_query_database = 'database',
@query ='set nocount on
(select rtrim(visit_id) + space(5) + "Value: " + rtrim(value) 
 from ptdata
 where grp_code = "NUSC1" and find_code = "K_TOTAL"
    and
    DATEDIFF(MINUTE,entered_date,getdate()) < 16

    and value > 1
 )'
END
END
GO