I have to do data monitoring in the database. Due to security features I am not given admin rights.

I have to create a matrix where I have to count the rows the database has on a unique key everyday.

So currently I run the query everyday:

select Count(Uniquekey)
from TableName 
where RecordCreatedDate < '2021-09-14' 
order by RecordCreatedDate desc

Then I create a table in Excel where I write

Date_For_Data Date_data_checked Count 
09/09/2021     09/09/2021       100
10/09/2021     10/09/2021       120 
11/09/2021     11/09/2021       130
12/09/2021     12/09/2021       134

I run this query 4 times. But next day I shall do it 5 times and on. Can I create a matrix in SQL itself. It shall save lots of time.

CodePudding user response：

You could group by the RecordCreatedDate:

SELECT   RecordCreatedDate, COUNT(Uniquekey)
FROM     TableName 
GROUP BY RecordCreatedDate

CodePudding user response：

You can have a job daily running, which tracks unique key for the previous day as given below:

DECLARE @table table(uniquekey int unique, recordcreateddate date);

insert into @table 
values (1,'2021-09-11'),(2,'2021-09-11'),(3,'2021-09-12'),(4,'2021-09-13');

SELECT cast(dateadd(dd,-1,getdate()) as date) as Date_For_Data, cast(getdate() as date) as Date_data_checked, count(uniquekey) as [Count]   FROM @table
where RecordCreatedDate = cast(dateadd(dd,-1,getdate()) as date)