Need to calculate nth day of nth month
-
I have a table of tasks. Each task has a due date and is configured based on a user defined schedule. (ie. first monday of each month, tuesdays every 3 weeks etc...). On the due date the task generates (via seperate procedure) and I need to calculate the next due date for the task.
I've figured out how to calculate the next due date for tasks that use a schedule defined by weeks and a specific day. For example one of my tasks is due every 3 weeks on the tuesday of that third week. Here is what I am currently using to calculate that next due date. I'm assuming the first day of the week is monday with a setting of 0, tuesday is 1 etc...
SELECT DATEADD(week,3,(SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()),1)))AS NewDueDate
Where I seem to be getting stuck is on my tasks that are configured to come out on a specific week of the month. So for example if I had a task that was set to be generated every 3 months on the second tuesday of that month. Any idea how I'd do that? I've seen some udf's out there that kinda do something along those lines but I'm hoping sql 2012 has something built in where I might be able to do it a little cleaner. Any help would be greatly appreciated.
-
mike.jacobs12 (1/15/2016)
I have a table of tasks. Each task has a due date and is configured based on a user defined schedule. (ie. first monday of each month, tuesdays every 3 weeks etc...). On the due date the task generates (via seperate procedure) and I need to calculate the next due date for the task.I've figured out how to calculate the next due date for tasks that use a schedule defined by weeks and a specific day. For example one of my tasks is due every 3 weeks on the tuesday of that third week. Here is what I am currently using to calculate that next due date. I'm assuming the first day of the week is monday with a setting of 0, tuesday is 1 etc...
SELECT DATEADD(week,3,(SELECT DATEADD(wk, DATEDIFF(wk,0,GETDATE()),1)))AS NewDueDate
Where I seem to be getting stuck is on my tasks that are configured to come out on a specific week of the month. So for example if I had a task that was set to be generated every 3 months on the second tuesday of that month. Any idea how I'd do that? I've seen some udf's out there that kinda do something along those lines but I'm hoping sql 2012 has something built in where I might be able to do it a little cleaner. Any help would be greatly appreciated.
What does the table look like that holds your schedule information? If you can post the ddl and a few rows of sample data this can be done pretty easily.
For things like every 3 weeks on Tuesday it should be simple. You said it sets the next run date when the task executes. That one would be nothing more complicated than DATEADD(WEEK, 3, getdate()) on the date you execute the task.
For the 2nd Monday of the month it isn't too complicated but without the datatypes and such it is dicey how to help with the code.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/ -
I think these "calculations" would become simple lookups on a calendar table using ROW_NUMBER() with PARTITION BY and ORDER BY.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Once we get the DDL, it could go in a number of directions.
-
True. I haven't heard anything that cannot be persisted yet, but you never know.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Simple mathematical calcs could give you any of those dates, with no need for I/O against a table. But I'm also not attempting actual code without existing data definitions/DDL.
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!
-
ScottPletcher (1/16/2016)
Simple mathematical calcs could give you any of those dates, with no need for I/O against a table. But I'm also not attempting actual code without existing data definitions/DDL.I think you are overselling the I/O a bit. It would be a corner case to not want nth day stuff in a calendar table, e.g. nth_day_of_week_in_month to show that this is the 3rd Saturday of the month.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Orlando Colamatteo (1/16/2016)
ScottPletcher (1/16/2016)
Simple mathematical calcs could give you any of those dates, with no need for I/O against a table. But I'm also not attempting actual code without existing data definitions/DDL.I think you are overselling the I/O a bit. It would be a corner case to not want nth day stuff in a calendar table, e.g. nth_day_of_week_in_month to show that this is the 3rd Saturday of the month.
It's more efficient to avoid I/O when you can. For calendar-based days/weeks, you don't need I/O at all. If you have special business-based days/weeks, you might. It also depends on how often this is run. If it's run hundreds of times a day, any I/O is obviously magnified.
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!
-
ScottPletcher (1/16/2016)
It's more efficient to avoid I/O when you can.There could be a trade off for CPU and coding effort. If you wrapped the calculation into an iTVF (which we would want to) you're still talking a JOIN, APPLY or sub-query to get the result.
Like I said I think you are overselling the I/O hit but for the sake of discussion with a calendar table containing only ~11K rows for every 30 years of dates that is used often per a strategy you'd effectively be talking about a memory-resident table with a data-access pattern based on a date, the unique clustered key, making I/O practically nil after the initial load into the BP. Chances are decent that the entire process the OP is describing would benefit from leveraging a calendar table making any column on that table easy to incorporate.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
Orlando Colamatteo (1/16/2016)
ScottPletcher (1/16/2016)
It's more efficient to avoid I/O when you can.There could be a trade off for CPU and coding effort. If you wrapped the calculation into an iTVF (which we would want to) you're still talking a JOIN, APPLY or sub-query to get the result.
Like I said I think you are overselling the I/O hit but for the sake of discussion with a calendar table containing only ~11K rows for every 30 years of dates that is used often per a strategy you'd effectively be talking about a memory-resident table with a data-access pattern based on a date, the unique clustered key, making I/O practically nil after the initial load into the BP. Chances are decent that the entire process the OP is describing would benefit from leveraging a calendar table making any column on that table easy to incorporate.
Disk I/O is always the most expensive operation for a db server. And if the calendar table is memory resident, that means some other data isn't. There's no such thing as "free" I/O. The calcs as described can be done with simple arithmetic, the cost of which measured in ns not ms as for I/O.
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!
-
ScottPletcher (1/16/2016)
Orlando Colamatteo (1/16/2016)
ScottPletcher (1/16/2016)
It's more efficient to avoid I/O when you can.There could be a trade off for CPU and coding effort. If you wrapped the calculation into an iTVF (which we would want to) you're still talking a JOIN, APPLY or sub-query to get the result.
Like I said I think you are overselling the I/O hit but for the sake of discussion with a calendar table containing only ~11K rows for every 30 years of dates that is used often per a strategy you'd effectively be talking about a memory-resident table with a data-access pattern based on a date, the unique clustered key, making I/O practically nil after the initial load into the BP. Chances are decent that the entire process the OP is describing would benefit from leveraging a calendar table making any column on that table easy to incorporate.
Disk I/O is always the most expensive operation for a db server. And if the calendar table is memory resident, that means some other data isn't. There's no such thing as "free" I/O. The calcs as described can be done with simple arithmetic, the cost of which measured in ns not ms as for I/O.
If you're system is so tight you need to worry about a calendar table taking up space in your BP then you have bigger problems to worry about than whether to calculate the nth day of the month on the fly or look it up.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
My apologies guys. I had to run out of town unexpectedly and I don't have all my stuff with me so I'll try and give you a basic outline of the table. Essentially this is for a preventive maintenance system. The table I am working with is the table that holds all the plans and task instructions. On a nightly basis I have a stored proc that runs and generates the tasks that are due on that particular day. The script basically inserts an instance of the task instructions into another table and then it updates the nextduedate for when that plan is set to be generated next. I am using the default day settings so (0=Monday, Tuesday = 1 etc...) I could be making all this more complicated than it really needs to be but I have to keep in mind that the tasks aren't always generated the first time on their set day of the week. For example a user might create a new plan and have it generate immediately but after generation the plan should revert to the schedule he/she has laid out. Hopefully the information have provided is enough. Thanks for all the responses thus far.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[tblScheduledTasks](
[PlanID] [int] NOT NULL,
[TaskInstructions] [varchar](4000) NOT NULL,
[DateLastGenerated] [datetime] NULL,
[NextDueDate] [datetime] NULL,
[WeekdayToGenerate] [smallint] NOT NULL,
[MonthlyFrequency] [smallint] NOT NULL,
[WeekToGenerate] [smallint] NOT NULL,
CONSTRAINT [PK_tblScheduledTasks] PRIMARY KEY CLUSTERED
(
[PlanID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
INSERT [dbo].[tblScheduledTasks] ([PlanID], [TaskInstructions], [DateLastGenerated], [NextDueDate], [WeekdayToGenerate], [MonthlyFrequency], [WeekToGenerate]) VALUES (5434, N'Some sample task instructions', CAST(N'2016-01-16 00:00:00.000' AS DateTime), CAST(N'2016-03-15 00:00:00.000' AS DateTime), 1, 2, 3)
INSERT [dbo].[tblScheduledTasks] ([PlanID], [TaskInstructions], [DateLastGenerated], [NextDueDate], [WeekdayToGenerate], [MonthlyFrequency], [WeekToGenerate]) VALUES (5435, N'Some sample task instructions', CAST(N'2016-01-16 00:00:00.000' AS DateTime), CAST(N'2016-02-11 00:00:00.000' AS DateTime), 3, 1, 2)
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'ID for the task plan' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblScheduledTasks', @level2type=N'COLUMN',@level2name=N'PlanID'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Task inistructions to generate' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblScheduledTasks', @level2type=N'COLUMN',@level2name=N'TaskInstructions'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date an instance of the task plan was last generated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblScheduledTasks', @level2type=N'COLUMN',@level2name=N'DateLastGenerated'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Date the task plan will be generated next' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblScheduledTasks', @level2type=N'COLUMN',@level2name=N'NextDueDate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Which day of the week to generated the task plan on' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblScheduledTasks', @level2type=N'COLUMN',@level2name=N'WeekdayToGenerate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'How many elapsed months before the task plan is next generated' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblScheduledTasks', @level2type=N'COLUMN',@level2name=N'MonthlyFrequency'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'Which week in the month will the should the task plan be generated. 1=1st week, 2=2nd week etc....5=last week of month' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblScheduledTasks', @level2type=N'COLUMN',@level2name=N'WeekToGenerate'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'tblScheduledTasks'
GO
-
I'm going to deviate from the calendar discussion for a moment. I've worked with preventive maintenance systems before, so something in your design caught my eye. You have an nvarchar(4000) for the instructions, but I don't know if that's going to be sufficient. In fact, I would consider creating a child table for the instructions. The tasks would be in the parent table and the instructions would be in a child table with a foreign key to the parent. This would enable you to have multiple steps for a single task and also assign the order of the tasks. Depending on how the system will be used, this could become necessary.
For example, if the user is going preventive maintenance on a boring mill or a Bridgeport, the work instructions will consist of many steps and they can be significant. If you consider larger machinery, the maintenance can take a day or more to complete.
Here's an example of what I mean for the tables. Please note that I don't mean for this to be complete at all. It's just a way to illustrate what I described.
CREATE TABLE dbo.Tasks (ID integer not null identity (1, 1),
constraint Tasks_PK primary key (ID),
Code varchar(8) not null,
Name varchar(64) not null,
--your schedule columns,
EntryDate datetime not null default getdate(),
UpdateDate datetime not null);
CREATE TABLE dbo.TaskInstructions (
ID integer not null identity (1, 1),
constraint TasksInstructions_PK primary key (ID),
TaskID integer not null,
constraint TasksInstructions_Tasks_FK
foreign key (TaskID)
references dbo.Tasks(ID),
TaskSequence integer not null,
Instructions nvarchar(4000),
EntryDate datetime not null default getdate(),
UpdateDate datetime not null);
You might also want to separate the actual schedule definitions from the tasks themselves. You could have a table of schedules where you define "First Tuesday of the Month", "First Day of the Month", "Every 3 Months" and so on. Then the task could be assigned a schedule and a DateDue column of the Task could be populated. There are undoubtedly many ways to define a scheduler. I don't know your audience, but in the manufacturing world, they tend to like codes and predefined schedules they can assign to preventive maintenance jobs.
I hope this helps.
-
Here is what a query using my calendar table would look like:
SELECT TOP 1t.PlanID,
t.TaskInstructions,
t.DateLastGenerated,
t.NextDueDate,
DATENAME(dw, t.WeekdayToGenerate) AS dtnam,
t.WeekdayToGenerate,
t.MonthlyFrequency,
t.WeekToGenerate,
c.day_dt AS looked_up_next_due_date
FROM dbo.tblScheduledTasks t
JOIN dbo.calendar c ON t.WeekdayToGenerate + 1 = c.cal_day_in_wk_nbr
AND t.WeekToGenerate = c.cal_wk_in_mth_nbr
WHERE c.day_dt >= CAST(GETDATE() AS DATE)
ORDER BY c.day_dt;
The TOP 1, ORDER BY and >= in the WHERE-clause work together to handle the scenario where you want to find the next occurrence of an event before the day of that event.
Scott knows all the benefits of these calendar tables so I am speaking to the converted and I agree he has a point. If you need to squeeze every last nanosecond out of your system then look into calculating everything on the fly and compare performance with using a calendar table. The calendar table is useful beyond just the problem case you showed so I wanted to introduce it in case you weren't familiar with the concept. I simply cannot imagine building a scheduler-system in SQL without one.
I have attached my calendar table definition and population code in case you want to give it a spin.
EDIT: reduced image size
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato -
SQL Agent schedules all of it jobs from msdb, and I don't see anything that looks like a calendar table in it.
Keep in mind, too, you've also created a potential maintenance problem using a calendar table when you don't really need one. Also, how do schedule something for every 4 hours using a calendar table??
Edit: Calendar tables certainly have valid uses, but for custom dates, not for routine scheduling.
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!
Viewing 15 posts - 1 through 15 (of 29 total)
You must be logged in to reply to this topic. Login to reply