0

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.

Dale K
  • 25,246
  • 15
  • 42
  • 71
Junaid
  • 2,572
  • 6
  • 41
  • 77
  • @DaleK, I think here we don't need sample data, the tables structure are crystal clear. I can't think of any solution for this, so I haven't try any query. Can't understand how to calculate MAX(Date) by comparing from multiple tables. **I just want to know how can we calculate MAX(Date) from multiple tables.** – Junaid Nov 26 '20 at 08:46
  • Its obvious to you of course, its your system, but its not obvious to me (and potentially not for others)... the clearer your question is, and the more details you provide, the faster you will get an answer. You must be able to produce some query, just not one that gets the data you want. And if you add sample data as DDL/DML (i.e. populated temp tables) its even easy to assist you because we don't have to type in test data. SQL question are **much easier** to answer when there is specific data to use rather than a theoretical question. – Dale K Nov 26 '20 at 09:00

2 Answers2

2

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;
Dale K
  • 25,246
  • 15
  • 42
  • 71
  • 1
    Just verify the results. Seems promising! Good way to use union though. – Junaid Nov 26 '20 at 09:18
  • Result seems good to me. Since we are using `union all`, will there be any performance issue when there will be lots of records? – Junaid Nov 26 '20 at 09:26
  • Shouldn't be, so long as you have good indexes on all tables. `union all` doesn't perform badly, its `union` which can perform badly as it de-dups the results. – Dale K Nov 26 '20 at 09:28
0

You can try this. Though I couldn't verify it.

SELECT 
   PT.projectid,
   (SELECT MAX(LastModifiedDate)
      FROM (VALUES (PT.ModifiedDate),(PFT.ModifiedDate),(PATT.ModifiedDate), (PCT.ModifiedDate)) AS UpdateDate(LastModifiedDate)) 
    AS LastModifiedDate
FROM
ProjectTable PT
JOIN ProjectFinalizeTable PFT ON PT.projectid = PFT.projectid
JOIN ProjectAdditionalTimeTable PATT ON PT.projectid = PATT.projectid
JOIN ProjectCompleteTable PCT ON PT.projectid = PCT.projectid
Amit Kumar
  • 5,888
  • 11
  • 47
  • 85