CODEWITHSUNDEEP
sqlsql-server
8indie8
8indie8

Reputation: 71

Current day's counts vs Yesterday's counts difference into a table

The main "issue": I am inserting the current day's row counts from every table into a table. The main goal is to have a table which has the current day's vs yesterday's counts difference for each day. What is essential that to know that the current days row total is or not than the yesterday's total.

Example :

+----------+-------------+--------+
|  Table   |    Date     | Count  |
+----------+-------------+--------+
| Table 1  | 2020-01-01  |     50 |
| Table 1  | 2020-01-02  |    150 |
| Table 1  | 2020-01-03  |    565 |
+----------+-------------+--------+

and this is what I would like to have as a final product:

+----------+-------------+--------------------+
|  Table   |    Date     | Count (difference) |
+----------+-------------+--------------------+
| Table 1  | 2020-01-01  |                 50 |
| Table 1  | 2020-01-02  |                100 |
| Table 1  | 2020-01-03  |                415 |
+----------+-------------+--------------------+

I was thinking in a kind of solution which is similar to the Rolling Forecast, but after a while I stuck and still thinking that what can be the best approach for this.

This is the current state of my "code":

DECLARE @SQL_RecordCounting nvarchar(MAX) = 

(
SELECT 
    STRING_AGG(CAST( N'SELECT N' AS nvarchar(MAX)) +QUOTENAME(name,'''') +N' AS [DatabaseName],  -- Database
    T.NAME AS [TableName], -- Table 
    0-P.ROWS AS [RowCount], -- Counting the rows
    GETDATE() as [Datetime] -- To have a history 
FROM ' +
    QUOTENAME(name) +
    N'.sys.tables T
LEFT JOIN ' +
    QUOTENAME(name) +
    N'.sys.partitions P ON T.object_id = P.OBJECT_ID 
WHERE T.is_ms_shipped = 0 -- excluding the MS provided tables 
AND P.rows <> 0 -- only the zeros
GROUP BY T.Name,
         P.Rows'
         , NCHAR(10) + N' UNION ALL ' + NCHAR(10)
) + NCHAR(10) + N'ORDER BY Name;'
FROM sys.databases
WHERE database_id > 4  -- excluding the sys dbs  
AND name not in ('DELTA','work','stage_dev') -- dbs which are out of scope
)
;

--INSERT INTO [ABC].[dbo].[TablesRecords]
exec sys.sp_executesql  @SQL_RecordCounting

Upvotes: 1

Views: 1326

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269953

Just keep the totals in your table TablesRecords and query it using:

select tr.*,
       (tr.count - lag(tr.count, 1, 0) over (partition by tr.table order by tr.date)) as count_difference
from [ABC].[dbo].[TablesRecords] tr;

Upvotes: 1

SQLpro
SQLpro

Reputation: 5131

Use :

COALESCE([Count] - LAG([Count]) OVER(ORDER BY [Date]), [Count])

In the select clause of your query.

Upvotes: 3

Related Questions