November 19, 2020 at 8:11 pm
CREATE TABLE [dbo].[DND](
[IN] [varchar](50) NULL,
[P] [bigint] NULL,
[N] varchar(100),
[A] [varchar](100) NULL,
[D] [datetime] NULL
) ON [PRIMARY]
GO
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('A',1111111111,'txt.att.net','MA','2020-11-24 13:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('B',2222222222,'messaging.sprintpcs.com','MB','2020-11-25 14:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('C',3333333333,'tmomail.net','MC','2020-11-26 15:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('C',3333333333,'tmomail.net','ADSS','2020-11-26 16:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('D',4444444444,'vtext.com','MD','2020-11-27 16:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('D',4444444444,'vtext.com','AB','2020-11-27 17:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('D',4444444444,'vtext.com','MD','2020-11-27 18:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('B',2222222222,'messaging.sprintpcs.com','ANM','2020-11-26 14:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('A',1111111111,'txt.att.net','MA','2020-11-24 06:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('B',2222222222,'messaging.sprintpcs.com','MB','2020-11-25 06:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('C',3333333333,'tmomail.net','MC','2020-11-26 07:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('C',3333333333,'tmomail.net','ADSS','2020-11-26 08:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('D',4444444444,'vtext.com','MD','2020-11-27 09:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('D',4444444444,'vtext.com','AB','2020-11-27 10:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('D',4444444444,'vtext.com','MD','2020-11-27 11:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('B',2222222222,'messaging.sprintpcs.com','ANM','2020-11-26 12:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('B',2222222222,'messaging.sprintpcs.com','ANM','2020-11-27 12:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('A',1111111111,'txt.att.net','ANM','2020-11-26 12:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('B',2222222222,'messaging.sprintpcs.com','ANM','2020-11-27 15:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('A',1111111111,'txt.att.net','ANM','2020-11-27 13:00:00.000')
insert into [DND].[dbo].[DND]([IN],P,N,A,D) values ('C',3333333333,'tmomail.net','ANM','2020-11-27 15:45:00.000')
If I need to process data for 27th then on 27th my job executes for every 15 minutes and should only select those rows which fall in the next 15 minutes
D should getting 6 reminder text messages at - 4 PM 5 PM,6 PM,9 AM,10 AM ,11 AM respectively
B should be getting 2 reminders text messages - 12 PM and 1 PM
A should be getting 1 reminder text message - 1PM
C should be getting 1 reminder text message - 3:45 PM
CREATE PROCEDURE dbo.usp_send_emails
AS
DECLARE
@IN nvarchar(100),
@P bigint,
@N varchar(100),
@A varchar(100),
@D datetime,
@body varchar(1000);
DECLARE Messaging CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT [IN],[P],N,A,D FROM DND.[dbo].[DND]
where D = should pick rows which fall in next 15 minutes;
OPEN Messaging;
WHILE 1=1
BEGIN
FETCH NEXT FROM Messaging INTO @IN,@P,@N,@A;
IF @@FETCH_STATUS = -1 BREAK;
SET @body = @IN + 'Your amount is: ' + @A + 'payable on' + @D
EXEC msdb.dbo.sp_send_dbmail
@recipients = "@P+@N",
@subject = "Renew",
@body = @body
END
CLOSE Messaging;
DEALLOCATE Messaging;
RETURN;
GO
Not sure how to fix the above code to send messages as mentioned described above.
November 19, 2020 at 9:12 pm
WHERE
D >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) / 15 * 15, 0) AND
D < DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) / 15 * 15 + 15, 0)
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 20, 2020 at 2:52 am
WHERE
D >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) / 15 * 15, 0) AND
D < DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) / 15 * 15 + 15, 0)
Ya just gotta love the blessing of integer math!
--Jeff Moden
Change is inevitable... Change for the better is not.
November 20, 2020 at 3:13 am
Thanks @scott
I was using dateadd but not the way you built it and hence did not work.
And how do I get the message to the right person
EXEC msdb.dbo.sp_send_dbmail
@recipients = "@P+@N", <-------- How do I concatenate here ? The Ph No and the Network
@subject = "Renew",
@body = @body
November 20, 2020 at 3:48 pm
Wouldn't you want just the network? The recipients are email addresses, not phone numbers afaik.
@recipients = @N,
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 20, 2020 at 4:36 pm
@scottpletcher, @grant
Yes, it has to be an email address.
I will be needing it to be in this format as below.The respective network gateway domains will be sending text messages to the phone numbers as in the body as below.
Message will be sent to 1111111111 who is on verizon network as in the below example.
USE [msdb]
GO
EXEC msdb.dbo.sp_send_dbmail
@recipients = "11111111111@vtext.com",
@subject = "Test SMS",
@body = "This is only a test"
GO
The code should dynamically build this email address part (@recipients = "11111111111@vtext.com", )and when done a message will be sent to the phone number(1111111111) using the networks gateway domain. How do I achieve this.
Thanks
November 22, 2020 at 7:42 pm
My Date format: 2020-11-22 14:50:00.000
The query is not pulling the record which are within 15mins away from current time.
November 23, 2020 at 11:45 am
@scottpletcher @grantFitchey
I got the emails working .
I still cant get this to work:
WHERE
D >= DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) / 15 * 15, 0) AND
D < DATEADD(MINUTE, DATEDIFF(MINUTE, 0, GETDATE()) / 15 * 15 + 15, 0)
It is not pulling records which are 15 minutes away from current time.
datetime datatype: sample record: 2020-11-22 14:50:00.000
November 23, 2020 at 12:42 pm
The code given with the /15 and *15 integer math is to figure out the 15-minute time period that contains the current time. As I write this, it is 7:36, so it would return 7:30 to 7:45.
If you want the time from now until 15 minutes from now, you'd code
WHERE
D >= GETDATE() AND
D < DATEADD(MINUTE, 15, GETDATE())
But I still can't fathom why you want to send out notices just as their policies expire. What is the business case for that? What is the insured supposed to do at the exact time the policy expires? Maybe that's the requirements that you were given. But if the requirements don't make sense, you should go back and ask if that's what the business really wants.
November 23, 2020 at 2:39 pm
But I still can't fathom why you want to send out notices just as their policies expire. What is the business case for that? What is the insured supposed to do at the exact time the policy expires? Maybe that's the requirements that you were given. But if the requirements don't make sense, you should go back and ask if that's what the business really wants.
+ 1 million to that plus what I previously said about getting a text beep at 3 in the morning. You would think that you'd give a two week, one week, and 1 day heads up to anyone who has not paid as well as a "you are no longer covered" the day after. The idea of doing a text only during the 15 minute period when their policy expires seems absurd.
If this is a business requirement, then the business really needs to re-evaluate their policy and maybe even ask the customer what would be considered to be helpful reminders and communication provided that it's also what's within the law.
--Jeff Moden
Change is inevitable... Change for the better is not.
November 23, 2020 at 3:12 pm
Works .Thanks
November 23, 2020 at 5:43 pm
The code given with the /15 and *15 integer math is to figure out the 15-minute time period that contains the current time. As I write this, it is 7:36, so it would return 7:30 to 7:45.
If you want the time from now until 15 minutes from now, you'd code
WHERE
D >= GETDATE() AND
D < DATEADD(MINUTE, 15, GETDATE())But I still can't fathom why you want to send out notices just as their policies expire. What is the business case for that? What is the insured supposed to do at the exact time the policy expires? Maybe that's the requirements that you were given. But if the requirements don't make sense, you should go back and ask if that's what the business really wants.
Just be aware that you could miss time this way depending on when the jobs starts. That is, if a job to check gets delayed for some reason, then you have a gap of time that is not checked for notices.
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 23, 2020 at 6:02 pm
True. But given the business specs, it doesn't hardly seem important. The business should also consider the latency between the time the job runs and the time the text is actually received.
Almost no one would send in a payment between "now" and "15 minutes from now" if they haven't already done it.
November 23, 2020 at 6:37 pm
Agree. Testing this for some other use case.
Got it working.Please ignore.Thanks
I am getting a message in this format.
Subject : Test Mail
Test:RTatJan 1 1900 1:20PM
Why am I getting Jan 1 1900 and how can I get rid of it?
I only need Test:RTat 1:20PM
Thanks
Resolved the above issue
November 24, 2020 at 5:35 am
DECLARE @EN VARCHAR(50)
DECLARE @PN varchar(60)
DECLARE @EA varchar(50)
DECLARE @CE varchar(100)
DECLARE @DtT datetime
DECLARE @body varchar(150)
DECLARE MCursor CURSOR LOCAL FAST_FORWARD READ_ONLY FOR
SELECT PN,EA,PN+'@'+EA,CONVERT(VARCHAR(10), CAST(DtT AS TIME), 0)from TableName
OPEN MCursor;
WHILE 1=1
BEGIN
FETCH NEXT FROM MCursor INTO @EN,@PN,@EA,@CE,@DtT;
IF @@FETCH_STATUS = -1 BREAK;
SET @body = 'Test '+@EN+' at '+ CONVERT(VARCHAR(10), CAST(@DtT AS TIME), 0)
EXEC msdb.dbo.sp_send_dbmail
@recipients = @CE,
@subject = 'Test Mail',
@body = @body
END
CLOSE MCursor;
DEALLOCATE MCursor;
RETURN;
GO
When CE is PhoneNumber@txt.att.net(Any AT&T number..1234567890@txt.att.net) with DtT column values in this format - 2020-11-22 14:50:00.000.It does not display AM or PM.
I receive Test A at 2:50.But I need to receive Test A at 2:50 PM
How do I fix it.
Thanks
Viewing 15 posts - 16 through 30 (of 30 total)
You must be logged in to reply to this topic. Login to reply