Home Loop through table with multiple rows and send one email
Reply: 0

Loop through table with multiple rows and send one email

user3617
1#
user3617 Published in July 18, 2018, 10:30 pm

As a noob, I am really battling with this.

I have a table as follows

SELECT [AuditFieldID]
      ,[CompanyRuleID]
      ,[CompanyRule]
      ,[VipUserName]
      ,[EffectiveDate]
      ,[FieldName]
      ,[SourceCode]
      ,[Action]
      ,[AccountNoOldValue]
      ,[AccountNoNewValue]
      ,[AccountTypeOldValue]
      ,[AccountTypeNewValue]
      ,[BankOldValue]
      ,[BankNewValue]
      ,[BranchOldValue]
      ,[BranchNewValue]
      ,[AccountHolderOldValue]
      ,[AccountHolderNewValue]
  FROM [SageStaging].[MASSMART].[AuditCondensed]

There are 5 rows in the table for each employee. The first 5 fields contain the same data, and then the fieldname field, contains a different record type.

So you will have

Auditfieldid = 111111
CompanyRuleID = 12
CompanyRule = Walmart
VipUsername = john.doe
EffectiveDate = date()
Fieldname = 'Account Holder Name'
SourceCode = 1234 - John Doe
Action = I
AccountNoOldValue = NULL
AccountNoNewValue = NULL
AccountTypeOldValue = NULL
AccountTypeNewValue = NULL
BankOldValue = NULL
BankNewValue = NULL
BranchOldValue = NULL
BranchNewValue = NULL
AccountHoldOldValue = ''
AcccountHolderNewValue = 'John Doe'

There are five field name types:

FieldName = 'Account Holder Name'
FieldName = 'Account Number'
FieldName = 'Account Type'
FieldName = 'Bank'
FieldName = 'Bank Branch'

If the FieldName is = 'Account Holder' the record will have values in AccountHoldOldValue and AccountHoldNewValue

If FieldName is = 'Account Number' the record will have values in AccountNoOldValue and AccountNoNewValue

and so forth. So all in all you have 5 records of different fieldname types and in the row the appropriate value field populated according to the fieldname type.

I need to send an email with these values consolidated. So one email creating the following:

SET @MailSubject = 'Banking Details Change Notification for Employee' + ' ' + @SOURCECODE
SET @MessageBody = '<html>
<head>
  <meta content="text/html; charset=ISO-8859-1"
 http-equiv="content-type">
  <title></title>
</head>
<body>
<br>
The following bank details have been changed: 
<br>
<br>

 Date Changed:    ' + @EFFECTIVEDATE + '<br>' +
' Company:    ' + @COMPANYRULE + '<br>' +
' Username:    ' + @VIPUSERNAME + '<br>' +
' Employee Details:    ' + @SOURCECODE + '<br>' +
' Action:    ' + @ACTION + '<br>' +
' Account Holder:  ' + ' Old Value:    ' + @ACCOUNTHOLDEROLDVALUE + ' New Value:    ' + @ACCOUNTHOLDERNEWVALUE + '<br>' +
' Account Number:  ' + ' Old Value:    ' + @ACCOUNTNOOLDVALUE + ' New Value:    ' + @ACCOUNTNONEWVALUE + '<br>' +
' Account Type:  ' + ' Old Value:    ' + @ACCOUNTTYPEOLDVALUE + ' New Value:    ' + @ACCOUNTTYPENEWVALUE + '<br>' +
' Bank:  ' + ' Old Value:    ' + @BANKOLDVALUE + ' New Value:    ' + @BANKNEWVALUE + '<br>' +
' Bank Branch:  ' + ' Old Value:    ' + @BRANCHOLDVALUE + ' New Value:    ' + @BRANCHNEWVALUE + '<br>' +
'<br>
<br>
<b>
Please do not respond to this email. If you have any questions regarding this email, please
contact your payroll administrator <br>
<br>
<br>
</body>'

I cannot seem to be able to figure out how to send just one email with all the necessary fields consolidated.

I seem to get five emails with blanks in all the fields, just the first four fields of the record gets populated.

You need to login account before you can post.

About| Privacy statement| Terms of Service| Advertising| Contact us| Help| Sitemap|
Processed in 0.31704 second(s) , Gzip On .

© 2016 Powered by mzan.com design MATCHINFO