How do I get the lates tm.detail_notes ( tm.detail_notes for max(tm.timeslip_date))?

Question

I have a query that return some record, but I need to get whatever the latest tm.detail_notes on max(tm.timeslip_date). Right now I am getting an error. (I have multiple record dates on the table and each day have note)

Query

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

Error

The text, next, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

I would really appreciate any help on this. Lastly, can anyone confirmed my logic, I am trying to get cases with no tm.timeslip_date for the last 90 days. I should get that with tm.timeslip_date <= DATEADD(day, -90, GETDATE()) this logic, right.

Thank you so much

Please add some example data and expected results from that data in form of a text. — VBoka, Sep 21 '20 at 13:57
Does this answer your question? [Select first row in each GROUP BY group?](https://stackoverflow.com/questions/3800551/select-first-row-in-each-group-by-group) — SMor, Sep 21 '20 at 14:36
You really, REALLY, should not be using the text and image datatypes by now! — SMor, Sep 21 '20 at 14:36
And your reference to tm.timeslip_date in the where clause logically turns your outer join to tm into an inner join. So maybe you should think about your goal and your join logic. — SMor, Sep 21 '20 at 14:38

Parfait · Accepted Answer · 2020-09-21T15:43:23.530

Without knowing more tm.detail_notes appears to be a text column which is one of the special columns of large, open-ended data types. See ntext, text, and image (Transact-SQL). These fields cannot be used in columns expressions of an aggregate query with GROUP BY.

To incorporate the text field with your aggregation, consider using two related CTEs for a unit level and aggregate level join. Final query will then bind the text column to aggregation joined at the 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]

Thank you so much for your time. This is kinda work but when I add `[Created Date]` on Inner JOIN at the end it will only give me 500 records. When I remove it, I will get 7000 records, but I get some duplicates. I only inner join be keys. Otherwise it will run forever. Any idea why is that or any suggestions? — Suneth, Sep 21 '20 at 20:56
I don't see any data. So only you can decide what is the right output. Why can't 500 records be the solution? Not clear what you mean by *keys*. Please advise if there are duplicate rows across *all* or some columns for the exact query in this answer which joins on grouping columns and aggregates (`Created Date` and `Last Bill Date`). To be clear, this answer returns the `LEFT JOIN` data at the minimum *create date* and *last bill date*. If these aggregates are not unique per each set of grouping columns, final result will show duplicates. — Parfait, Sep 21 '20 at 21:32

How do I get the lates tm.detail_notes ( tm.detail_notes for max(tm.timeslip_date))?

1 Answers1