2

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 |
    +----+-------+-------+------+
Improve this question
1

1 Answer 1

Reset to default
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
8
  • 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
    Commented Jan 22, 2020 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
    Commented Jan 22, 2020 at 8:11
  • 1
    "I can't insert literal datetime values in the temporal date fields". Are you sure about that? ;)
    – Peter Vandivier
    Commented Jan 22, 2020 at 12:06
  • 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
    Commented Jan 22, 2020 at 22:25
  • 1
    @HandyD "...but once you enable the system versioning you can no longer do direct inserts." Are you sure about that? ;)
    – Peter Vandivier
    Commented Jan 23, 2020 at 8:24

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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