January 18, 2016 at 12:20 pm
ScottPletcher (1/18/2016)
SQL Agent schedules all of it jobs from msdb, and I don't see anything that looks like a calendar table in it
That's because Agent is not a database-resident system. Real-time schedulers have different requirements than what the OP has presented and, like Agent, are typically implemented outside a database engine where they can keep closer track of clock-ticks and leverage things like multi-threading more easily. Agent is no exception here.
Keep in mind, too, you've also created a potential maintenance problem using a calendar table when you don't really need one.
Please explain.
Also, how do schedule something for every 4 hours using a calendar table??
Apples and oranges...not currently presented as a requirement. See my earlier comment about real-time scheduling with respect to the code being database-resident.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 18, 2016 at 1:25 pm
Hey guys first thanks for all the replies. A calendar table was not something I considered before but I'll for sure take a look at that now. I can't really see any situation where I'd need to be have ability to schedule in hour frequencies so that's not a requirement. Currently I have daily, weekly, monthly and yearly frequencies with daily only being used in a small handful of plans. Most are using weekly or monthly based rotations. Also computing overhead is something I am not overly concerned with. The stored proc script runs at 3am daily when basically no one is using the system and none of scheduled backups are being run.
This is what I'm using for my plans that run on weekly rotations. I was wondering if there might be something similar I could use for my monthly rotations that bring the week of the month into the equation.
Given today's date this is what I have as running for my weekly rotation plans.
SELECT DATEADD(week,@WeeklyFrequency,(SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()),@WeekdayToGenerate)))AS NewDueDate
So basically for a PM that I have configured to come out every 3 weeks on tuesday it ends up being SELECT DATEADD(week,3,(SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()),1)))AS NewDueDate This returns Feb 9th 2016 which is the tuesday 3 weeks from now. I'm just not sure how to structure the query for my monthly plans to take in the week of the month as a parameter.
January 18, 2016 at 8:43 pm
Two functions leveraging the calendar table for your consideration:
This one answers the question in your last post. You will pass in the year, month, week number of the month and the day name and the calendar table does the rest:
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.get_day_in_nth_week_in_month')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION dbo.get_day_in_nth_week_in_month;
GO
CREATE FUNCTION dbo.get_day_in_nth_week_in_month
(
@Year SMALLINT,
@MonthNumber TINYINT,
@WeekInMonthNumber TINYINT,
@DayName VARCHAR(9)
)
RETURNS TABLE
AS
RETURN
(
SELECT day_dt,
day_nm
FROM dbo.calendar
WHERE cal_yr_nbr = @Year
AND cal_mth_in_yr_nbr = @MonthNumber
AND cal_wk_in_mth_nbr = @WeekInMonthNumber
AND day_nm = @DayName
);
GO
-- get the Tuesday in the third week of February, 2015
SELECT *
FROM dbo.get_day_in_nth_week_in_month(2015, 2, 3, 'Tuesday');
This one will retrieve a specific day n-weeks from now, starting counting from the next full week where Monday is the beginning (note the + 1 in the WHERE-clause). I was a bit confused by you saying 3 Tuesdays from now (from Monday 1/18) would be 2/9. If tomorrow is the 1st Tuesday from now, then the 26th is the 2nd and 2/2 is the third Tuesday from now. But, if you're only starting counting from a fresh week then yes, 2/9 is the "Tuesday three weeks from now, starting from the beginning of next week."
IF EXISTS ( SELECT *
FROM sys.objects
WHERE object_id = OBJECT_ID(N'dbo.get_day_n_weeks_from_now')
AND type IN (N'FN', N'IF', N'TF', N'FS', N'FT') )
DROP FUNCTION dbo.get_day_n_weeks_from_now;
GO
CREATE FUNCTION dbo.get_day_n_weeks_from_now
(
@WeeklyFrequency TINYINT,
@DayName VARCHAR(9)
)
RETURNS TABLE
AS
RETURN
(WITH cte
AS (
SELECT ROW_NUMBER() OVER (ORDER BY day_dt) row_num,
day_dt,
day_nm
FROM dbo.calendar
WHERE day_dt >= CAST(GETDATE() AS DATE)
AND day_nm = @DayName
)
SELECT *
FROM cte
WHERE cte.row_num = @WeeklyFrequency + 1
);
GO
SELECT *
FROM dbo.get_day_n_weeks_from_now(3, 'Tuesday');
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 19, 2016 at 12:21 pm
This relies on @@DateFirst = 7 (Sunday) and is based on the logic at this site: http://sqllessons.com/second_tuesday_of_the_month.html
DECLARE @FirstDayOfMonth_InThreeMonths DateTime2
, @SecondTuesdayOfMonth_InThreeMonths DateTime2
SELECT @FirstDayOfMonth_InThreeMonths = DATEADD(mm, DATEDIFF(mm,0,GETDATE())+3,0)
SELECT @SecondTuesdayOfMonth_InThreeMonths = DATEADD(D, ((17 - DATEPART(dw,@FirstDayOfMonth_InThreeMonths)) % 7 ) + 7, @FirstDayOfMonth_InThreeMonths)
If it has to be on one line you can do that, but it just takes away the very little legibility I have on this one.
January 19, 2016 at 2:42 pm
It's not nearly that complicated to get the nth given day of any month:
DECLARE @SecondTuesdayOfMonth_InThreeMonths datetime2
--set to last possible day = 14th of month, then ...
SET @SecondTuesdayOfMonth_InThreeMonths = DATEADD(DAY, 13, DATEADD(MONTH, DATEDIFF(MONTH,0,GETDATE()) + 3, 0))
--... backup to the actual Tuesday.
SET @SecondTuesdayOfMonth_InThreeMonths = DATEADD(DAY, -DATEDIFF(DAY, 1 /*known Tuesday base date*/, @SecondTuesdayOfMonth_InThreeMonths)%7, @SecondTuesdayOfMonth_InThreeMonths)
SELECT @SecondTuesdayOfMonth_InThreeMonths
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 19, 2016 at 3:04 pm
Orlando Colamatteo (1/18/2016)
CREATE FUNCTION dbo.get_day_in_nth_week_in_month
(
@Year SMALLINT,
@MonthNumber TINYINT,
@WeekInMonthNumber TINYINT,
@DayName VARCHAR(9)
)
RETURNS TABLE
AS
RETURN
(
SELECT day_dt,
day_nm
FROM dbo.calendar
WHERE cal_yr_nbr = @Year
AND cal_mth_in_yr_nbr = @MonthNumber
AND cal_wk_in_mth_nbr = @WeekInMonthNumber
AND day_nm = @DayName
);
GO
-- get the Tuesday in the third week of February, 2015
SELECT *
FROM dbo.get_day_in_nth_week_in_month(2015, 2, 3, 'Tuesday');
Of course that code doesn't specify the date key, so it will do a full table scan. Again, vastly too much overhead when two very simple arithmetic calcs will give you the nth day of any month.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 19, 2016 at 7:14 pm
Maybe it's just me but that does not look as intuitive as this to get the 2nd Tuesday in April 2016:
-- get the second Tuesday of the month
SELECT day_dt,
day_nm
FROM dbo.calendar
WHERE cal_yr_nbr = 2016
AND cal_mth_in_yr_nbr = 4
AND day_nm = 'Tuesday'
ORDER BY day_dt
OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 19, 2016 at 7:17 pm
ScottPletcher (1/19/2016)
Of course that code doesn't specify the date key, so it will do a full table scan. Again, vastly too much overhead when two very simple arithmetic calcs will give you the nth day of any month.
Sorry I did not mention it but you should feel free to add indexes to this table to support any data access pattern you utilize. Since this table is static there is insert or update overhead.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 20, 2016 at 7:40 am
Orlando Colamatteo (1/19/2016)
ScottPletcher (1/19/2016)
Of course that code doesn't specify the date key, so it will do a full table scan. Again, vastly too much overhead when two very simple arithmetic calcs will give you the nth day of any month.Sorry I did not mention it but you should feel free to add indexes to this table to support any data access pattern you utilize. Since this table is static there is insert or update overhead.
But that's yet more buffer space for a table that never needed read to begin with. Sure, the cost of building/maintaining such indexes is trivial, but not the additional loss of buffer space.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 20, 2016 at 7:50 am
ScottPletcher (1/20/2016)
Orlando Colamatteo (1/19/2016)
ScottPletcher (1/19/2016)
Of course that code doesn't specify the date key, so it will do a full table scan. Again, vastly too much overhead when two very simple arithmetic calcs will give you the nth day of any month.Sorry I did not mention it but you should feel free to add indexes to this table to support any data access pattern you utilize. Since this table is static there is insert or update overhead.
But that's yet more buffer space for a table that never needed read to begin with. Sure, the cost of building/maintaining such indexes is trivial, but not the additional loss of buffer space.
Don't oversell it. It is a small amount of space. For me it comes down to a system design decision and a usability issue and will readily trade the buffer space we are talking about. This extends into other areas like holiday and other business calendars. Under certain conditions I would take on the date-math but if I am not compelled to do so a calendar table just makes more sense.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 20, 2016 at 7:53 am
Orlando Colamatteo (1/20/2016)
ScottPletcher (1/20/2016)
Orlando Colamatteo (1/19/2016)
ScottPletcher (1/19/2016)
Of course that code doesn't specify the date key, so it will do a full table scan. Again, vastly too much overhead when two very simple arithmetic calcs will give you the nth day of any month.Sorry I did not mention it but you should feel free to add indexes to this table to support any data access pattern you utilize. Since this table is static there is insert or update overhead.
But that's yet more buffer space for a table that never needed read to begin with. Sure, the cost of building/maintaining such indexes is trivial, but not the additional loss of buffer space.
Don't oversell it. It is a small amount of space. For me it comes down to a system design decision and a usability issue and will readily trade the buffer space we are talking about. This extends into other areas like holiday and other business calendars. Under certain conditions I would take on the date-math but if I am not compelled to do so a calendar table just makes more sense.
Why wouldn't you keep holiday data in a column in your calendar table?
Edit: Come to think of it, wouldn't fiscal year also benefit your table as well?
January 20, 2016 at 8:31 am
Orlando Colamatteo (1/20/2016)
ScottPletcher (1/20/2016)
Orlando Colamatteo (1/19/2016)
ScottPletcher (1/19/2016)
Of course that code doesn't specify the date key, so it will do a full table scan. Again, vastly too much overhead when two very simple arithmetic calcs will give you the nth day of any month.Sorry I did not mention it but you should feel free to add indexes to this table to support any data access pattern you utilize. Since this table is static there is insert or update overhead.
But that's yet more buffer space for a table that never needed read to begin with. Sure, the cost of building/maintaining such indexes is trivial, but not the additional loss of buffer space.
Don't oversell it. It is a small amount of space. For me it comes down to a system design decision and a usability issue and will readily trade the buffer space we are talking about. This extends into other areas like holiday and other business calendars. Under certain conditions I would take on the date-math but if I am not compelled to do so a calendar table just makes more sense.
Holiday calendars are necessary -- there's not arithmetic calc to tell you which days are holidays in a given country.
But every has 7 days period. Basically it's just a crutch / lazy coding, which is sometimes ok if it's clearer, but these types of calcs often occur many thousands of times a day, counting all code combined, and I've seen errors when calendar tables ran out of dates. And the more dates you add, of course the more overhead it is when you do scan the table hundreds / thousands of times a day.
Edit: I've seen calendar tables used for very simple things, from down to the next calendar day (!) to listing all Mondays (or whatever day) for a given year. Even if you prefer to use the calendar table to get the first matching day, there's no reason to use it to get the rest.
Also, we have large, consolidated servers with multiple instances and, of course, limited RAM divided among them. At least all SQL instances are no longer on virtualized machines. But either way, SQL just loves RAM, so the more we can save the better.
SQL DBA,SQL Server MVP(07, 08, 09) "Money can't buy you happiness." Maybe so, but it can make your unhappiness a LOT more comfortable!
January 20, 2016 at 10:37 am
Ed Wagner (1/20/2016)
Orlando Colamatteo (1/20/2016)
ScottPletcher (1/20/2016)
Orlando Colamatteo (1/19/2016)
ScottPletcher (1/19/2016)
Of course that code doesn't specify the date key, so it will do a full table scan. Again, vastly too much overhead when two very simple arithmetic calcs will give you the nth day of any month.Sorry I did not mention it but you should feel free to add indexes to this table to support any data access pattern you utilize. Since this table is static there is insert or update overhead.
But that's yet more buffer space for a table that never needed read to begin with. Sure, the cost of building/maintaining such indexes is trivial, but not the additional loss of buffer space.
Don't oversell it. It is a small amount of space. For me it comes down to a system design decision and a usability issue and will readily trade the buffer space we are talking about. This extends into other areas like holiday and other business calendars. Under certain conditions I would take on the date-math but if I am not compelled to do so a calendar table just makes more sense.
Why wouldn't you keep holiday data in a column in your calendar table?
Edit: Come to think of it, wouldn't fiscal year also benefit your table as well?
You could keep holiday dates in a column but it you need to add a new calendar then you need to add a new column. In some shops business calendars (of which holiday calendars are a type) need to be definable through a UI therefore normalizing them into a separate table makes more sense since we would not want to allow a UI to add columns to our calendar table dynamically. It's an implementation detail and mileage may vary.
I like having Fiscal Year in the calendar table but since it differs from one company to the next I leave it out of the version I share with folks.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 20, 2016 at 10:42 am
ScottPletcher (1/20/2016)
Holiday calendars are necessary -- there's not arithmetic calc to tell you which days are holidays in a given country.
Actually, if memory serves, there are algorithms for calculating all U.S. holidays. Not sure about all holidays in all countries but chances are there will be exceptions. Either way, if you have the need for speed why not just hardcode the calendar-dates into a function?
Also, we have large, consolidated servers with multiple instances and, of course, limited RAM divided among them. At least all SQL instances are no longer on virtualized machines. But either way, SQL just loves RAM, so the more we can save the better.
Regarding RAM, I said it before and I'll say it again, you have a point. It's a choice and both ways have merit. In some circumstances date-math makes sense but neither approach is to be avoided or adopted in all cases.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
January 21, 2016 at 2:57 pm
I just had a chance to dig up a table-size script. For every 11K rows (30 years of dates) the calendar table I posted occupies 2.75 MB of space in my database.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 15 posts - 16 through 29 (of 29 total)
You must be logged in to reply to this topic. Login to reply