Generate Calender Recurring Dates Automatically with SQL Job

• Download scheduler scripts - 17.2 KB

Introduction

This tip will help to automatically create recurring entries in schedule table.

Background

From frontend, the schedule with 'No EndDate' was created manually for one year (refer to the screenshot above).
The sample screenshot is for the scheduler set as:

• Recurrence Pattern occurring Daily only on weekdays.
• Recurrence Range with Start date 13^th Feb 2014 and having 'No EndDate'.
  To avoid infinite entries in PatrolScheduleDetail child table, the actual entries here from Front end were created only for one year. Hence, I need to create a mechanism which will automatically create future one year entries.

SQL job will automatically calculate new entries as per the 'Recurrence Pattern': Hourly, Daily, Weekly, Monthly, Yearly. For this purpose, I have written a job and sps in SQL which will create new entries for next year, for the schedule which is about to expire with 'No EndDate'.
To get a quick idea about this tip, I urge you to go through the 'Useful links' which I have given at the bottom of this article.

Using the Code

To use these scripts, a job needs to be setup in SQL which will run the main sp 'usp_GenerateNextYearSchedulePlannerMain' and this will call internally another sps according to Recurrence pattern set for the schedule type.

//
// Example :- As explained above, in backend following process will occur. 
//Sample seed data with scripts is available in the attached files.
// 
// Here Patrol_Schedule parent table will have single entry as provided 
// 'Patrol_Schedule_Insert_Daily_EveryWeekDay.sql' script
// insert into [Patrol_Schedule] 
//  values
// (46, 1, '2013-03-27 00:00:00.000', '2013-03-27 00:30:00.000', 
// 1, 1, 0, 0, 2, 1, 127, 1, 1, 1, 0, '2013-03-27 00:00:00.000', '2014-03-26 00:00:00.000', 1)
//  
//  Patrol_Schedule_Detail child table will have total 261 entries for this schedule for 1 year.
//  For eg. Starting from 13th Feb 2014 till 12th Feb 2015.
//  
//  As my problem statement was to create automatic entries of creating similar 
//  entries for the next year from 2015. I have created these scripts which will 
//  run automatically through job.
//  
//  Attached scripts are :- 
//  Sample seed data Insert scripts
//  - Patrol_Schedule_Table
//  - Patrol_Schedule_Detail_Table
//  - Patrol_Schedule_Insert_Daily_EveryWeekDay
//  
//  Job 
//  - SeedScript_CreateJobCreateNextYearSchedule
//  
//  Stored Procedures required for job
//  - usp_GenerateNextYearSchedulePlannerMain
//    -- usp_GenerateNextYearSchedule_HourlyPattern_Insert
//    -- usp_GenerateNextYearSchedule_DailyPattern_Insert
//    -- usp_GenerateNextYearSchedule_WeeklyPattern_Insert
//    -- usp_GenerateNextYearSchedule_MonthlyPattern_Insert
//    -- usp_GenerateNextYearSchedule_YearlyPattern_Insert
//    --- usp_Patrol_Schedule_Detail_InsertTable
//   
//  These sps will create automatic entries for one year.

Points of Interest

This is my first tip. Though I am not a very good writer, I hope that you find this useful. If you find this tip correct/incorrect, express this fact by rating the tip as you see fit. In the end, you're very welcome at any moment to freely contribute to this project by suggesting improvements, or by submitting code or other materials.

Thanks.

Useful Links

• How to Use SQL Server to Select Records for a Scheduled Date
• How to Generate Recurring Events in the Database
• Why should I consider using an auxiliary calendar table?
• Robyn Page's SQL Server DATE/TIME Workbench

It was my days efforts to write these sps with the help of the above links I mentioned. So I hope it will help someone to save their time with my tip. Thanks to my wife Geeta who encouraged me to write this stuff and to CodeProject for posting my first tip.

History

• 15^th May, 2013: Original post