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
Advertisement
Answer
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]