June 17, 2021 at 2:11 pm
Dear Group:
We have a process that loads the record count of tables into a table so that we can compare records. We do this, because the process that that loads our tables isn't consistent, so this will allow us to look on any given day, "Table XYZ has no records, so their process failed". However, what we would like to do is get a list of tables where the numbers are the same for today vs. yesterday.
I feel there has to be an easier way to accomplish this, as right now I have a CURSOR going through this table. It gets the listing of tables, then runs a query to get the number for today, then another query to get the numbers from yesterday and compares them.
This process works, but I am just wondering if there is a way to do this in a single query.
June 17, 2021 at 2:56 pm
This process works, but I am just wondering if there is a way to do this in a single query.
The answer is 'almost certainly'.
Now, if you will take the time to provide us with some sample DDL, sample data in the form of INSERT statements and desired results based on the sample data provided, someone here will probably provide a working solution.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 17, 2021 at 4:31 pm
LAG function ?
https://docs.microsoft.com/en-us/sql/t-sql/functions/lag-transact-sql?view=sql-server-ver15
June 18, 2021 at 11:54 am
Here is the table with the historical record counts:
CREATE TABLE [dbo].[HistoricalRecordCounts](
[ID] [int] IDENTITY(1,1) NOT NULL,
[DatabaseName] [nvarchar](255) NOT NULL,
[SchemaName] [nvarchar](255) NOT NULL,
[TableName] [nvarchar](255) NOT NULL,
[RecordCount] [int] NOT NULL,
[ReportDate] [datetime] NOT NULL
) ON [PRIMARY]
The data inside this table is as follows:
ID DatabaseName SchemaName TableName RecordCount ReportDate
1 DB1 Schema1 Table1 70404511 2021-06-17 14:43:54.527
2 DB1 Schema1 Table2 152452 2021-06-17 14:43:54.527
3 DB1 Schema2 Table3 356630 2021-06-17 14:43:54.527
So everyday, we dump the record counts of these tables into this historical table, so that we can have proof of the days when tables were not loaded correctly.
So what I am trying to do in a single query, is compare the record counts in the historical table (which is a day offset) with the live record count in the real tables. We know the real tables, as the historical table tracks the "DataBaseName, SchemaName and TableName".
So if on 6-17-2021, DB1.Schema1.Table1 had a record count of 70404511 as shown dbo.HistorcalRecordCounts, but 6-18-2021 in the actual table, the record counts are also 70404511, there is a good chance for our system that this table failed to update, as the odds of the record counts being exact two days in a row is about zero (live and one day offset). It isn't a perfect system, but we are unable to create any database objects (such as Last Updated column, triggers, etc) to do this.
I hope this makes sense.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply