Get Latest ModifiedDate Joining Multiple Tables

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;