The goal is to prepare a power BI report that provides historical data based on month-year selected by the user.
To get data for all available months, I am trying to do something like below
SELECT * FROM Employee
FOR SYSTEM_TIME
AS OF <<End of month>>
I want to run this query for every month available in the history table then union them all in a single table.
This way, I will be able to let the user select any month-year in power bi and filter this union table based on the selection.
The only question is that, how do I retrieve all available months from history table of a temporal table, so that I can iterate over them and run the above query for each?