+11
Hi all
My backoffice team have asked if there is a way to find all customers with active alerts. From what I can see, this is not visible except looking at the attachements on the customer. Any easy way to find this? Or will I need to create a SQL-script?
Best answer by Reid Gilbert
View original
+15
I’m not aware of a way to identify those all directly within the application but you can probably get that info quickly directly from the ap_attachments table at the database level with a script like the following (you may need to add a check for the ap_from_date just in case there are alerts with a future start date but this should get you close):
select distinct ap_table_key from ap_attachments with(NOLOCK) where ap_table_name = 'cust' and ap_is_alert = 'Y' and ap_to_date > Getdate();
+11
Excellent - thank you, Reid!
+21
Thanks, Reid….
Might I suggest that you also account for the possibility the ap_to_date is null or the row won’t pull up in the cases where no dates were defined within the alert definition.
select disinct ap_table_key from ap_attachments with(nolock) where ap_table_name = ‘cust’ and ap_is_alert = ‘Y’ and (ap_to_date > getdate() or ap_to_date is null)
+15
Quick note: Phil just pointed out that there could be alerts without any date values entered so be sure to account for those as well in your testing
+11
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.