CODEWITHSUNDEEP
sqlms-access
user4581436
user4581436

Reputation: 57

SQL Group by Max Date Including Null

The code below groups multiple records with the same project name and different dates.

The problem is that some dates in the date field is null. I need a way for this code to include the max date or a null value.

I tried adding Or Is Null under Dates but then that just brings me back to my problem with projects having multiple records

Original SQL

SELECT t1.Project_Name, t1.IssueNmb, t1.Date
FROM Milestones_Detail_Report_Table AS t1
WHERE (((t1.Date)=(SELECT Max(t2.Date) 
     FROM Table_Name t2 
     WHERE t2.Project_Name=t1.Project_Name
     GROUP BY t2.Project_Name)))

SQL with OR 

SELECT t1.Project_Name, t1.IssueNmb, t1.Date
FROM Milestones_Detail_Report_Table AS t1
WHERE (((t1.Date)=(SELECT Max(t2.Date) 
     FROM Table_Name t2 
     WHERE t2.Project_Name=t1.Project_Name
     GROUP BY t2.Project_Name))) OR (((t1.Date) Is Null));

Table: TableName

Project_Name |  IssueNmb | Date
------------------------
  Car        |    1      | 1/1/15
  Car        |    1      | 2/1/15 <--This record populates
  House      |    1      |        <--This record does NOT populate

Upvotes: 0

Views: 873

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

You might want this where clause:

WHERE (t1.Date IS NULL) OR
      (t1.Date = (SELECT iif(COUNT(*) <> COUNT(t2.Date), NULL, Max(t2.Date))
                  FROM Table_Name t2 
                  WHERE t2.Project_Name = t1.Project_Name
                 )
      )

This will treat NULL as the maximum date, so there will be no matches on the subquery if any values of t2.Date are NULL for a given project.

Note that you do not need the group by in the subquery.

Upvotes: 1

Related Questions