The issue I am encountering is when the email is sent to me, the results sent to my email is not formatted correctly in a html table.
First, here are my queries. The first query builds a temp table with the data that contains type M and C. The second query builds another temp table and compares the temp table created before and checks if the data contains type M or C.
use Letters
-- Build a temp table with the M and C Denial letters. We do not want to include these in our final list.
select distinct
LT.Description
into #ModCarvLetters
from dbo.AELetters_LetterTemplateText TT (nolock)
inner join dbo.AELetters_LetterTemplates LT (nolock) on
TT.TemplateUniqueID = LT.TemplateUniqueID
inner join dbo.AELetters_LetterAssignment LA (nolock) on
LT.TemplateUniqueID = LA.TemplateUniqueID
inner join dbo.AELetters_LetterAssignmentDetail LAD (nolock) on
LA.LetterAssignmentID = LAD.LetterAssignmentID
where CurrentVersion = 1
and LT.TemplateType = 'Auto'
and LAD.LetterAssignment_lkupID = 3 --- Autst
and (LAD.MatchTableColumnValue = 'C' or LAD.MatchTableColumnValue = 'M')
and cast(LA.EffFrom as DATE) <= cast(GETDATE() as DATE)
and cast(LA.EffTo as DATE) >= cast(GETDATE() as DATE)
order by LT.Description
-- Get the language from the letter just before the denial reason.
select distinct
LT.Description,
substring(TT.TemplateText, charindex('Denial', TT.TemplateText)-500, 500) as [Denial_Reason]
into #DenialLetters
from dbo.AELetters_LetterTemplateText TT (nolock)
inner join dbo.AELetters_LetterTemplates LT (nolock) on
TT.TemplateUniqueID = LT.TemplateUniqueID
inner join dbo.AELetters_LetterAssignment LA (nolock) on
LT.TemplateUniqueID = LA.TemplateUniqueID
inner join dbo.AELetters_LetterAssignmentDetail LAD (nolock) on
LA.LetterAssignmentID = LAD.LetterAssignmentID
where CurrentVersion = 1
and LT.TemplateType = 'Auto'
and TT.LanguageCodeID = 65 -- English
--and LAD.LetterAssignment_lkupID = 2
and LAD.MatchTableColumnValue = 'Denied'
and cast(LA.EffFrom as DATE) <= cast(GETDATE() as DATE)
and cast(LA.EffTo as DATE) >= cast(GETDATE() as DATE)
and LT.Description not in (select M.Description from #ModCarvLetters M)
order by LT.Description desc--, TT.TemplateText
drop table #ModCarvLetters
The following is where I send the results an a email.
DECLARE @CT AS INT
DECLARE @LETTER_DESCRIPTION AS VARCHAR (255)
DECLARE @DENIAL_REASON AS VARCHAR (MAX)
DECLARE @EMESSAGE AS VARCHAR (MAX)
SELECT @CT = COUNT(*) FROM #DenialLetters
SET @EMESSAGE = '<head><style type="text/css">h2 {font-family: Arial, verdana;} td{background-color:#F1F1F1; border:1px solid black; padding:3px;} th{background-color:#99CCFF;}</style>
<h2><font color="#0000ff" size="4">DENIAL LETTERS ON TEST</font></h2></head><table border="1"><tr><th>Letter Description</th><th>Denial Reason</th></tr>';
--<h2><font color="#0000ff" size="4">DENIAL LETTERS ON PRODUCTION</font></h2></head><table border="1">' + '<tr><th>Letter Description</th><th>Denial Reason</th></tr>';
WHILE @CT > 0
BEGIN
SELECT @LETTER_DESCRIPTION = Description, @DENIAL_REASON = Denial_Reason FROM #DenialLetters
SET @EMESSAGE = @EMESSAGE +
'<tr><td>' + @LETTER_DESCRIPTION + '</td><td>' + @DENIAL_REASON + '</td></tr>' ;
DELETE FROM #DenialLetters WHERE (Description = @LETTER_DESCRIPTION and Denial_Reason = @DENIAL_REASON)
SELECT @CT = COUNT(*) FROM #DenialLetters
--Print @CT
--Print @LETTER_DESCRIPTION
--Print @DENIAL_REASON
END
SET @EMESSAGE = @EMESSAGE + '</table>'
exec msdb.dbo.sp_send_dbmail
@recipients = 'mbruhn@regalmed.com',
@subject = 'Denial letters on REA Test',
--@subject = 'Denial letters on REA Production',
@body = @EMESSAGE,
@body_format = 'HTML'
Drop table #DenialLetters
And here is what the results appear when I recieve the email. The problem I am encountering is that the text outside of the table should be in the table. The "!=""){%>" shouldnt appear.
