SQL - Identify where values have increased and decreased in a specific audit table

Question

I have two tables that basically record the maximum amount of weight in KG that a user can store in three different containers:

• dbo.Storage contains the most up to date values for a user (first screenshot)
• dbo.AuditStorage basically audits the change of these values (second screenshot)

The way that dbo.AuditStorage works is as follows (screenshot below):

The table is initially empty and when it first gets populated 3 rows will be inserted automatically (this is done by a trigger):

1. The first row is inserted but only Max_Storage1 is populated while the remaining storages remain as NULL.
2. Milliseconds after, another row is inserted. The Max_Storage1 value is persisted and Max_Storage2 is populated, while Max_Storage3 remains as NULL.
3. Miliseconds after, another row is inserted. The Max_Storage1 and Max_Storage2 values are persisted and finally Max_Storage3 values are populated.

In the above screenshot we can see that a month later the user has decided to increase his Max_Storage1 from 50 to 100 and decrease his Max_Storage2 from 500 to 400. The same logic is applied here:

1. First insert a row and update Max_Storage1 while the others Max_Storage_X remaing the same
2. Then insert anoter row and update Max_Storage2
3. Because Max_Storage3 remained the same, no row is inserted.

My goal is to be able to write a query that returns me a list of users where one of the Max_Storage_X values has increased while the other has decreased.

How can I achieve this please?

Answer 1

The problem as I see it is that you don't have any field to group by in order to do the self join that you will require to make the comparison.

Here is some sample code. You will want to add an another OR condition to compare Max_storage1 and maxstorage3, as well as Max_storage2 and maxstorage3

SELECT IDENTITY(int,1,1) AS rownum, * 
INTO #temp_audit
FROM dbo.Auditstorage
ORDER BY UPDATE_time

SELECT * FROM #temp_audit a
INNER JOIN #temp_audit b
ON a.rownum = b.rownum -2
AND a.userid = b.userid

WHERE 
(
    (a.Max_storage1 < b.Max_storage1 AND a.Max_storage2 > b.Max_storage2)
OR 
    (a.Max_storage1 > b.Max_storage1 AND a.Max_storage2 < b.Max_storage2)
)

Answer 2

You can try something like this:

DECLARE @DataSource TABLE
(
    [UserID] BIGINT
   ,[Max_Storage1] INT
   ,[Max_Storage2] INT
   ,[Max_Storage3] INT
   ,[UpdateTime] DATETIME2
);

INSERT INTO @DataSource ([UserID], [Max_Storage1], [Max_Storage2], [Max_Storage3], [UpdateTime])
VALUES (1, 50, NULL, NULL, DATEADD(DAY, 1, GETUTCDATE()))
      ,(1, 50, 500, NULL, DATEADD(DAY, 2, GETUTCDATE()))
      ,(1, 50, 500, 900, DATEADD(DAY, 3, GETUTCDATE()))
      ,(1, 100, 500, 900, DATEADD(DAY, 4, GETUTCDATE()))
      ,(1, 100, 400, 900, DATEADD(DAY, 5, GETUTCDATE()))
      ,(2, 50, NULL, NULL, DATEADD(DAY, 6, GETUTCDATE()))
      ,(2, 50, 500, NULL, DATEADD(DAY, 7, GETUTCDATE()));

WITH DataSource AS
(
    SELECT ROW_NUMBER() OVER(PARTITION BY [UserID] ORDER BY [UpdateTime]) AS [RowID]
          ,*
    FROM @DataSource
), 
DataSourcePrecalc AS
(
SELECT DS1.*
      ,ISNULL(DS1.[Max_Storage1] - DS2.[Max_Storage1], 0) + ISNULL(DS1.[Max_Storage2] - DS2.[Max_Storage2], 0) + ISNULL(DS1.[Max_Storage3] - DS2.[Max_Storage3], 0) AS [CurrDirection]
      ,LAG(ISNULL(DS1.[Max_Storage1] - DS2.[Max_Storage1], 0) + ISNULL(DS1.[Max_Storage2] - DS2.[Max_Storage2], 0) + ISNULL(DS1.[Max_Storage3] - DS2.[Max_Storage3], 0), 1, 0) OVER (PARTITION BY DS1.[UserID] ORDER BY DS1.[RowID]) AS [PrevDirection]
FROM DataSource DS1
INNER JOIN DataSource DS2
    ON DS1.[UserID] = DS2.[UserID]
    AND DS1.[RowID]  = DS2.[RowID] + 1
)
SELECT *
FROM DataSourcePrecalc
WHERE [CurrDirection] < 0 AND [PrevDirection] > 0;

The idea is to calculated the RowID for each record inserted by particular user. Then in the second CTE we have this as result:

We have current value (up or down) and previous value (up or down).So we simply need to use this row, which current value is negative, while previous was positive.