如何获得lates tm.detail_notes ( tm.detail_notes for max(tm.timeslip_date))？

Question

我有一个返回一些记录的查询，但是我需要获取max(tm.timeslip_date)上的最新max(tm.timeslip_date)。现在我收到了一个错误。(我在桌子上有多个记录日期，每天都有笔记)

查询

SELECT
      min(tm.create_date) 'Created Date'
    , max(tm.timeslip_date) 'Last Bill Date'
    , cases.case_sk
    , cases.case_number
    , cases.closed_ind
    , cases.atty2_sk
    , vc.atty2_name 'Business Leader'
    , em.smtp_reply_to 'Business Leader Email'
    , cases.atty1_sk
    , vc.atty1_name 'Assign Attorney'
    , tm.detail_notes
FROM dbo.cases
LEFT JOIN dbo.vcases vc ON cases.case_sk = vc.case_sk
LEFT JOIN dbo.employee em ON cases.atty2_sk = em.employee_sk    
LEFT JOIN dbo.timeslips tm ON cases.case_sk = tm.case_sk
WHERE 
      cases.case_number = '0130751-KMG' 
      AND tm.timeslip_date <= DATEADD(day, -90, GETDATE())
      AND cases.closed_ind = 'O'
GROUP BY 
      cases.case_sk
    , cases.case_number
    , cases.closed_ind
    , cases.atty2_sk
    , vc.atty2_name 
    , em.smtp_reply_to 
    , cases.atty1_sk
    , vc.atty1_name
    , tm.detail_notes

错误

文本、下一步和图像数据类型不能进行比较或排序，除非使用是NULL或LIKE运算符。

我真的很想在这方面提供任何帮助。最后，谁能证实我的逻辑，我正试图得到没有tm.timeslip_date的病例在过去的90天。我应该用tm.timeslip_date <= DATEADD(day, -90, GETDATE())这个逻辑来理解，对吧。

非常感谢

Answer 1

在不了解更多信息的情况下，tm.detail_notes似乎是一个text列，它是大型开放数据类型的特殊列之一。见ntext、文本和图像(Transact-SQL)。这些字段不能在具有列表达式的聚合查询的GROUP BY中使用。

若要将text字段与您的聚合结合起来，请考虑在单元级别和聚合级别连接中使用两个相关的CTE。最后的查询将将文本列绑定到在Last Bill Date上连接的聚合。

WITH unit AS (
   -- NO AGGREGATIONS
   SELECT
         tm.create_date 
       , tm.timeslip_date
       , cases.case_sk
       , cases.case_number
       , cases.closed_ind
       , cases.atty2_sk
       , vc.atty2_name    AS [Business Leader]
       , em.smtp_reply_to AS [Business Leader Email]
       , cases.atty1_sk
       , vc.atty1_name    AS [Assign Attorney]
       , tm.detail_notes
   FROM dbo.cases
   LEFT JOIN dbo.vcases vc ON cases.case_sk = vc.case_sk
   LEFT JOIN dbo.employee em ON cases.atty2_sk = em.employee_sk    
   LEFT JOIN dbo.timeslips tm 
         ON cases.case_sk = tm.case_sk
         AND tm.timeslip_date <= DATEADD(day, -90, GETDATE()) -- MOVED FROM WHERE TO ON CLAUSE
   WHERE cases.case_number = '0130751-KMG'
     AND cases.closed_ind = 'O'
), agg AS (
   -- NO JOINS / WHERE
   SELECT
         MIN(u.create_date)   AS [Created Date]
       , MAX(u.timeslip_date) AS [Last Bill Date]
       , u.case_sk
       , u.case_number
       , u.closed_ind
       , u.atty2_sk
       , u.[Business Leader]
       , u.[Business Leader Email]
       , u.atty1_sk
       , u.[Assign Attorney]
   FROM unit u
   GROUP BY 
         u.case_sk
       , u.case_number
       , u.closed_ind
       , u.atty2_sk
       , u.[Business Leader]
       , u.[Business Leader Email]
       , u.atty1_sk
       , u.[Assign Attorney]
)

-- FINAL QUERY JOINING ON GROUP COLUMNS AND AGG DATES
SELECT agg.*, unit.detail_notes
FROM agg
INNER JOIN unit
   ON  agg.case_sk = unit.case_sk
   AND agg.case_number = unit.case_number
   AND agg.closed_ind = unit.closed_ind
   AND agg.atty2_sk = unit.atty2_sk
   AND agg.[Business Leader] = unit.[Business Leader]
   AND agg.[Business Leader Email] = unit.[Business Leader Email]
   AND agg.atty1_sk = unit.atty1_sk
   AND agg.[Assign Attorney] = unit.[Assign Attorney]  

   AND agg.[Created Date] = unit.[create_date]
   AND agg.[Last Bill Date] = unit.[timeslip_date]