CREATE TABLE ScheduleMaster
(
rcrPK int NOT NULL PRIMARY KEY,
rcrName varchar(500) NOT NULL,
rcrStartDate date NOT NULL,
rcrEndDate date NULL,
rcrPatternType tinyint NOT NULL,
rcrPattern varchar(5000) NULL,
rcrInterval int NOT NULL,
rcrDayOfMonth int NOT NULL,
rcrWeekNo int NOT NULL,
rcrDayNo int NOT NULL,
rcrMonthOfYear varchar(50) NULL,
rcrDayOfYear int NOT NULL,
rcrTimeOccurence time(7) NOT NULL,
rcrNoOfOccurence int NULL,
rcrActive tinyint NOT NULL
)
GO
INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName], [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (1, N'Yearly wise Schedule with First pattern', CAST(0x07240B00 AS Date), CAST(0xAE400B00 AS Date), 4, NULL, 2, 0, 0, 0, N'01,05,10', 25, CAST(0x07004EB119B90000 AS Time), 0,1)
INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName], [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (2, N'Yearly wise Schedule with Second pattern', CAST(0x07240B00 AS Date), CAST(0xAE400B00 AS Date), 4, NULL, 2, 0, 2, 3, N'01,05,10', 0, CAST(0x07004EB119B90000 AS Time), 0,1)
INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName], [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (3, N'Monthly Schedule with First pattern', CAST(0x15240B00 AS Date), CAST(0xED410B00 AS Date), 3, NULL, 1, 8, 0, 0, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0, 1)
INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName], [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (4, N'Monthly Schedule with Second pattern', CAST(0x15240B00 AS Date), CAST(0xED410B00 AS Date), 3, NULL, 3, 0, 3, 4, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0, 1)
INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName], [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (5, N'Weekly Schedule', CAST(0x07240B00 AS Date), CAST(0xCC400B00 AS Date), 2, N'MON,FRI', 1, 0, 0, 0, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0, 1)
INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName], [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (6, N'Daily Schedule', CAST(0x07240B00 AS Date), CAST(0x90400B00 AS Date), 1, NULL, 1, 0, 0, 0, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0,1)
INSERT [dbo].[ScheduleMaster] ([rcrPK], [rcrName], [rcrStartDate], [rcrEndDate], [rcrPatternType], [rcrPattern], [rcrInterval], [rcrDayOfMonth], [rcrWeekNo], [rcrDayNo], [rcrMonthOfYear], [rcrDayOfYear], [rcrTimeOccurence], [rcrNoOfOccurence], [rcrActive])
VALUES (7, N'Custom Schedule', CAST(0x07240B00 AS Date), CAST(0x90400B00 AS Date), 5, N'01-JAN-2011,08-JAN-2011,10-JAN-2011,05-OCT-2013', 1, 0, 0, 0, NULL, 0, CAST(0x07004EB119B90000 AS Time), 0, 1)
GO
Create Procedure dbo.spGetSchedule
(
@FromDt Date ,
@ToDt Date
)
As
Begin
Set Nocount On;
--// Temporary table for handle final result.
Declare @tmp Table
(
dt DateTime not null,
Name Varchar(500) not null
)
--// Declaration section
Declare
-- Variables for Cursor looping
@Name Varchar(500),
@StartDate Datetime,
@EndDate Datetime,
@PatternType TinyInt,
@Pattern Varchar(5000),
@Interval Int, -- Interval period
@DayOfMonth Int, -- Monthwise scheduling on which day.
@WeekNo Int, -- Month/Year wise Second pattern, indicates which week 1'st,2'nd,3'rd,4'th
@DayNo Int, -- Sunday - 1,Monday - 2,Tuesday - 3,Wednesday -4,Thursday - 5,FrNameay - 6,Saturday - 7
@MonthOfYear Varchar(50), -- Year wise scheduling on which month , 1-Jan,2-Feb.. etc.
@DayOfYear Int, -- Year wise scheduling on which month of day
@TimeOccurence Time,
@NoOfOccurence TinyInt,
------------------------------
-- Variables for Prgm flow
@firstofmonth Datetime, -- Optional variable for taking first date of the month in pgrm flow
@CurDate Datetime, -- Optional variable for taking date in pgrm flow
@Pos Int = 0, -- To keep Position of week or Custom type.
@intYearMonth Int, --for take month value from month pattern in year wise shedule.
-------------------------------
-- Code pending using with following variable
@LastOccurence Int,
@LastProcessed Datetime
-------------------------------
Declare Cur Cursor Fast_Forward Read_Only For
Select
rcrName
,rcrStartDate
,rcrEndDate
,rcrPatternType
,rcrPattern
,rcrInterval
,rcrDayOfMonth
,rcrWeekNo
,rcrDayNo
,rcrMonthOfYear
,rcrDayOfYear
,rcrTimeOccurence
,rcrNoOfOccurence
From ScheduleMaster Where
(
(@FromDt Between rcrStartDate And rcrEndDate) Or
(@ToDt Between rcrStartDate And rcrEndDate) Or
(rcrStartDate Between @FromDt And @ToDt) Or
(rcrEndDate Between @FromDt And @ToDt)
) And (rcrActive = 1)
Open Cur
Fetch Next From Cur Into
@Name
,@StartDate
,@EndDate
,@PatternType
,@Pattern
,@Interval
,@DayOfMonth
,@WeekNo
,@DayNo
,@MonthOfYear
,@DayOfYear
,@TimeOccurence
,@NoOfOccurence
While @@FETCH_STATUS <> -1
Begin
If @StartDate < @FromDt Set @StartDate = @FromDt
If @EndDate > @ToDt Set @EndDate = @ToDt
--// Main loop schedule.
While (@StartDate <= @EndDate)
Begin
If @PatternType = 5 --// Custom option
Begin
If Cast(@StartDate As Date) = Cast(substring(@Pattern,@Pos + 1,11) As Date) --// if date matching with given custom date.
Begin
Insert Into @tmp Select @StartDate,@Name
Set @Pos = @Pos + 12 --// increasing position.
End
Set @StartDate = DATEADD(day,1,@StartDate) --// Adding oneday day with current date.
End
Else If @PatternType = 4 --// Year wise schedule
Begin
--if DATEADD(YEAR,@interval,@StartDate) <= @EndDate -- Next date less than current date
--begin
If /*@MonthOfYear <> 0 and */ @DayOfYear > 0 --// First pattern for year shedule -- Every fixed month & Date
Begin
--// Logic:
-- Date set to first date of the current year then adding with given month and date.
-- Eg, '15 nov 2011' => '01 jan 2011' => given month march (3), => '01 mar 2011' => given Day 20 => '20 Mar 2011'
While (@Pos <= Len(@MonthOfYear))
Begin
Set @intYearMonth = Cast(Substring(@MonthOfYear,@Pos + 1,2) As Int)
Set @CurDate = Dateadd(Month,0,Dateadd(Day,@DayOfYear-1,Dateadd(Month,@intYearMonth-1,Dateadd(Month,-Month(Dateadd(Day,-Day(@StartDate)+1,@StartDate))+1,Dateadd(Day,-Day(@StartDate)+1,@StartDate)))))
If @CurDate > @EndDate Break -- // Some time date will over come given end date so that time we want to break up the postion loop.
Insert Into @tmp Values(@CurDate,@Name)
Set @Pos = @Pos + 3
Set @StartDate = Dateadd(Day,1,@CurDate) --// Adding oneday day with current date.
End
End
Else
Begin
--// Logic:
-- In first Step, Date set to first date of the current month of year.
-- Eg, '15 nov 2011' => '01 nov 2011'
While (@Pos <= Len(@MonthOfYear))
Begin
Set @intYearMonth = Cast(Substring(@MonthOfYear,@Pos + 1,2) As Int)
Set @firstofmonth = Dateadd(Day,@DayOfYear,Dateadd(Month,@intYearMonth-1,Dateadd(Month,-Month(Dateadd(Day,-Day(@StartDate)+1,@StartDate))+1,Dateadd(Day,-Day(@StartDate)+1,@StartDate))))
--// Second Step, add requested week with '01 nov 2011' -- Suppose 2'nd weeek then (2-1) * 7 => 7 with '01 nov 2011' then => date will be '08 nov 2011'
--// Third step ,No of days to add = (7 + which day (Sunday - 1,Monday - 2,Tuesday - 3,Wednesday -4,Thursday - 5,FrNameay - 6,Saturday - 7) - Weekday Of first date) % 7 with second step result.
-- Second week of monday november month -- 2(monday)
-- Eg, '08 nov 2011' + ((7+2) - 3) = 9-3 = 6%7 = 6 = '14 nov 2011' Second monday
Select @CurDate = Dateadd(Day,((7+@DayNo)-Datepart(DW,t.mydate))%7,t.mydate) From
(Select Dateadd(Day,(@WeekNo-1)*7 ,@firstofmonth) As mydate )t
If @CurDate > @EndDate Break -- // Some time date will over come given end date so that time we want to break up the postion loop.
Insert Into @tmp Values(@CurDate,@Name)
Set @Pos = @Pos + 3
Set @StartDate = Dateadd(Day,1,@CurDate) --// Adding oneday day with current date.
End
End
--end
--// Setting added month as current month for next process
Set @StartDate = Dateadd(Year,@interval,Dateadd(Day,-Day(@StartDate)+1,Dateadd(Month,-Month(@StartDate)+1,@StartDate)))
Set @Pos = 0
End
Else If @PatternType = 3 --// Month wise schedule
Begin
--insert into @tmp Select DATEADD(MONTH,@interval,@StartDate),@Name
If @DayOfMonth = 0
Begin
--// year wise second pattern formula logic used.
Select @firstofmonth = Dateadd(Day,-Day(@StartDate)+1,@StartDate)
Select @CurDate = Dateadd(Day,((7+@DayNo)-Datepart(DW,t.mydate))%7,t.mydate) From
(Select Dateadd(Day,(@WeekNo-1)*7 ,@firstofmonth) As mydate )t
If @CurDate <= @EndDate
Insert Into @tmp values (@CurDate,@Name)
End
Else
Begin
--// current date take as first date of the month and add with given day.
Set @CurDate = Dateadd(Day,@DayOfMonth-1,Dateadd(Day,-Day(@StartDate)+1,@StartDate))
If @CurDate <= @EndDate
Insert Into @tmp Select @CurDate,@Name
End
If @DayOfMonth > 0
Begin
Set @StartDate = Dateadd(Month,@interval,@CurDate)
End
Else
Begin
Set @StartDate = Dateadd(Month,@interval,Dateadd(Day,-Day(@StartDate)+1,@StartDate))
End
End
Else If @PatternType = 1 --// Day wise schedule
Begin
If @StartDate <= @EndDate
Begin
--// simple logic -- day add with current date considering interval period.
Insert Into @tmp Select @StartDate,@Name
End
Set @StartDate = Dateadd(Day,@interval,@StartDate)
End
Else If @PatternType = 2 --// Week wise schedule
Begin
While (@Pos <= Len(@Pattern)) --// looping Position less than given week type.
Begin
If @StartDate > @EndDate Break -- // Some time date will over come given end date so that time we want to break up the postion loop.
If Left(Upper({fn Dayname (@StartDate)}),3) = Substring(@Pattern,@Pos + 1,3) --// if day character of the current month matching with given week string.
Begin
If @Pos = 0 Set @CurDate = @StartDate
Insert Into @tmp Select @StartDate,@Name
Set @Pos = @Pos + 4 --// increasing position.
End
Set @StartDate = Dateadd(Day,1,@StartDate)
End
Set @StartDate = Dateadd(Week,@interval,@CurDate)
Set @Pos = 0 --// We need to clear the position for next level loop.
End
End
Fetch Next From Cur Into
@Name
,@StartDate
,@EndDate
,@PatternType
,@Pattern
,@Interval
,@DayOfMonth
,@WeekNo
,@DayNo
,@MonthOfYear
,@DayOfYear
,@TimeOccurence
,@NoOfOccurence
End
Close cur
Deallocate cur
--// Final Result
Select dt + @TimeOccurence As Date,Name From @tmp
End
GO
No comments:
Post a Comment