February 8, 2020 at 12:14 am
Hello All,
First of all, I would like to thank you for going through my topic. So the question is I need to compose the message and send it to the recipients based on the Notification Type (Phone, and Email). For that, I have written the below query which basically composes the part of the message based in the Test Details Column. Here is the query
SELECT alertQueue.[Prod No] AS ProdNumber
, SUBSTRING(alertQueue.[Prod No], PATINDEX('%[^0]%', alertQueue.[Prod No] +'.'), LEN(alertQueue.[Prod No])) AS ProducerNumber
, SUBSTRING(alertQueue.[Tank No], PATINDEX('%[^0]%', alertQueue.[Tank No] +'.'), LEN(alertQueue.[Tank No])) AS TankNo
, alertQueue.[Sequence_Number] AS SeqNumber
, alertQueue.[Notification_Account] AS NotificationAccount
, CASEWHEN ([LIBECIRTP].[API].[fn_TestAlerts_AbbreviatedCheck](alertQueue.[Notification_Account]) = 0)
THEN 'LongMessage'
WHEN ([LIBECIRTP].[API].[fn_TestAlerts_AbbreviatedCheck](alertQueue.[Notification_Account]) = 1)
THEN 'ShortMessage'
ELSE 'Email'
END AS NotificationMessageType
, alertQueue.[Login] AS [Login]
, alertQueue.[Manifest_Number] AS ManifestNumber
, prod.[Prod Div] AS Division
, prod.[Prod Name] AS Name
, alertQueue.[Notification_Type] AS NotificationType
, alertQueue.[Pickup_Date] AS PickupDate
, STUFF((SELECT CASE alertQueue.[Notification_Type]
WHEN 'Phone'
THEN CAST(CONCAT(' <br/> ', nestedAlertQueue.[Test_Name], ': ', nestedAlertQueue.[Test_Value]) AS varchar(MAX))
WHEN 'Email'
THEN CAST(CONCAT(' <br/> '
, nestedAlertQueue.[Test_Name]
, ' is '
, nestedAlertQueue.[Test_Value]
, CASE nestedAlertQueue.[Test_Name]
WHEN 'BF'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'LPC'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'MUN'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'PIC'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'PRO'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'SCC'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'COLI' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'FFA'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'FRZP' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'INH'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'ROPY' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'SED'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'SPC'THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
WHEN 'TEMP' THEN CONCAT(' ( ', nestedAlertQueue.[Threshold], ' ', nestedAlertQueue.[Threshold_Value], ' )')
ELSE ''
END) AS VARCHAR(MAX))
ELSE ''
END
FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS nestedAlertQueue
INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS nestedProducer
ON nestedProducer.[Prod No KEY] = nestedAlertQueue.[Prod No]
WHERE nestedAlertQueue.[Prod No] = alertQueue.[Prod No]
AND nestedAlertQueue.[Tank No] = alertQueue.[Tank No]
AND nestedAlertQueue.[Sequence_Number] = alertQueue.[Sequence_Number]
AND nestedAlertQueue.[Notification_Account] = alertQueue.[Notification_Account]
AND nestedAlertQueue.[Login] = alertQueue.[Login]
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)') ,1,2,'') AS TestDetails
FROM [LIBECIRTP].[API].[Test_Alert_Queue] (NOLOCK) AS alertQueue
INNER JOIN [LIBECIRTP].[report].[Producer] (NOLOCK) AS prod
ON prod.[Prod No KEY] = alertQueue.[Prod No]
WHERE [LIBECIRTP].[API].[fn_TestAlerts_AbbreviatedCheck](alertQueue.[Notification_Account]) = 1
GROUP BY alertQueue.[Prod No]
, alertQueue.[Tank No]
, alertQueue.[Sequence_Number]
, alertQueue.[Notification_Account]
, alertQueue.[Login]
, alertQueue.[Manifest_Number]
, prod.[Prod Div]
, prod.[Prod Name]
, alertQueue.[Notification_Type]
, alertQueue.[Pickup_Date]
ORDER BY alertQueue.[Prod No]
, alertQueue.[Notification_Account]
, alertQueue.[Login] ASC
So the above query will compose part of the message. But now that based on the requirement changes I need to compose all the messages and send all the Emails together based on NotificationType Column. Phone Messages are working good but for the Email messages, I need to send all the messaged in one Email. Like if you see the sample message all messages are composed and sent once to the Recipients Email. If you see the Sample Data it is how I am getting data back now using the above query.
So, I need to send these 415 rows of the data in one row and send it back to recipients. However, this change is for only Email message type Phone works perfectly fine.
Any help is really appreciated. Once again thanks for reading my post and have a great weekend ahead!
February 9, 2020 at 1:10 am
Thanks for posting your issue and hopefully someone will answer soon.
This is an automated bump to increase visibility of your question.
February 10, 2020 at 4:47 pm
A picture of your data is worthless.
{;} Insert/edit code sample that does the following.Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
February 10, 2020 at 5:34 pm
Thanks for letting me know. I will update the post with scripts as per your suggestions.
February 14, 2020 at 11:34 pm
If I'm understanding correctly, you've done all the heavy lifting at this point. Dump that data in a temp table and then build a string with <br/> and send that in an email. For example:
DROP TABLE IF EXISTS #Demo
CREATE TABLE #Demo
(Dairy VARCHAR(100),
Div INT,
Mbr INT,
PickUpDate DATE,
TicketNumber INT,
Tank INT,
PUID INT,
COLI INT,
LPC INT,
PIC INT,
SPC INT)
INSERT INTO #Demo VALUES
('AUTUMN VISTA DAIRY LLC', 246, 3011036, '1/30/2020', NULL, 4, 2765, 28, 140, 3, 3),
('AUTUMN VISTA DAIRY LLC', 246, 3028238, '1/29/2020', 54753668, 1, 950, NULL, 30, 3, 2),
('AUTUMN VISTA DAIRY LLC', 246, 3028238, '1/30/2020', NULL, 1, 941, NULL, 10, 3, 2),
('AUTUMN VISTA DAIRY LLC', 246, 3028238, '1/30/2020', NULL, 1, 953, NULL, 30, 4, 2)
-- Review what we have
SELECT *
FROM #Demo
DECLARE @Email VARCHAR(MAX) = ''
SELECT @Email += Dairy + '<br/>'
+ 'Div: ' + CONVERT(VARCHAR, Div) + ' Mbr: ' + CONVERT(VARCHAR, Mbr) + ' PU Dt: ' + CONVERT(VARCHAR, PickupDate, 101) + '<br/>'
+ 'Ticket#: ' + ISNULL(CONVERT(VARCHAR, TicketNumber), '') + ' Tank: ' + CONVERT(VARCHAR, Tank) + '<br/>'
+ CASE WHEN COLI IS NOT NULL THEN 'COLI is ' + CONVERT(VARCHAR, COLI) + '<br/>' ELSE '' END
+ CASE WHEN LPC IS NOT NULL THEN 'LPC is ' + CONVERT(VARCHAR, LPC) + '<br/>' ELSE '' END
+ CASE WHEN PIC IS NOT NULL THEN 'PIC is ' + CONVERT(VARCHAR, PIC) + '<br/>' ELSE '' END
+ CASE WHEN SPC IS NOT NULL THEN 'SPC is ' + CONVERT(VARCHAR, SPC) + '<br/>' ELSE '' END
+ '<br/>'
FROM #Demo
EXEC msdb.dbo.sp_send_dbmail
@recipients = 'youremail@yourdomain.com',
@subject = 'Test email',
@body = @Email,
@body_format = 'HTML'
That ends up looking like the attached. This is just a jumping off point for you. You could format the font better, maybe use <table> tag, etc. You get the idea. Hope this is what you were after. Good luck!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply