Compare dates from 2 separate sql queries-CodePudding

For an application we have, it is incorrectly listing the last access date and I'm trying tp get it updated.

To see what the date it is currently showing I can do;

select LoginID, ActionDate
From LoginHistory

Now to see what the date should actually be I'm doing:

select lg.UserName, lg.LoginID, lg.Activated, id.Title, max(ih.ActionedDate) as LastAccessed
from Login lg
left join (
       ItemLoginAssociation la
       join ItemView id
       on id.ItemId = la.ItemID
) on la.LoginID = lg.LoginID
left join ItemHistory ih
on id.ItemId = ih.ActionedBy and id.VersionRank =1
where action= 'View' 
group by lg.UserName, lg.LoginID, lg.Activated, id.Title
order by LastAccessed

What I would like to do is compare the ActionDate from the first query and the LastAccessed date from the second query by the loginID and if the date in the second query is more recent than the date in the first one, replace the first query date with the second query date if that makes sense?

How can I go about doing that?

CodePudding user response：

You can left join your second query:

select lh.LoginID, lh.ActionDate, x.LastAccessed
From LoginHistory lh
left join (

   select lg.UserName, lg.LoginID, lg.Activated, id.Title, max(ih.ActionedDate) as LastAccessed
   from Login lg
   left join (
       ItemLoginAssociation la
       join ItemView id
       on id.ItemId = la.ItemID
   ) on la.LoginID = lg.LoginID
   left join ItemHistory ih
   on id.ItemId = ih.ActionedBy and id.VersionRank =1
   where action= 'View' 
   group by lg.UserName, lg.LoginID, lg.Activated, id.Title
   -- order by LastAccessed
) x on x.loginID = lh.LoginID

The order by is commented because ordering a sub-query is pretty useless.

P.S. If both queries work on your DBMS, this query should work too, because it's pretty standard SQL.