Click here to Skip to main content
Click here to Skip to main content

Generate Calender Recurring Dates Automatically with SQL Job

, 16 May 2013
Rate this:
Please Sign up or sign in to vote.
I had a requirement to create automatic date entries into Scheduler SQL table. Whenever a Schedule with RecurrenceRange was set with 'No EndDate', the dates will automatically get added from backend for next year through a job.

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 13th 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

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

  • 15th May, 2013: Original post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

Pavan Thakur
Software Developer (Senior)
India India
MCP, MCAD.NET, MCSD.NET in .NET technology. MCTS in .NET Framework 3.5, ASP.NET Applications.
Post Graduate in IT (Symbiosis).
 
Doing programming into .Net Technology from year 2004.
Worked extensively on both windows and web platform.
In my projects I have worked on Asp.Net(1.1, 2.0, 3.5), C#, VB.Net and Sql Server(2000,2005, 2008).
HTML5, JScript, CSS, XML, AJAX, WCF & Web Services, WPF, MVC.
I love programming and like to share my knowledge.
Cheers Smile | :)
Follow on   Google+

Comments and Discussions

 
QuestionRequired Info PinmemberMember 106543738-Mar-14 11:53 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Mobile
Web02 | 2.8.140827.1 | Last Updated 16 May 2013
Article Copyright 2013 by Pavan Thakur
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid