SQL Server: detect inventory changes

Question

I have a simple inventory table:

IF OBJECT_ID('tempdb.dbo.#t') IS NOT NULL
    DROP TABLE #t
GO

CREATE TABLE #t
(
    [date] DATE,
    Item VARCHAR(1),
    [Location] INT,
    Qty INT
)

INSERT INTO #t ([date], [Item], [Location], [Qty]) 
VALUES ('2017-11-16', 'A', 1, 5),
       ('2017-11-16', 'B', 1, 5),
       ('2017-11-16', 'B', 2, 10),
       ('2017-11-16', 'A', 3, 1),
       ('2017-11-16', 'C', 3, 2),
       ('2017-11-16', 'A', 4, 20),
       ('2017-11-15', 'A', 1, 5),
       ('2017-11-15', 'B', 1, 5),
       ('2017-11-15', 'B', 2, 10),
       ('2017-11-15', 'A', 3, 1),
       ('2017-11-15', 'C', 3, 8),
       ('2017-11-14', 'A', 1, 10),
       ('2017-11-14', 'B', 1, 1),
       ('2017-11-14', 'B', 2, 10),
       ('2017-11-14', 'A', 3, 1),
       ('2017-11-14', 'C', 3, 8)

I would like to find out the date (in the where clause) and also the difference in quantity of a location-item-level for the past.

Thus the result should be as follows:

+------------+------+----------+-----+------------+---------+
|    Date    | Item | Location | Qty | LastChange | LastQty |
+------------+------+----------+-----+------------+---------+
| 16.11.2017 | A    |        1 |   5 | 14.11.2017 |      10 |
| 16.11.2017 | B    |        1 |   5 | 14.11.2017 |       1 |
| 16.11.2017 | B    |        2 |  10 |            |         |
| 16.11.2017 | A    |        3 |   1 |            |         |
| 16.11.2017 | C    |        3 |   2 | 15.11.2017 |       8 |
| 16.11.2017 | A    |        4 |  20 |            |         |
+------------+------+----------+-----+------------+---------+

Since the inventory table is quite big I would like to avoid windowed-functions if possible.

I have self-joined the inventory table. However, I have problems to find a clause for eliminating the irrelevant data sets.

SELECT
    a.[date],
    a.Item,
    a.Location,
    a.qty,
    b.[date] LastChange,
    b.qty LastQty
FROM 
    #t a
LEFT JOIN 
    #t b ON a.Item = b.Item 
         AND a.location = b.location  
         AND b.date < a.date
WHERE   
    a.date = '2017-11-16'

Windowed function can be more efficient than using subqueries. Look for pivot to do this. — Daniel E., Nov 16 '17 at 08:38
Which version of SQL Server are you using? Also, window functions were introduced to increase performance for cases like this, so why not using a window function? — Giorgos Betsos, Nov 16 '17 at 08:47
Fair enough, if this has a positive impact than I am prone to use them. Server Version is 11.0.6598.0. Thank you :-) — rasenkantenstein, Nov 16 '17 at 08:57
@JeanDoux : You mean using the pivot function in MSSQL? Wouldn't that work on only one column to pivot? — rasenkantenstein, Nov 16 '17 at 09:00

Giorgos Betsos · Answer 1 · 2017-11-16T09:10:26.900

2

You need an additional LEFT JOIN in order to eliminate redundant records:

SELECT a.[date], a.Item, a.Location, a.qty,
       b.[date] LastChange, b.qty LastQty
FROM t AS a
LEFT JOIN t AS b 
   ON a.Item = b.Item AND a.location = b.location AND b.date < a.date AND a.qty != b.qty
LEFT JOIN t AS c 
   ON c.Item = b.Item AND c.location = b.location AND c.date < b.date    
WHERE   
    a.[date] = '2017-11-16' AND c.Item IS NULL

Using

LEFT JOIN t AS c 
   ON c.Item = b.Item AND c.location = b.location AND c.date < b.date

combined with

WHERE   
    ... AND c.Item IS NULL

is like saying: get me those b records having no other, c, record with an earlier date.

Demo here

Using FIRST_VALUE window function:

;WITH CTE AS (
    SELECT [date], [Item], [Location], [Qty],
           FIRST_VALUE([date]) OVER (PARTITION BY [Item], [Location] 
                                     ORDER BY [date]) AS LastChange,
           FIRST_VALUE([Qty]) OVER (PARTITION BY [Item], [Location] 
                                    ORDER BY [date]) AS LastQty
    FROM t
)
SELECT [date], [Item], [Location], [Qty],
       IIF([Qty] != [LastQty], LastChange, NULL) AS LastChange,
       IIF([Qty] != [LastQty], LastQty, NULL) AS LastQty
FROM CTE
WHERE [date] = '2017-11-16'

Demo here

edited Nov 16 '17 at 09:10

answered Nov 16 '17 at 09:00

Giorgos Betsos

71,379
9
63
98

Thank you. Learned a lot here. The windowed functions are indeed must faster! Thank you. – rasenkantenstein Nov 16 '17 at 09:54
There is one problem however when using FIRST_VALUE. There is no detection in intermediate changes. In you demo you can see this with the C article. The last change should be 15.11. not 14.11. – rasenkantenstein Nov 16 '17 at 10:09
@rasenkantenstein So you want to detect the *latest* record in this case? What if you have multiple changes, like `('2017-11-16', 'C', 3, 2)`, ` `('2017-11-16', 'C', 3, 8)`, `('2017-11-16', 'C', 3, 5)`? – Giorgos Betsos Nov 16 '17 at 10:26
Hi Giorgos, exactly. The Date of the previous change and the amount of that change. This is a snapshot table being create once a day, the primary key is date+location+item. Only one state is possible. However the windowed functions were indeed very fast. – rasenkantenstein Nov 16 '17 at 10:37

Sergey Menshov · Answer 2 · 2017-11-16T09:08:49.800

1

Try this query

DECLARE @ReportDate date='20171116'

SELECT
  curData.[date],
  curData.Item,
  curData.Location,
  curData.Qty,
  lastData.[date] LastChange,
  lastData.Qty LastQty
FROM
  (
    SELECT *
    FROM #t
    WHERE [date]=@ReportDate
  ) curData
OUTER APPLY
  (
    SELECT TOP 1 *
    FROM #t lastData
    WHERE lastData.Item=curData.Item
      AND lastData.Location=curData.Location
      AND lastData.[date]<curData.[date]
      AND lastData.Qty<>curData.Qty
    ORDER BY lastData.[date] DESC
  ) lastData

edited Nov 16 '17 at 09:08

answered Nov 16 '17 at 09:03

Sergey Menshov

3,856
2
8
19

I changed my answer. In my first version I forgot to use the condition `lastData.Qty<>curData.Qty` – Sergey Menshov Nov 16 '17 at 09:11
Hi, I have never really used the concept apply so far. I might want to explore this further. Thank you! – rasenkantenstein Nov 16 '17 at 09:55
1

@rasenkantenstein - SQL is amazing language. I wish you success on this way! – Sergey Menshov Nov 16 '17 at 09:57

SQL Server: detect inventory changes

2 Answers2