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

Loop through table with multiple rows and send one email

Jeremy Reynolds
1#
Jeremy Reynolds Published in 2018-01-12 21:14:08Z

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.

Ron Ballard
2#
Ron Ballard Reply to 2018-01-12 22:23:52Z

This query should give you one record for each email.

select
    a.Auditfieldid,
    a.CompanyRuleID,
    a.CompanyRule,
    a.VipUsername,
    a.EffectiveDate,
    a.AccountHolderOldValue,
    a.AccountHolderNewValue,
    b.AccountNoOldValue,
    b.AccountNoNewValue,
    c.AccountTypeOldValue,
    c.AccountTypeNewValue,
    d.BankOldValue,
    d.BankNewValue,
    e.BranchOldValue,
    e.BranchNewValue
from
    (
        select
            Auditfieldid,
            CompanyRuleID,
            CompanyRule,
            VipUsername,
            EffectiveDate,
            AccountHolderOldValue,
            AccountHolderNewValue
        from 
            SageStaging.MASSMART.AuditCondensed
        where
            FieldName = 'Account Holder Name'
    ) a
    join
    (
        select
            Auditfieldid,
            CompanyRuleID,
            CompanyRule,
            VipUsername,
            EffectiveDate,
            AccountNoOldValue,
            AccountNoNewValue
        from 
            SageStaging.MASSMART.AuditCondensed
        where
            FieldName = 'Account Number'
    ) b
    on
        a.Auditfieldid  = b.Auditfieldid  and
        a.CompanyRuleID = b.CompanyRuleID and
        a.CompanyRule   = b.CompanyRule   and
        a.VipUsername   = b.VipUsername   and
        a.EffectiveDate = b.EffectiveDate 
    join
    (
        select
            Auditfieldid,
            CompanyRuleID,
            CompanyRule,
            VipUsername,
            EffectiveDate,
            AccountTypeOldValue,
            AccountTypeNewValue
        from 
            SageStaging.MASSMART.AuditCondensed
        where
            FieldName = 'Account Type'
    ) c
    on
        a.Auditfieldid  = c.Auditfieldid  and
        a.CompanyRuleID = c.CompanyRuleID and
        a.CompanyRule   = c.CompanyRule   and
        a.VipUsername   = c.VipUsername   and
        a.EffectiveDate = c.EffectiveDate 
    join
    (
        select
            Auditfieldid,
            CompanyRuleID,
            CompanyRule,
            VipUsername,
            EffectiveDate,
            BankOldValue,
            BankNewValue
        from 
            SageStaging.MASSMART.AuditCondensed
        where
            FieldName = 'Bank'
    ) d
    on
        a.Auditfieldid  = d.Auditfieldid  and
        a.CompanyRuleID = d.CompanyRuleID and
        a.CompanyRule   = d.CompanyRule   and
        a.VipUsername   = d.VipUsername   and
        a.EffectiveDate = d.EffectiveDate 
    join
    (
        select
            Auditfieldid,
            CompanyRuleID,
            CompanyRule,
            VipUsername,
            EffectiveDate,
            BranchOldValue,
            BranchNewValue
        from 
            SageStaging.MASSMART.AuditCondensed
        where
            FieldName = 'Bank Branch'
    ) e
    on
        a.Auditfieldid  = e.Auditfieldid  and
        a.CompanyRuleID = e.CompanyRuleID and
        a.CompanyRule   = e.CompanyRule   and
        a.VipUsername   = e.VipUsername   and
        a.EffectiveDate = e.EffectiveDate;

You have been given an appalling database design; I'm not surprised you find it difficult. The name suggests that it is condensed data. It isn't. It is actually a very wasteful structure.

I am concerned about the assertion that there are 5 rows for each employee. If some don't have all 5 rows you will have to do outer joins. You will need one reliable record type for each employee if this is the case.

Max Szczurek
3#
Max Szczurek Reply to 2018-01-12 22:27:21Z

From personal experience, whenever you build a string like that, concatenating a bunch of strings together, you should wrap each variable in an ISNULL check. One null variable, and the entire string will be null.

You should probably isolate the logic for creating the email body in a function. Here's a full example with sample DDL and DML statements for testing:

