I would like to build something as described below.
An Insurancetable which has InsurerName, PhoneNumber, InsureAmount and InsureRenewalDateTime
I would like to send reminder text messages to Insurer phones everytime they are due on their Insurance at EXACTLY the datetime they are due.
What is the best possible way to achieve this from the database itself.
Ex:
Insurer A 1111111111 InsureAmount 2020-11-20 06:30:00
Insurer B 1111111112 InsureAmount 2020-11-21 07:30:00
Insurer C 1111111113 InsureAmount 2020-11-22 08:30:00
Insurer A 1111111111 InsureAmount 2020-11-23 09:30:00
Insurer A 1111111111 InsureAmount 2020-11-24 10:30:00
Insurer D 1111111111 InsureAmount 2021-01-01 15:30:00
For Insurer A Send message to their registered PHONES on 2020-11-20 at 06:30:00hrs -Message --> Insurer A, its time renew your insurance - Pay [InsureAmount].
What are my options to setup something like this from within the database itself.
How do I read my InsuranceTable every minute to check the datetime column ? Run a SQL job every minute or are there any other efficient ways to achieve this. Please advise.
November 18, 2020 at 1:23 pm
There isn't a way within the database alone to do this. You're going to have to go external to the database and set up something that polls the data. Now, yes, you could call it once a minute, sure. Using an application, scheduling tool, SQL Agent, some code, somewhere, to call the database over & over. However, that's going to be very chatty. So, what about, just calling the database once a day, gather all the latest information into a local store, then use your code to respond that way? After all, it's unlikely that new expirations will occur outside of the schedule, right? So instead of hammering the db, just deal with it in code.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2020 at 3:47 pm
@Grant ,
Yes, that was my intention take a chunk of everyday data rows and execute some sql code/agaent job against the table.I am sure the datatable per day will be pretty small so should not much of an issue.
I have zero application knowledge when you said polling ...
When you say external to the DB and have some application poll the datatabase/table, it still is pretty much similar to have the sqlagent running every minute and I mean this in terms of process .Excepting that it is some outside application instead of the sqljob running/polling the relevant sqltable..isn't it or not ?
November 18, 2020 at 4:43 pm
You could run the job every minute. But I'd say run the job every 10 minutes (or, I guess, 5 minutes). Would it really be that big a deal to normalize the time they enter to round to a ten-minute (or five-minute) interval?
DECLARE cursor_renewals CURSOR LOCAL FAST_FORWARD FOR
SELECT ...columns...
FROM dbo.Insurancetable it
WHERE it.InsureRenewalDateTime = DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) / 10 * 10, 0)
OPEN cursor_renewals
WHILE 1 = 1
BEGIN
FETCH NEXT FROM cursor_renewals INTO @...
IF @@FETCH_STATUS <> 0
BREAK;
--send text message here--
END /*WHILE*/
CLOSE cursor_renewals
DEALLOCATE cursor_renewals
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 18, 2020 at 5:00 pm
Similar to what Grant mentioned, I'd suggest creating a table to help manage the process. Run a process every day to update the table with the next day's planned messages – this sort of thing:
CREATE TABLE RenewalQueue(SendAt DATETIME, PhoneNumber VARCHAR(50), Msg VARCHAR(500), SendComplete BIT)
Then, when your 'send' job executes, it queries the RenewalQueue table, where SendAt <= Getdate() AND SendComplete = 0 & sends messages for any rows returned, before updating SendComplete to 1 for them.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2020 at 6:30 pm
@Grant ,
Yes, that was my intention take a chunk of everyday data rows and execute some sql code/agaent job against the table.I am sure the datatable per day will be pretty small so should not much of an issue.
I have zero application knowledge when you said polling ...
When you say external to the DB and have some application poll the datatabase/table, it still is pretty much similar to have the sqlagent running every minute and I mean this in terms of process .Excepting that it is some outside application instead of the sqljob running/polling the relevant sqltable..isn't it or not ?
Yes. Agent is external to the database. Code would be required to make the stuff you want happen.
By polling I mean constantly checking the database for status. It really shouldn't need that much.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
November 18, 2020 at 7:33 pm
...send reminder text messages...
You need to figure out a couple of things before you go off half-cocked doing something like that. People need to "opt-in" and be informed that it can cost them $ to receive such messages and all the management and legal stuff that goes along with that. I also think that polling on a per-minute basis is a complete waste of development and computer time to do notifications "at EXACTLY the datetime they are due". If mine is due at 3AM and you text me, the "beep" will wake me up because I'm required to "listen" for text messages by my job and I'll be looking for someway to make the company you work for suffer in return. 😉
Like the Grannies used to say on the old Dunkin'Donuts commercials, "See you in court, sonny!"
--Jeff Moden
Change is inevitable... Change for the better is not.
November 18, 2020 at 9:15 pm
This does not sound right to me. Usually, renewal/expiry systems like this require a complete date, not down to the microsecond. But that's not what you described. This is just weird to me. Next, sending out expiry notices and such are usually done multiple times n–days in advance of the expiry date ("your gambling debt comes due on 2020-12-22, and if you fail to pay by then, then we will break both your legs").
This is not a database problem, but an application problem. These table alerts should probably be done in the application tier of your system, or other rules can be applied. For example, members student loan system where certain loans were forgiven. If the student had agreed to serve in the military, teach in an underserved area in Appalachia, etc.
Also, in the future. Would you please follow the formal rules and post actual DDL instead of vague general narrative descriptions? If you're working in a place that requires you to program from vague narrative general descriptions. It is time to update your resume.
Please post DDL and follow ANSI/ISO standards when asking for help.
November 18, 2020 at 9:48 pm
Similar to what Grant mentioned, I'd suggest creating a table to help manage the process. Run a process every day to update the table with the next day's planned messages – this sort of thing:
CREATE TABLE RenewalQueue(SendAt DATETIME, PhoneNumber VARCHAR(50), Msg VARCHAR(500), SendComplete BIT)
Then, when your 'send' job executes, it queries the RenewalQueue table, where SendAt <= Getdate() AND SendComplete = 0 & sends messages for any rows returned, before updating SendComplete to 1 for them.
But then you need a trigger to re-run the process when a row gets DELETEd OR INSERTed OR UPDATEd (if the update effects the notification details). Ugh.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 18, 2020 at 10:24 pm
Phil Parkin wrote:Similar to what Grant mentioned, I'd suggest creating a table to help manage the process. Run a process every day to update the table with the next day's planned messages – this sort of thing:
CREATE TABLE RenewalQueue(SendAt DATETIME, PhoneNumber VARCHAR(50), Msg VARCHAR(500), SendComplete BIT)
Then, when your 'send' job executes, it queries the RenewalQueue table, where SendAt <= Getdate() AND SendComplete = 0 & sends messages for any rows returned, before updating SendComplete to 1 for them.
But then you need a trigger to re-run the process when a row gets DELETEd OR INSERTed OR UPDATEd (if the update effects the notification details). Ugh.
I'm in the insurance business. A daily update of the table is likely to be sufficient, because renewal reminders are unlikely to be dependent on any other transactions occurring that specific day.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2020 at 10:33 pm
ScottPletcher wrote:Phil Parkin wrote:Similar to what Grant mentioned, I'd suggest creating a table to help manage the process. Run a process every day to update the table with the next day's planned messages – this sort of thing:
CREATE TABLE RenewalQueue(SendAt DATETIME, PhoneNumber VARCHAR(50), Msg VARCHAR(500), SendComplete BIT)
Then, when your 'send' job executes, it queries the RenewalQueue table, where SendAt <= Getdate() AND SendComplete = 0 & sends messages for any rows returned, before updating SendComplete to 1 for them.
But then you need a trigger to re-run the process when a row gets DELETEd OR INSERTed OR UPDATEd (if the update effects the notification details). Ugh.
I'm in the insurance business. A daily update of the table is likely to be sufficient, because renewal reminders are unlikely to be dependent on any other transactions occurring that specific day.
"Unlikely" is not good enough for business rules. IF you're supposed to send a reminder, you send a reminder, even if it was added today. That's the rule of any business I've ever worked for. You can't ignore something just because it's "unlikely".
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 18, 2020 at 10:40 pm
Phil Parkin wrote:ScottPletcher wrote:Phil Parkin wrote:Similar to what Grant mentioned, I'd suggest creating a table to help manage the process. Run a process every day to update the table with the next day's planned messages – this sort of thing:
CREATE TABLE RenewalQueue(SendAt DATETIME, PhoneNumber VARCHAR(50), Msg VARCHAR(500), SendComplete BIT)
Then, when your 'send' job executes, it queries the RenewalQueue table, where SendAt <= Getdate() AND SendComplete = 0 & sends messages for any rows returned, before updating SendComplete to 1 for them.
But then you need a trigger to re-run the process when a row gets DELETEd OR INSERTed OR UPDATEd (if the update effects the notification details). Ugh.
I'm in the insurance business. A daily update of the table is likely to be sufficient, because renewal reminders are unlikely to be dependent on any other transactions occurring that specific day.
"Unlikely" is not good enough for business rules. IF you're supposed to send a reminder, you send a reminder, even if it was added today. That's the rule of any business I've ever worked for. You can't ignore something just because it's "unlikely".
OK, give me an example of an insurance policy for which you need a renewal reminder on the day it's purchased.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 18, 2020 at 11:38 pm
ScottPletcher wrote:Phil Parkin wrote:ScottPletcher wrote:Phil Parkin wrote:Similar to what Grant mentioned, I'd suggest creating a table to help manage the process. Run a process every day to update the table with the next day's planned messages – this sort of thing:
CREATE TABLE RenewalQueue(SendAt DATETIME, PhoneNumber VARCHAR(50), Msg VARCHAR(500), SendComplete BIT)
Then, when your 'send' job executes, it queries the RenewalQueue table, where SendAt <= Getdate() AND SendComplete = 0 & sends messages for any rows returned, before updating SendComplete to 1 for them.
But then you need a trigger to re-run the process when a row gets DELETEd OR INSERTed OR UPDATEd (if the update effects the notification details). Ugh.
I'm in the insurance business. A daily update of the table is likely to be sufficient, because renewal reminders are unlikely to be dependent on any other transactions occurring that specific day.
"Unlikely" is not good enough for business rules. IF you're supposed to send a reminder, you send a reminder, even if it was added today. That's the rule of any business I've ever worked for. You can't ignore something just because it's "unlikely".
OK, give me an example of an insurance policy for which you need a renewal reminder on the day it's purchased.
That overall is what the OP stated they needed. [I'm not going to presume to tell the OP what their system requires. You can do that if you want.]
Once you agree to do the notifications, then you don't deliberately design something with bugs. If you're going to build it, build it correctly from the start.
SQL DBA,SQL Server MVP(07, 08, 09) "It's a dog-eat-dog world, and I'm wearing Milk-Bone underwear." "Norm", on "Cheers". Also from "Cheers", from "Carla": "You need to know 3 things about Tortelli men: Tortelli men draw women like flies; Tortelli men treat women like flies; Tortelli men's brains are in their flies".
November 19, 2020 at 7:36 am
Yes, I agree.5 to 10 minute intervals should be fine. Thanks
November 19, 2020 at 7:38 am
Thanks, will take that into consideration.
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply