Sample Header Ad - 728x90

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

0 votes
1 answer
34 views
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?
Asked by Pure.Krome (283 rep)
Sep 16, 2022, 04:13 AM
Last activity: Sep 19, 2022, 07:02 AM