stored procedure - do something for every day between x and y

[cornercuttin]

i am relatively new to sql server, and even more new to stored procedures (I have done quite a bit of SQL, but it has all been "1 liner" kind of things).

basically, i have a table that is getting quite a bit of updates every day, and each update gives me a timestamp of when the insert was done.

i need to be able to say, in layman's terms, "FOR every day BETWEEN today AND X (where X is a timestamp of a previous day) DO ....insert another SQL statement here...."

does anyone have any ideas off the top of their head? i don't know much about the sql language as far as if statements and for statements and whatnot. any clue what i could do?

thanks

 

[cornercuttin]

and i also guess i should say, this stored procedure will be called by a "job", and will be run every day.

the thing is, if i could be assured the "job" was called every day, then this would be a lot easier. but because this system is a bit volatile, i cannot assume that the sql server will be alive all 24 hours of each day for 365 days of the year.

so if the sql server is down for 2 days, my script has to know to go back and redo calculations with those previous 2 days in mind.

 

[SQLSister]

Have you considered a trigger which will run whenever data is inserted (and/or updated /deleted)into the table?

Also, just so you know the timestamp is a data type in SQL Server which actually has nothing to do with the date and time. So when discussing SQL Server it is best not to use the term timestamp unless you are referring to a filed of that data type. It wasn't confusing in this instance, but there are times when it is. Also sometimes people think they are getting date data when they use a timestamp field. Hopefully you are actually using a datetime field to store the dates?

Questions about posting. See faq183-874

 

[cornercuttin]

yeah. there are actually year, month, day, hour, and second fields for this table.

the trigger wouldnt work. the table for last year had about 9 million insertions. thousands or even tens of thousands of inserts a day.

i am starting to figure it out a little. if i get it figured out, i will post it so people can help me make it a little better.

 

[SQLSister]

we have millions of records and use triggers all the time for this type of thing.

Questions about posting. See faq183-874

 

[cornercuttin]

do you really think triggers would be ok for this? we get roughly 25 thousand insertions a day into this 1 table. there are about 25 tables in this one database, and there are also another 10 or so databases running on this ms sql server.

with that many insertions, are triggers not going to bog down the system? there are lots of stored procedures that are taking place, and i dont have any clue what any of the other databases are doing on this system. i am only dealing with 1 of 10 or so...

basically, i am doing count(*) statements. i havent really done any stored procedure stuff; this is my first go at it for a long time, so i havent even tested this properly yet. here is what i came up with...

/*** Declare variables ***/
declare @1count int
declare @11count int
declare @12count int
declare @31count int
declare @32count int
declare @121count int
declare @211count int
declare @212count int
declare @221count int
declare @year_time int
declare @month_time int
declare @day_time int
declare @hour_time int
declare @month_diff int
declare @last_run_date DATE
declare @my_counter int
/*** Get the last time this stored procedure was run ***/
SET @last_run_date = SELECT last_run_date FROM status_table
/*** So the deal is if the last run date was, lets say, 3 months ago, we need to retally  ***/
/***   the statistics for those months, as well as the stats so far for this month.  ***/
/***   So what we are going to do here is get the difference in months, and then for each of ***/
/***   those months, we are going to get the stats.  ***/
/***   The months are also zero based. ***/
IF (MONTH(@last_run_date) <> MONTH(CURRENT_TIMESTAMP))
BEGIN
	/*** Set the counter ***/
	SET @my_counter = 0
	SET @month_diff = DATEDIFF(mm, @last_run_date, GETDATE())
	/*** While the counter is less than the difference in months ***/
	WHILE (@my_counter < @month_diff)
	BEGIN
		/*** Get the month for this year ***/
		SET @year_time = YEAR(DATEADD(mm, @last_run_date, @my_counter))
		SET @month_time = MONTH(DATEADD(mm, @last_run_date, @my_counter))
		SET @my_hour = 0
		/** For each hour of the day.  This is zero based ***/
		WHILE (@hour_time < 24)
		BEGIN
		   SET @1count = SELECT COUNT(*) FROM master 
			WHERE MY_YEAR = @year_time AND MY_MONTH = 
			@month_time AND MY_HOUR = @hour_time 
			AND MY_VAR = 1
		   SET @11count = SELECT COUNT(*) FROM master 
			WHERE MY_YEAR = @year_time AND MY_MONTH = 
			@month_time AND MY_HOUR = @hour_time 
			AND MY_VAR = 11
		   SET @12count = SELECT COUNT(*) FROM master
			WHERE MY_YEAR = @year_time AND MY_MONTH = 
			@month_time AND MY_HOUR = @hour_time 
			AND MY_VAR = 12
		   SET @31count = SELECT COUNT(*) FROM master 
			WHERE MY_YEAR = @year_time AND MY_MONTH = 
			@month_time AND MY_HOUR = @hour_time 
			AND MY_VAR = 31
		   SET @32count = SELECT COUNT(*) FROM master 
			WHERE MY_YEAR = @year_time AND MY_MONTH = 
			@month_time AND MY_HOUR = @hour_time 
			AND MY_VAR = 32
		   SET @121count = SELECT COUNT(*) FROM master 
			WHERE MY_YEAR = @year_time AND MY_MONTH = 
			@month_time AND MY_HOUR = @hour_time 
			AND MY_VAR = 121
		   SET @211count = SELECT COUNT(*) FROM master 
			WHERE MY_YEAR = @year_time AND MY_MONTH = 
			@month_time AND MY_HOUR = @hour_time 
			AND MY_VAR = 211
		   SET @212count = SELECT COUNT(*) FROM master 
			WHERE MY_YEAR = @year_time AND MY_MONTH = 
			@month_time AND MY_HOUR = @hour_time 
			AND MY_VAR = 212
		   SET @221count = SELECT COUNT(*) FROM master 
			WHERE MY_YEAR = @year_time AND MY_MONTH = 
			@month_time AND MY_HOUR = @hour_time 
			AND MY_VAR = 221
		   INSERT INTO _Hourly (MY_YEAR, MY_MONTH, MY_HOUR, 
			1Count, 11Count, 12Count, 31Count, 32Count, 
			121Count, 211Count, 212Count, 221Count) VALUES 
			(@year_time, @month_time, @hour_time, @1count, 
			@11count, @31count, @32count, @121count, 
			@211count, @212count, @221count)
		   @hour_time = @hour_time + 1
		END
		/*** Increment the counter ***/
		@my_counter = @my_counter + 1
	END
	/*** Update the status table when we are done ***/
	UPDATE status_table SET last_run_date = GETDATE()
