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,