Sql-server – Aggragate values over time in temporal table

sql servertemporal-tables

Recently we started using temporal tables feature in MSSQL. Now our analytics department wants to write query aggregating data on per month basis.

Let's say we have table

    +----+-------+------------+------------+
    | Id | Value | BeginDate  | EndDate    |
    +------------+------------+------------+
    | 1  | 10    | 2019-12-24 | 9999-12-31 |
    +------------+------------+------------+
    | 1  | 20    | 2019-12-05 | 2019-12-24 |
    +------------+------------+------------+
    | 1  | 40    | 2019-11-05 | 2019-12-05 |
    +----+-------+------------+------------+

And now I would like to know average value of Value on per month basis. Is that even possible with MSSQL temporal tables? Is it possible to somehow calculate those values (for example avg(5/31 * 40 + 19/31 * 20 + 7/31 * 10) for December) automatically and then return them in following format

    +----+-------+-------+------+
    | Id | Avg   | Month | Year |
    +------------+--------------+
    | 1  | 15,16 | 12    | 2019 |
    +----+-------+-------+------+

Best Answer

This is fairly straightforward. You can query the data from your temporal tables using the FOR SYSTEM_TIME clause to return the history of each record, then simply use DATEPART to identify the month and aggregate using GROUP BY. You can see an example of this here, using SECOND instead of MONTH to group on because I can't insert literal datetime values in the temporal date fields.

Suitable code for MONTH would be:

SELECT ID, Year, Month, AVG(Value) AS AvgValue
FROM
(
  SELECT ID, Value, DATEPART(MONTH, BeginDate) AS Month, DATEPART(YEAR, BeginDate) AS Year
  FROM TemporalData
  FOR SYSTEM_TIME ALL
) s
GROUP BY ID, Month, Year
Related Question