CREATE TABLE AuditCondensed (
       [AuditFieldID] INT
      ,[CompanyRuleID] INT
      ,[CompanyRule] VARCHAR(10)
      ,[VipUserName] VARCHAR(10)
      ,[EffectiveDate] DATE
      ,[FieldName] VARCHAR(25)
      ,[SourceCode] VARCHAR(25)
      ,[Action] CHAR(1)
      ,[AccountNoOldValue] VARCHAR(25)
      ,[AccountNoNewValue] VARCHAR(25)
      ,[AccountTypeOldValue] VARCHAR(25)
      ,[AccountTypeNewValue] VARCHAR(25)
      ,[BankOldValue] VARCHAR(25)
      ,[BankNewValue] VARCHAR(25)
      ,[BranchOldValue] VARCHAR(25)
      ,[BranchNewValue] VARCHAR(25)
      ,[AccountHolderOldValue] VARCHAR(25)
      ,[AccountHolderNewValue] VARCHAR(25))

INSERT INTO AuditCondensed VALUES (111111, 12, 'Walmart', 'john.doe', GETDATE(), 'Account Number', '1234 - John Doe', 'I', '12345', '1234567-123', NULL,  NULL, NULL, NULL, NULL, NULL, NULL, NULL)
INSERT INTO AuditCondensed VALUES (111111, 12, 'Walmart', 'john.doe', GETDATE(), 'Account Type', '1234 - John Doe', 'I', NULL, NULL, NULL, 'Savings', NULL, NULL, NULL, NULL, NULL, NULL)
INSERT INTO AuditCondensed VALUES (111111, 12, 'Walmart', 'john.doe', GETDATE(), 'Bank', '1234 - John Doe', 'I', NULL, NULL, NULL,  NULL, 'Old Bank', 'New Bank', NULL, NULL, NULL, NULL)
INSERT INTO AuditCondensed VALUES (111111, 12, 'Walmart', 'john.doe', GETDATE(), 'Branch', '1234 - John Doe', 'I', NULL, NULL, NULL,  NULL, NULL, NULL, 'Branch 1', 'Branch 2', NULL, NULL)
INSERT INTO AuditCondensed VALUES (111111, 12, 'Walmart', 'john.doe', GETDATE(), 'Account Holder', '1234 - John Doe', 'I', NULL, NULL, NULL,  NULL, NULL, NULL, NULL, NULL, '', 'John Doe')

