How to model/query this data problem? Is this a timeseries issue?

Question

We have a large amount of data in a MS-SQL2014 (v12) server.

We need to determine if a product was for sale in some time period.

For example, was this inventory for sale in the month of July 2022? (1st-Jul-2022 -> 31st-Jul-2022).

I've made a db fiddle here which can be used to refine the answer(s).

The data we have is some inventory/stock list. It's a list of events that occur to single inventory items. So here's a sample of some 'events' that happen to this one random widget, in the inventory:

Sample Data - Events that occur with some inventory
^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^

| Id | Name      | Date       | Price | Status    |
| -- | --------- | -----------| ----- | --------- |
| 1  | INV-1     | 2022-05-01 | 100   | Available | New
| 1  | INV-1 aaa | 2022-09-05 | 100   | Available | Name change
| 1  | INV-1 bbb | 2022-09-06 | 100   | Available | Name change
| 1  | INV-1     | 2022-09-07 | 120   | Available | Name and price change
| 1  | INV-1     | 2022-09-08 | 120   | Sold      | Sold
| 1  | INV-1     | 2022-10-02 | 120   | Available | Returned. Back for sale
| 1  | INV-1     | 2022-10-01 | 115   | Available | Price change
| 1  | INV-1     | 2022-10-01 | 115   | Sold      | New

| 2  | INV-2     | 2022-05-01 | 200   | Available | New (Other product)
| 3  | INV-3     | 2022-05-01 | 300   | Available | New (Other product)
| 4  | INV-4     | 2022-10-01 | 400   | Available | New (Other product)
| 5  | INV-5     | 2022-10-01 | 500   | Available | New (Other product)

Expected Results:
^^^^^^^^^^^^^^^^^

-- Which products were available in JULY?
| Id |
| -- |
| 1  |
| 2  |
| 3  |

-- Which products available in OCT?
| Id |
| -- |
| 1  | -- Was returned and put back to available again.
| 2  |
| 3  |
| 4  |
| 5  |

Is this possible?

At first, I was told to investigate TEMPORAL TABLES. That sounds like it might seriously help .. except that it's for MS-SQL2016 (v13+). I'm on v12 -and- I won't be able to upgrade or migrate the data to a v13+ version. Also, I'm not on an Enterprise Edition and can't go to that.

Can anyone please help?

Answer 1

Feature availability aside, please keep in mind SQL Server 2014 is officially no longer a supported version of the product line by Microsoft, and will stop receiving even security updates in roughly 1.5 years, so you should probably plan to upgrade soon.

---

A similar feature to Temporal Tables that is available in SQL Server 2014 is Change Data Capture:

Change data capture (CDC) uses the SQL Server agent to record insert, update, and delete activity that applies to a table. This makes the details of the changes available in an easily consumed relational format. Column information and the metadata that is required to apply the changes to a target environment is captured for the modified rows and stored in change tables that mirror the column structure of the tracked source tables. Table-valued functions are provided to allow systematic access to the change data by consumers.

It's a fairly common feature, that's been around a while, for keeping track of historical changes to the data. So it's a reliable feature to use, but it also has some limitations / things to consider:

1. Certain schema changes such as adding / removing columns or changing a columns data type aren't automatically propagated to the history tables. To workaround this, you need to remove Change Data Capture, manually apply the change, and re-add Change Data Capture - loosely speaking.

2. The base tables being tracked can't use clustered columnstore indexes.

3. Any computed columns on the base tables are stored as NULL values in the history tables.

4. The history tables don't directly store the timestamp of when the change occurred (or when that version of the data is valid as of). But it can be derived from the LSN (Log Sequence Number) that is stored in the history table. Additionally, columns can be added to the history table as needed, so a computed column on the history table may be leveraged to supplement this.

Further reading on limitations of CDC (Change Data Capture):

1. Microsoft Books Online for CDC - Limitations

---

The only other consideration is for SQL Server 2014 (and 2016 RTM and older versions) unfortunately you need to be running Enterprise Edition to be able to use the Change Data Capture feature.