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
