Get Latest ModifiedDate Joining Multiple Tables

Tags: , , , ,



I have 4 tables

  1. ProjectTable

    projectid: INT (pk)
    modifiedDate: DateTime2
    ...
    
  2. ProjectFinalizeTable

    id: INT (pk)
    projectid: INT (fk)
    modifiedDate: DateTime2
    ...
    
  3. ProjectAdditionalTimeTable

    id: INT (pk)
    projectid: INT (fk)
    modifiedDate: DateTime2
    ...
    
  4. ProjectCompleteTable

    id: INT (pk)
    projectid: INT (fk)
    modifiedDate: DateTime2
    ...
    

Any of tables gets modified depending upon the user’s action. All tables have projectId as foreignKey from ProjectTable.

I want to get the latest modifiedDate from any of the table. Ex: ProjectFinalizeTable can have latest modifiedDate so that-date will be returned from the query.

To give you an idea what I want is like below query (Note: below query is just to give you idea about what exactly I am looking for)

Select MAX(pt.modifiedDate, pat.modifiedDate, pft.modifiedDate, pct.modifiedDate) as latestModifiedDate 
from ProjectTable pt 
join ProjectFinalizeTable pft pt.projectid = pft.projectid
join ProjectAdditionalTimeTable pat pt.projectid = pat.projectid
join ProjectCompleteTable pct pt.projectid = pct.projectid

I have gone through many links but can’t find a way to calculate MAX(Date) from multiple tables.

Answer

Given you want the max(modifiedDate) over a number of tables, it would seem that unioning them and the aggregating them is the way to go.

with cte as (
    select projectId, modifiedDate
    from dbo.ProjectTable
    union all
    select projectId, modifiedDate
    from dbo.ProjectFinalizeTable
    union all
    select projectId, modifiedDate
    from dbo.ProjectAdditionalTimeTable
    union all
    select projectId, modifiedDate
    from dbo.ProjectCompleteTable
)
select projectId, max(modifiedDate)
from cte
group by projectId;


Source: stackoverflow