CREATE FUNCTION dbo.fn_create_email_body(@SourceCode VARCHAR(25)) RETURNS NVARCHAR(MAX)
AS
BEGIN

    DECLARE @MailSubject NVARCHAR(200) = ''
    DECLARE @MessageBody NVARCHAR(MAX) = ''
    DECLARE @EFFECTIVEDATE DATE
    DECLARE @COMPANYRULE VARCHAR(10)
    DECLARE @VIPUSERNAME VARCHAR(10)
    DECLARE @ACTION CHAR(1)
    DECLARE @ACCOUNTHOLDEROLDVALUE VARCHAR(25)
    DECLARE @ACCOUNTHOLDERNEWVALUE VARCHAR(25)
    DECLARE @ACCOUNTNOOLDVALUE VARCHAR(25)
    DECLARE @ACCOUNTNONEWVALUE VARCHAR(25)
    DECLARE @ACCOUNTTYPEOLDVALUE VARCHAR(25)
    DECLARE @ACCOUNTTYPENEWVALUE VARCHAR(25)
    DECLARE @BANKOLDVALUE VARCHAR(25)
    DECLARE @BANKNEWVALUE VARCHAR(25)
    DECLARE @BRANCHOLDVALUE VARCHAR(25)
    DECLARE @BRANCHNEWVALUE VARCHAR(25)

    SELECT TOP 1 @EFFECTIVEDATE = EffectiveDate, @COMPANYRULE = CompanyRule, @VIPUSERNAME = VipUserName, @ACTION = Action,
        @ACCOUNTHOLDEROLDVALUE = (SELECT AccountHolderOldValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Account Holder'),
        @ACCOUNTHOLDERNEWVALUE = (SELECT AccountHolderNewValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Account Holder'),
        @ACCOUNTNOOLDVALUE = (SELECT AccountNoOldValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Account Number'),
        @ACCOUNTNONEWVALUE = (SELECT AccountNoNewValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Account Number'),
        @ACCOUNTTYPEOLDVALUE = (SELECT AccountTypeOldValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Account Type'),
        @ACCOUNTTYPENEWVALUE = (SELECT AccountTypeNewValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Account Type'),
        @BANKOLDVALUE = (SELECT BankOldValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Bank'),
        @BANKNEWVALUE = (SELECT BankNewValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Bank'),
        @BRANCHOLDVALUE = (SELECT BranchOldValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Branch'),
        @BRANCHNEWVALUE = (SELECT BranchNewValue FROM AuditCondensed WHERE SourceCode = @SourceCode AND FieldName = 'Branch')
    FROM AuditCondensed 
    WHERE SourceCode = @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:    ' + ISNULL(FORMAT(@EFFECTIVEDATE, 'M/d/yyyy'), '') + '<br>' +
    ' Company:    ' + ISNULL(@COMPANYRULE, '') + '<br>' +
    ' Username:    ' + ISNULL(@VIPUSERNAME, '') + '<br>' +
    ' Employee Details:    ' + ISNULL(@SOURCECODE, '') + '<br>' +
    ' Action:    ' + ISNULL(@ACTION, '') + '<br>' +
    ' Account Holder:  ' + ' Old Value:    ' + ISNULL(@ACCOUNTHOLDEROLDVALUE, '') + ' New Value:    ' + ISNULL(@ACCOUNTHOLDERNEWVALUE, '') + '<br>' +
    ' Account Number:  ' + ' Old Value:    ' + ISNULL(@ACCOUNTNOOLDVALUE, '') + ' New Value:    ' + ISNULL(@ACCOUNTNONEWVALUE, '') + '<br>' +
    ' Account Type:  ' + ' Old Value:    ' + ISNULL(@ACCOUNTTYPEOLDVALUE, '') + ' New Value:    ' + ISNULL(@ACCOUNTTYPENEWVALUE, '') + '<br>' +
    ' Bank:  ' + ' Old Value:    ' + ISNULL(@BANKOLDVALUE, '') + ' New Value:    ' + ISNULL(@BANKNEWVALUE, '') + '<br>' +
    ' Bank Branch:  ' + ' Old Value:    ' + ISNULL(@BRANCHOLDVALUE, '') + ' New Value:    ' + ISNULL(@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>'

    RETURN @MessageBody
END
GO

SELECT dbo.fn_create_email_body('1234 - John Doe')

Note the query in the function that sets all the variable values. It uses TOP to set the values for all the constant variables, and subqueries to conditionally set the variables that are dependent on the FieldName. If you use this, you'll need to make sure the data types of the variables (and lengths) match your database.

John Cappelletti
4#
John Cappelletti Reply to 2018-01-12 23:18:30Z

Perhaps another approach dbFiddle

Example

-- Just creating a DUMMY dataset --
-----------------------------------
Declare @YourTable table (ID int,Active bit,First_Name varchar(50),Last_Name varchar(50),EMail varchar(50))
Insert into @YourTable values
(1,1,'John','Smith','johnsmith.@email.com'),
(2,0,'Jane','Doe','janedoe.@email.com')

-- Here we have our Pattern/Template --
---------------------------------------
Declare @Pattern varchar(max) = '
Dear [[First_Name]] [[Last_Name]]

Please confirm your email address <b>[[EMail]]</b>

Thank You
'


-- The actual query --
----------------------
Select A.ID
      ,Formatted = [dbo].[svf-Str-Tokenize]((Select A.* for XML Raw),@Pattern)
 From  @YourTable A

Returns

ID  Formated
1   Dear John Smith

    Please confirm your email address <b>johnsmith.@email.com</b>

    Thank You

2   Dear Jane Doe

    Please confirm your email address <b>janedoe.@email.com</b>

    Thank You

The UDF if Interested

CREATE FUNCTION [dbo].[svf-Str-Tokenize](@XML xml,@Template varchar(max))
Returns varchar(max)
Begin
    Select @Template = Replace(@Template,Item,Value)
     From (
            Select Item   = '[['+attr.value('local-name(.)','varchar(100)')+']]'
                  ,Value  = attr.value('.','varchar(max)') 
             From  @XML.nodes('/row') as xn(n)
             Cross Apply xn.n.nodes('./@*') AS xa(attr)
            Union All
            Select Item  = left(Item,charindex(']]',Item)+1)
                  ,Value = ' ' 
             From (
                Select Item  = ltrim(rtrim(B.i.value('(./text())[1]', 'varchar(max)')))
                 From  (Select x = Cast('<x>' + replace((Select replace(replace(@Template,'[[','||[['),'||','§§Split§§') as [*] For XML Path('')),'§§Split§§','</x><x>')+'</x>' as xml).query('.')) as A 
                 Cross Apply x.nodes('x') AS B(i)
                  ) P 
             Where charindex('[[',Item)>0 and charindex(']]',Item)>0
          ) A 

    Return ltrim(rtrim(replace(replace(replace(@Template,' ','><'),'<>',''),'><',' ')))
End
You need to login account before you can post.

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

© 2016 Powered by mzan.com design MATCHINFO