Calculating Totals

[PaulHamill]

All

I have written a Stored Procedure to Run on a daily basis to record the number of records in each of the tables within my database. The ouput of this script is to a table and is as follows...

Date TableName NoOfRecords
23-04-2003 TableA 10
23-04-2003 TableB 50
24-04-2003 TableA 15
24-04-2003 TableB 60

I want to be able to work out the daily increase in records of each of the tables. Does anyone have any ideas,

Thanking you all in advance

 

[Kudel]

Hi

Like this

select t1.date,(t1.RecCount - (Select t2.RecCount from YourTable t2 where t2.date = dateadd(dd,-1,t1.date) and t2.Name = t1.Name))
from YourTable t1


-Kudel

 

[bertrandkis]

I think the easiest way will be to create a trigger on your output table. Add a column say DailyIncrease int
Then create this trigger on your table:
create TRIGGER UpdDailyInc on YourOutputTable for update
as
begin
--this trigger would raise an error if more than one row ----is updated at once,so the second part of the if ensures ---that the trigger does not raises the error,the first part
--ensures that the trigger only fires if the NoOfRecords
--column is updated
if (update(NoOfRecords)and ((select count(*)from deleted)<2))
begin
declare @newvalue int,@oldvalue int,@tblname varchar(20)
select @newvalue=NoOfRecords,@tblname=Tablename from inserted
select @oldvalue=NoOfRecords from deleted
update YourOutputtable set DailyIncrease =@oldvalue-@newvalue where DATE=convert(varchar(15),getdate(),105) And Tablename=@tblname
end
end

**Note: I have used the Convert function to convert the date in the where clause to the date format that you have shown in your table, this assumes that the datatype of your
date column is varchar. If the datatype of your date column is datetime do not use the convert function this will generate an error.

This should work fine or you may need to modify it a bit.

Black belt sequelist.