Query to detect changes in item location from one day before (RFID tracking)

Question

I have some RFID tags on itens, which generate some data on a table. Unfortunately, the reports on this system are poorly to non existant, and I want to make one. Consider my data somethings as this. Everytime we "query" the system, it scans and inserts all item data on the same table:

My desired output, when using 02/03/2020 day as base, is this (based on the location):

I've done some color (based on the address column) on the first picture to better ilustrate. In this example, you can see all possible status:

• No change (ITEM_ID stood on the same place as the day before)
• Item left forever (was sold, so it is not on any other place - example as ITEM_ID 00006)
• Item was moved from address A to B (example ITEM_ID 0005: was on A104 and now is in A110)
• New item added on a unused address (example ITEM_ID 0008, another bag)
• New item on a address used yesterday (example ITEM_ID 0009 which was put on A104, used the day before by ITEM_ID 0005)

Someone told me that this can be accomplished by using ROLLUP or CUBE, but I'm not sure if it is the best approach, or how to use it.

The totals are a plus. I can export data do Excel and do a count based on the STATUS column (or even another select)

In summary, it is a tracking report.

ANY tips will be kindly appreciated.

SQL SERVER is Microsoft SQL Server 2016 Standard

Answer 1

Basically, you want to join the table with itself to track where things are going. I don't think I've addressed all of your concerns, but this should be a good place to start.

CREATE TABLE rfid 
  ( 
     item_id     INT, 
     address     VARCHAR(50), 
     description VARCHAR(50), 
     qty         INT, 
     [date]      DATE 
  ) 

INSERT INTO rfid 
            (item_id, 
             address, 
             description, 
             qty, 
             [date]) 
VALUES      (1, 
             'a100', 
             'cable', 
             100, 
             '2020-01-03'), 
            (2, 
             'a101', 
             'charger', 
             100, 
             '2020-01-03'), 
            (3, 
             'a102', 
             'laptop', 
             100, 
             '2020-01-03'), 
            (4, 
             'a103', 
             'chair', 
             100, 
             '2020-01-03'), 
            (5, 
             'a104', 
             'basket', 
             100, 
             '2020-01-03'), 
            (6, 
             'a105', 
             'bag', 
             100, 
             '2020-01-03'), 
            (1, 
             'a100', 
             'cable', 
             100, 
             '2020-02-03'), 
            (2, 
             'a101', 
             'charger', 
             100, 
             '2020-02-03'), 
            (3, 
             'a102', 
             'laptop', 
             100, 
             '2020-02-03'), 
            (4, 
             'a103', 
             'chair', 
             100, 
             '2020-02-03'), 
            (5, 
             'a110', 
             'basket', 
             100, 
             '2020-02-03'), 
            (8, 
             'a200', 
             'bag', 
             100, 
             '2020-02-03'), 
            (9, 
             'a104', 
             'keyboard', 
             100, 
             '2020-02-03'); 

WITH inventory_new (item_id, address, description) 
     AS (SELECT item_id, 
                address, 
                description 
         FROM   rfid 
         WHERE  [date] = '2020-02-03'), 
     inventory_old (item_id, address, description) 
     AS (SELECT item_id, 
                address, 
                description 
         FROM   rfid 
         WHERE  [date] = '2020-01-03') 
SELECT COALESCE(o.item_id, n.item_id)         item_id, 
       COALESCE(o.description, n.description) description, 
       CASE 
         WHEN o.address = n.address THEN 'no change' 
         WHEN o.address IS NULL THEN 'in' 
         WHEN n.address IS NULL THEN 'out' 
       END                                    outcome 
FROM   inventory_old o 
       FULL OUTER JOIN inventory_new n 
                    ON ( n.item_id = o.item_id )