As mentioned, in SQL Server versions before 2016, you need to take care of temporal data by yourself. Even in SQL Server 2016, you still need to take care of the human, or application, times. The following code shows an example of how to create a table with validity intervals expressed with the b and e columns, where the beginning and the end of an interval are represented as integers. The table is populated with demo data from the WideWorldImporters.Sales.OrderLines table:
USE tempdb; GO SELECT OrderLineID AS id, StockItemID * (OrderLineID % 5 + 1) AS b, LastEditedBy + StockItemID * (OrderLineID % 5 + 1) AS e INTO dbo.Intervals FROM WideWorldImporters.Sales.OrderLines; -- 231412 rows GO ALTER TABLE dbo.Intervals ADD CONSTRAINT PK_Intervals PRIMARY KEY(id); CREATE INDEX idx_b ON dbo.Intervals(b) INCLUDE(e); CREATE INDEX idx_e ON dbo.Intervals(e) INCLUDE(b); GO
Please note also the indexes created. The two indexes are optimal for searches at the beginning of an interval or on the end of an interval. You can check the minimum beginning and maximum end of all intervals with the following code:
SELECT MIN(b), MAX(e) FROM dbo.Intervals;
You can see in the results that the minimum beginning time point is 1 and the maximum end time point is 1155. Now you need to give the intervals some time context. In this case, a single time point represents a day. The following code creates a date lookup table and populates it. Note that the starting date is July 1, 2014:
CREATE TABLE dbo.DateNums (n INT NOT NULL PRIMARY KEY, d DATE NOT NULL); GO DECLARE @i AS INT = 1, @d AS DATE = '20140701'; WHILE @i <= 1200 BEGIN INSERT INTO dbo.DateNums (n, d) SELECT @i, @d; SET @i += 1; SET @d = DATEADD(day,1,@d); END; GO
Now you can join the dbo.Intervals table to the dbo.DateNums table twice, to give context to the integers that represent the beginning and the end of the intervals:
SELECT i.id, i.b, d1.d AS dateB, i.e, d2.d AS dateE FROM dbo.Intervals AS i INNER JOIN dbo.DateNums AS d1 ON i.b = d1.n INNER JOIN dbo.DateNums AS d2 ON i.e = d2.n ORDER BY i.id;
The abbreviated result from the previous query is:
id b dateB e date -- --- ---------- --- ---------- 1 328 2015-05-24 332 2015-05-28 2 201 2015-01-17 204 2015-01-20 3 200 2015-01-16 203 2015-01-19
Now you can see which day is represented by which integer.