So…you’ve got a fancy Business Intelligence (BI) solution? Predictive Analytics or perhaps Actionable Analytics? Ever thought about the right time to contact your (potential) customers?
SQLTimer might be a handy addition to your BI Suite!
Like a timer for your lighting, it checks every hour whether it’s the right moment to execute jobs for categories like:
– BusinessHour: Indicates whether or not it’s the right time to contact your customer during business hours (included in the SQLTimer script)
Other additional time schedules which are downloadable:
– Twitter: Indicates whether or not it’s the right time to contact your Twitter users
– FaceBook: Indicates whether or not it’s the right time to contact your FaceBook users
– LinkedIn: Indicates whether or not it’s the right time to contact your LinkedIn users
– GooglePlus: Indicates whether or not it’s the right time to contact your Google+ users
– Pinterest: Indicates whether or not it’s the right time to contact your Pinterest users
In this blogpost, I’ll be diving into the SQLTimer itself and the free time schedule called ‘BusinessHour’ (included in the script).
Download the complete script free on:
SQLTimer – How to install
How it works:
Just like the timer for your lightbulb, it is configured on an hourly basis:
The values indicate whether it is a bad, better or even the best time to contact your business on a weekly basis:
BusinessHour values:
0) = Outside of business hours
1) = Inside of business hours
2) = Preferred hours
Important note: SQLTimer is setup on a day-to-day basis and thus you might tweak the time schedule a bit for local holidays or other important days in the year:
SQLTimer objects:
(An existing database is required)
Let’s have a look at all the SQLTimer objects:
*** Tables ***
timer.TimerLogging
This table contains the logging (when a time schedule was executed (StartTime/EndTime) and what value the time schedule had)
SQLTimer’s logging in timer.TimerLogging:
timer.TimeSchedule
This table actually contains the time schedule itself
timer.TimeScheduleConfiguration
This table is all about the configuration
SQLTimer’s configuration in timer.TimeScheduleConfiguration
In case you would like to change the name of the schema, don’t forget to change the configuration settings:
*** Stored Procedures ***
timer.usp_Hourly_Timer
This SP is the timer itself and needs to be scheduled in SQL Agent to be run on an hourly basis
timer.usp_Select_TimeSchedule
You might want to use the timer.usp_Select_TimeSchedule() stored procedure to analyze the time schedule for the coming week:
timer.usp_Execute_BusinessHour_Jobs
This SP will execute the preferred jobs (for example: T-SQL code, SP’s or SQL Agent Jobs) for the time schedule ‘BusinessHour’
Here’s how the timer.usp_Execute_BusinessHour_Jobs looks like.
You need to add your T-SQL code, SP’s or SQL Agent Jobs here that need to be executed if the @TimeScheduleValue reaches a certain value.
*** Install the SQLTimer ***
So far about the SQLTimer itself, it’s time to install it!
1) Download the T-SQL script here: SQLTimer – How to install
2) Unzip the ZIP-file and open the *.sql file in SSMS or SSDT
3) Change the database name and uncomment the T-SQL code:
4) Execute the T-SQL script
Output of the script:
5) Create a SQL Agent Job (Execute T-SQL statements) which executes the timer.usp_Hourly_Timer stored procedure every hour
6) Don’t forget to update the time schedule with your local (company) holidays 🙂
Table: timer.TimeSchedule
Websites:
SQLTimer
BumbleBI IT
Clint Huijbers' Blog 