SQL temporal tables to collect invoice data for customers

Projcct Hours Time Tracking has been online now for over a year and it is time to send out the first invoices to customers. I realized that in order to correctly calculate the amounts to charge for users I will need historical data, i.e. when did users start or when where they set to inactive.

To record historical data I decided to use SQL temporal tables. I could have used database triggers to record table changes but after reading up on SQL temporal tables I decided to give it a try. Here is a good link to learn about this feature: SQL Server Temporal Tables Recipes .

One thing I like very much about temporal tables is that there are special queries that make querying on time intervals much easier, for instance you can do a query like

SELECT email, SysStartTime, SysEndTime FROM AspNetusers
FOR SYSTEM_TIME FROM @StartDate TO @EndDate
WHERE Active = 1 

This will retrieve all records with changes in a certain time period. Writing this yourself in SQL is more complicated. A good article explaining how to query temporal tables: How to query data on versioned temporal tables in SQL

This is what tables with history look like in SQL Server Management Studio:

That's it for now,
Bert-Jan