SQL Server 2012 Max Date in Subquery in a joined table

Question

I manage a state wide application and use Tableau to create visualizations of data.

I have been tasked with creating a visualization that show how much time is passing between contact entries and today (Case Note Dates). I know how to isolate the max case note date in the case note table:

Select 
    [Case_Master_ID],
    [Case_Note_Date],
    [Case_Note_Category_Desc],
    [Case_Note_Summary_Narr]
From 
    buCase_Note 
Where 
    Case_Note_Date = (Select MAX(Case_Note_Date)
                      From buCase_Note)

This query will show me that max case notes in the table from today. The issue is I need to show the max case note for all participants, not just the ones from today. The original query I have been using to view case notes is:

Select
    vc.[_Case Master ID],
    vc.[_Caseload Assignment Current],
    vc.[_Participant Name],
    vc.[Case Status],
    vc.[Reporting Structure Level 4],
    vc.[Reporting Structure Level 5],
    vc.[Application Date],
    vc.[Eligibility Date],
    vc.[Eligibility Determination Extension Date],
    vc.[Eligibility Extended To Date], 
    vc.[Days in Application],
    cn.[Case_Note_Date], 
    cn.[Case_Note_Category_Desc],
    cn.[Case_Note_Summary_Narr]
From  
    biVR_Cases vc
Left outer Join 
    buCase_Note cn ON cn.Case_Master_ID = vc.[_Case Master ID]

I need to keep biVR_Cases on the left to show all the open clients. Then I need to join in the case note table and for every participant, I want to show their max case note date. When I add this to the end of the above query:

Where cn.[Case_Note_Date] = (
    Select
        MAX(cn.Case_Note_Date)
    From buCase_Note)

I get the following error is SSMS 2012:

An aggregate may not appear in the WHERE clause unless it is in a subquery contained in a HAVING clause or a select list, and the column being aggregated is an outer reference.

I am looking to retain the bi table on the left while successfully joining in the case note table and bringing in only the most recent case note per participant.

Adding details: Of course, Here is a sample of data I get when running the following query:

Select
 vc.[_Case Master ID],
 vc.[_Caseload Assignment Current],
 vc.[_Participant Name],
cn.[Case_Note_Date],
From  biVR_Cases vc
LEFT outer JOIN buCase_Note cn ON vc.[_Case Master ID] = cn.Case_Master_ID

_Caseload Assignment Current Test Participant Name Casenote Date Test Counselor Participant A September 29, 2010 September 23, 2010 August 30, 2010 June 30, 2010 June 1, 2010

The bi table contains participant information like name, application, case master ID, etc. The casenote table contains the case master ID as well hence the join. It also contains the dates each entry is created. So for the dataset above, I am trying to only bring in the most recent casenote for each participant. I only included 1 in the sample above, but we have over 15,000. Each participant will have many case notes. I am trying to grab the top casenote so I can calculate the date difference between the most recent case note and today for each participant. When I add :

Where cn.[Case_Note_Date] = (Select
top 1 [Case_Note_Date]
From buCase_Note
Order by 1 DESC))
 OR 
Where Case_Note_Date=(
Select
MAX(Case_Note_Date)
From buCase_Note)

It is only showing the top or max casenote for participants that had a casenote created today. Instead of showing the max casenote in the casenote table, I need the max casenote per participant. I hope that makes more sense.

You should show some data. Your MAX(cn.Case_Note_Date) will return only one data from buCase_Note not one per [_Case Master ID] — Chuck, May 06 '16 at 20:36

score 1 · Accepted Answer · answered May 06 '16 at 21:38

You might try something like what I have included below. Without actual data, I do not know if it is efficient enough for you, but it should work. If you get a better answer, however, I would love to know it.

Select vc.[_Case Master ID],
 vc.[_Caseload Assignment Current],
 vc.[_Participant Name],
 vc.[Case Status],
 vc.[Reporting Structure Level 4],
 vc.[Reporting Structure Level 5],
 vc.[Application Date],
 vc.[Eligibility Date],
 vc.[Eligibility Determination Extension Date],
 vc.[Eligibility Extended To Date], 
 vc.[Days in Application],
 cn.[Case_Note_Date], 
 cn.[Case_Note_Category_Desc],
 cn.[Case_Note_Summary_Narr]
From  biVR_Cases vc
LEFT outer JOIN 
   (SELECT Case_Master_ID, Case_Note_Date, Case_Note_Category_Desc, Case_Note_Summar_Narr, 
           ROW_NUMBER() OVER (PARTITION BY Case_Master_ID ORDER BY Case_Note_Date DESC) as RowNum FROM buCase_Note) cn 
   ON cn.Case_Master_ID = vc.[_Case Master ID] AND cn.RowNum=1

Wow, this worked perfectly. I would love to hear an explanation of the logic but do not want to take anymore of your time. Thank you! — Mike, May 06 '16 at 22:06
You might try reading up on ROW_NUMBER() and the OVER clause. The basic idea is that this gives a number to each record with the same Case_Master_ID in descending order by Case_Note_Date. The most recent date for each Case_Master_ID will have a RowNum value of 1. I don't claim it is the most efficient method, but it works. — Brian Payne, May 09 '16 at 19:40

score 0 · Answer 2 · answered May 06 '16 at 19:57

0

Remove the cn. from the MAX(cn.Case_Note_Date) row. You don't want to reference column from the main query. You just want to select a Case_Note_Date from buCase_Note.

So the whole query will be

Select
 vc.[_Case Master ID],
 vc.[_Caseload Assignment Current],
 vc.[_Participant Name],
 vc.[Case Status],
 vc.[Reporting Structure Level 4],
 vc.[Reporting Structure Level 5],
 vc.[Application Date],
 vc.[Eligibility Date],
 vc.[Eligibility Determination Extension Date],
 vc.[Eligibility Extended To Date], 
 vc.[Days in Application],
 cn.[Case_Note_Date], 
 cn.[Case_Note_Category_Desc],
 cn.[Case_Note_Summary_Narr]
From  biVR_Cases vc
LEFT outer JOIN buCase_Note cn ON cn.Case_Master_ID = vc.[_Case Master ID]
Where cn.[Case_Note_Date] = (Select
MAX(Case_Note_Date)
From buCase_Note)

answered May 06 '16 at 19:57

j.kaspar

751
1
11
29

Hi J.kasper,Thanks for the quick reply. The suggested adjustment made the query go through. I am still having the same issue as before though, it is still only pulling case notes from today. IS there a different way to get the most recent casenote for each participant, even if the most recent casenote was from 4 months ago? Thank you again for taking the time to look this over. – Mike May 06 '16 at 20:11
@Mike Can you please provide the definition of the two tables and some sample data? It would be much easier to come up with a solution – j.kaspar May 06 '16 at 21:17
I added details to my original question. It looks like it did not take the excel export formatting. In essence participant will have a 1 to many relationship with casenotes. 1 participant and many casenotes over time. The goal is to isolate the top or max casenote so I can d a date difference calculation between the most recent casenote date and today, whatever today is when it is run. – Mike May 06 '16 at 21:45

SQL Server 2012 Max Date in Subquery in a joined table

2 Answers2