ELSE
	SET @month_time = MONTH(CURRENT_TIMESTAMP)
	SET @year_time = YEAR(CURRENT_TIMESTAMP)
	SET @my_hour = 0
	/*** For all of the hours of the day...zero based ***/
	WHILE (@hour_time < 24)
	BEGIN
	   SET @1count = SELECT COUNT(*) FROM master WHERE 
		MY_YEAR = @year_time AND MY_MONTH = @month_time 
		AND MY_HOUR = @hour_time AND MY_VAR = 1
	   SET @11count = SELECT COUNT(*) FROM master WHERE 
		MY_YEAR = @year_time AND MY_MONTH = @month_time 
		AND MY_HOUR = @hour_time AND MY_VAR = 11
	   SET @12count = SELECT COUNT(*) FROM master WHERE 
		MY_YEAR = @year_time AND MY_MONTH = @month_time 
		AND MY_HOUR = @hour_time AND MY_VAR = 12
	   SET @31count = SELECT COUNT(*) FROM master WHERE 
		MY_YEAR = @year_time AND MY_MONTH = @month_time 
		AND MY_HOUR = @hour_time AND MY_VAR = 31
	   SET @32count = SELECT COUNT(*) FROM master WHERE 
		MY_YEAR = @year_time AND MY_MONTH = @month_time 
		AND MY_HOUR = @hour_time AND MY_VAR = 32
	   SET @121count = SELECT COUNT(*) FROM master WHERE 
		MY_YEAR = @year_time AND MY_MONTH = @month_time 
		AND MY_HOUR = @hour_time AND MY_VAR = 121
	   SET @211count = SELECT COUNT(*) FROM master WHERE 
		MY_YEAR = @year_time AND MY_MONTH = @month_time 
		AND MY_HOUR = @hour_time AND MY_VAR = 211
	   SET @212count = SELECT COUNT(*) FROM master WHERE 
		MY_YEAR = @year_time AND MY_MONTH = @month_time 
		AND MY_HOUR = @hour_time AND MY_VAR = 212
	   SET @221count = SELECT COUNT(*) FROM master WHERE 
		MY_YEAR = @year_time AND MY_MONTH = @month_time 
		AND MY_HOUR = @hour_time AND MY_VAR = 221
	   INSERT INTO _Hourly (MY_YEAR, MY_MONTH, MY_HOUR, 
		1Count, 11Count, 12Count, 31Count, 32Count, 
		121Count, 211Count, 212Count, 221Count) VALUES 
		(@year_time, @month_time, @hour_time, @1count, 
		@11count, @31count, @32count, @121count, @211count, 
		@212count, @221count)
	   @hour_time = @hour_time + 1
	END	
	UPDATE status_table SET last_run_date = GETDATE()
END