Group by the messages based on the Column and retrieve the records

Question

Post reply

Group by the messages based on the Column and retrieve the records

chowdarydeepus@gmail.com

Old Hand

Points: 338

February 8, 2020 at 12:14 am

#3724113

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!

This topic was modified 4 years, 2 months ago by chowdarydeepus@gmail.com.

Attachments:

You must be logged in to view attached files.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Site Owners SSC Guru Points: 80320 More actions · Answer 1

Thanks for posting your issue and hopefully someone will answer soon.

This is an automated bump to increase visibility of your question.

drew.allen SSC Guru Points: 76944 More actions · Answer 2

A picture of your data is worthless.

You need to provide a script using the {;} Insert/edit code sample that does the following.
1. Creates a temp table or declares a table variable for the sample data.
2. Inserts sample data into said table.
3. Creates a temp table or declares a table variable for the expected results.
4. Inserts the expected results data into said table.
DO NOT UNDER ANY CIRCUMSTANCES PROVIDE THE DATA IN AN ATTACHED FILE.
1. People are hesitant to open files posted by random strangers on the Internet.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

chowdarydeepus@gmail.com Old Hand Points: 338 More actions · Answer 3

Thanks for letting me know. I will update the post with scripts as per your suggestions.

SoCal_DBD Hall of Fame Points: 3370 More actions · Answer 4

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!

Attachments:

You must be logged in to view attached files.