Sql-server – Derive daily delta from total daily counts? Tsql

sql servert-sql

I have a table with columns:

ID, 
tableName, 
totalRowCount, 
Date

Each day I'm appending the number of rows in the relevant table. What I want to do is create a table which has the number of new rows created on each table each day. Whats the best way to do this?

Best Answer

If this is for SQL Server 2012 or later version, you could use the LAG analytic function to produce the required result:

SELECT
  ID,
  tableName,
  rowCountDelta = totalRowCount - LAG(totalRowCount, 1, 0) OVER (PARTITION BY tableName
                                                                     ORDER BY Date),
  Date
FROM
  dbo.yourTable
;

The above query assumes that the first entry in a partition is indeed the very first addition of rows. (LAG(totalRowCount, 1, 0) means the absence of the previous totalRowCount will be treated as a zero.) If that cannot be assumed safely in every case and you would prefer to return NULL for first entries, you could use just LAG(totalRowCount) OVER ... instead:

SELECT
  ID,
  tableName,
  rowCountDelta = totalRowCount - LAG(totalRowCount) OVER (PARTITION BY tableName
                                                               ORDER BY Date),
  Date
FROM
  dbo.yourTable
;

If you insist on storing the results in a table, you can just add an INTO clause to the query. A possibly better idea, though, might be to save the query as a view.

Related Question