Given a table of data with bitemporal modeling, where there are 2 dates: (i) the date that the data applies to, and (ii) the date at which the fact was known.
City Temperature Date As_of_Datetime
---- ----------- ---- --------------
Boston 32 2022/07/01 2022/06/28 13:23:00
Boston 31 2022/07/01 2022/06/29 12:00:00
Miami 81 2022/07/01 2022/06/28 13:23:00
Miami 85 2022/07/01 2022/06/29 12:00:00
What SQL query will give the latest snapshot of the data date based on the As_of_Datetime? e.g.
City Temperature Date
---- ----------- ----
Boston 31 2022/07/01
Miami 85 2022/07/01
select
t1.*
from
temperature_table t1,
(select max(As_of_Datetime) as max_as_of, “City”, “Date” from temperature_table group by “City”, “Date”) t_temp
where
t1.”City” = t_temp.”City” and t1.”Date” = t_temp.”Date” and t1.”As_of_Datetime” = t_temp.”max_as_of”
order by
t1.”City”, t1.”Date”