0

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 |
    +----+-------+-------+------+
0

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
| improve this answer | |
  • Well, that looks easy indeed. What I'm unsure of is if it handles the time spans overlapping for example two months? You're grouping by DATEPART(MONTH, BeginDate) but what if DATEPART(MONTH, BeginDate) is different than DATEPART(MONTH, EndDate)? Will this query still behave as expected? – Paweł Hemperek Jan 22 at 8:06
  • Another thing to consider is following situation - BeginDate = 2010-01-01 and EndDate = 2019-01-01. Is there any way for this query to produce rows not only for 2010-01-01, but also for every month in between those two dates? – Paweł Hemperek Jan 22 at 8:11
  • "I can't insert literal datetime values in the temporal date fields". Are you sure about that? ;) – Peter Vandivier Jan 22 at 12:06
  • @PeterVandivier Cool, I didn't realise you could do that, I was auto-generating the system versioning fields during table creation. It works for generating test data, but once you enable the system versioning you can no longer do direct inserts. – HandyD Jan 22 at 22:15
  • 1
    @PawełHemperek You need to decide how you want to aggregate your data. For example, do you want the averages of data based on begin month or end month, or only records that occurred inside a single month. It depends on what the data actually is as to the most appropriate way of aggregating it. – HandyD Jan 22 at 22:25

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.