时间戳

首页 > 解决方案 > 为 HTML 格式的电子邮件构建存储过程时如何按 CASE（通常以“X”结尾）排序？

sql - 为 HTML 格式的电子邮件构建存储过程时如何按 CASE（通常以“X”结尾）排序？

问题描述

我正在尝试构建一个 SP 来发送一个 HTML 格式的报告，该报告按我的 CASE WHEN 语句中的新值排序。通常，CASE WHEN 可以以“批处理状态”结束，然后我可以按“批处理状态”排序，但这在 HTML 格式中不起作用。按状态排序对我不起作用，因为状态是数字的，我需要按字母数字排序。

SET NOCOUNT ON

DECLARE @Last3BusinessDays as DATETIME, @tableHTML NVARCHAR(MAX)

SET @Last3BusinessDays = (SELECT DATEADD(DAY, CASE (DATEPART(WEEKDAY, GETDATE()) + @@DATEFIRST) % 7 
                                                                                  WHEN 1 THEN -4 
                                                                                  WHEN 2 THEN -5 
                                                                                  ELSE -3 
                                                                             END, 
                                                                      GETDATE()))

If (SELECT count(aq.batchnum) FROM hsi.archivedqueue aq
WHERE aq.status != 8
AND datestarted < @Last3BusinessDays
AND aq.queuename like 'HIM%'
       OR aq.queuename = 'MHHS'
       OR aq.queuename = 'DX Summaries'
       OR aq.queuename = 'HCA') > 0

BEGIN

SET @tableHTML = 
N'<H3>The following HIM scans have not been committed in over 72 hours. Please index ASAP.</H3>' +
              N'<table border="1">' +
              N'<th>Batch #</th>' +
              N'<th>Batch Name</th>' +
              N'<th>Scan Date-Time</th>' +
              N'<th>Status Queue</th>' +
              N'<th>Total Documents</th>' +
              CAST ( ( 
                        SELECT
                                                td = batchnum, '', 
                                                td = rtrim(batchname), '', 
                                                td = datestarted, '', 
                                                td = case 
                                                              when status = 44 then 'Awaiting Bar Code Processing'
                                                              when status = 32 then 'Awaiting QA Review'
                                                              when status = 23 then 'Awaiting Document Separation'
                                                              when status = 1 then 'Index in Progress'
                                                              when status = 0 then 'Awaiting Index'
                                                              else 'Other'
                                                       end,  '',
                                                td = totaldocuments, ''
                                                FROM OB_PRD.hsi.archivedqueue
                                                WHERE status != 8
                                                AND datestarted < @Last3BusinessDays
                                                AND (queuename like 'HIM%'
                                                       OR queuename = 'MHHS'
                                                       OR queuename = 'DX Summaries'
                                                       OR queuename = 'HCA')

                                                ORDER BY status, datestarted DESC
               FOR XML PATH('tr'), TYPE
              ) AS NVARCHAR(MAX) ) +
              N'</table>'

按 CASE WHEN 中的新字母数字值排序是目标。目前，ORDER BY 状态按非最佳数值排序。

标签： sqlsql-server

解决方案

我会将其移到CASEasFROM子句VALUES中，这样您就可以在以下内容中轻松引用它ORDER BY：

                  SELECT td = aq.batchnum, '', 
                         td = rtrim(aq.batchname), '', 
                         td = aq.datestarted, '', 
                         td = V.[status],  '',
                         td = totaldocuments, ''
                         FROM OB_PRD.hsi.archivedqueue aq
                              CROSS APPLY (VALUES(CASE WHEN [status] = 44 THEN 'Awaiting Bar Code Processing'
                                                       WHEN [status] = 32 THEN 'Awaiting QA Review'
                                                       WHEN [status] = 23 THEN 'Awaiting Document Separation'
                                                       WHEN [status] = 1 THEN 'Index in Progress'
                                                       WHEN [status] = 0 THEN 'Awaiting Index'
                                                       ELSE 'Other'
                                                   END))V ([Status])
                         WHERE aq.status != 8
                         AND aq.datestarted < @Last3BusinessDays
                         AND (aq.queuename like 'HIM%'
                                OR aq.queuename = 'MHHS'
                                OR aq.queuename = 'DX Summaries'
                                OR aq.queuename = 'HCA')

                         ORDER BY v.[status], aq.datestarted DESC

推荐阅读