Function for Getting Interval Dates by days,months,years.
Pass the values as per below in parameter for getting output.
(@EntityId INT, /*( (Not mandatory) can be pass as null or 0) */
@Frequency VARCHAR(40), /*( From this 1)'Daily', 2)'Monthly', 3)'Yearly')*/
@StartDate DATETIME, /*( Start date of month from where range date starts eg.'01/01/2015' or '01-Jan-2015')*/
@EndDateAvailable BIT, /*( '0' for false and '1' for true)*/
@EndDate DATETIME, /*( First date of last month if @EndDateAvailable is true (till this month range will get set))*/
@RepeatedTimes INT, /*( This is for how many records you want to see from date range)*/
@Intervals INT, /*( As per @Frequency how much distance you want to set eg. in monthly you want date after every 2 month then set '2')*/
@DateType VARCHAR(40), /*( From this 1)'FixedDate', 2)'FixedDay')*/
@FixedMOnth INT, /*( Put month number if required)*/
@FixedDATE INT, /*( If @DateType = 'FixedDate' then set date between 1 to 31 (if @DateType is 'FixedDay' then set null))*/
@FixedDay VARCHAR(40), /*( If @DateType = 'FixedDay' then set day between 'Monday' to 'Sunday' (if @DateType is 'FixedDate' then set null))*/
@DayNo INT, /*( If You want perticular date like 4th sunday of every month then set it as '4' with @DateType = 'FixedDay')*/
@FixedMonthName VARCHAR(40)) /*( This is required when you are using @DayNo functionality and want to find for perticaular month eg. 'January')*/
--SELECT
*
FROM funGetIntervalDates(
@EntityId, @Frequency, @StartDate, @EndDateAvailable,@EndDate,@RepeatedTimes,
@Intervals, @DateType, @FixedMOnth, @FixedDATE,@FixedDay,@DayNo,@FixedMonthName)
--Eg. for @RepeatedTimes --> select * from dbo.funGetIntervalDates(null,'Daily','01-Jan-2014',1,'01-Jan-2018',8,1,'FixedDate',4,05,'Monday',2,'January') --(here 8 records btw two dates)
--Eg. for @RepeatedTimes --> select * from dbo.funGetIntervalDates(null,'Daily','01-Jan-2014',1,'01-Jan-2018',10,3,'FixedDate',null,null,null,null,null) --(here 10 records with 3 days interval btw two dates)
--Eg. for @DayNo --> select * from dbo.funGetIntervalDates(null,'Monthly','01-Jan-2015',1,'01-Jan-2016',10,1,'Fixedday',1,2,'Thursday',3,'January') --(here 10 records with every months 3rd 'Thursday' with week number from January)
--SELECT * FROM funGetIntervalDates(@EntityId,@Frequency,@StartDate,@EndDateAvailable,@EndDate,@RepeatedTimes,@Intervals,@DateType,@FixedMOnth,@FixedDATE,@FixedDay,@DayNo,@FixedMonthName)
--Eg. for @RepeatedTimes--> select * from dbo.funGetIntervalDates(null,'Daily','01-Jan-2014',1,'01-Jan-2018',8,1,'FixedDate',4,05,'Monday',2,'January') --(here 8 records btw two dates)
--Eg. for @RepeatedTimes--> select * from dbo.funGetIntervalDates(null,'Daily','01-Jan-2014',1,'01-Jan-2018',10,3,'FixedDate',null,null,null,null,null) --(here 10 records with 3 days interval btw two dates)
--Eg. for @DayNo--> select * from dbo.funGetIntervalDates(null,'Monthly','01-Jan-2015',1,'01-Jan-2016',10,1,'Fixedday',1,2,'Thursday',3,'January') --(here 10 records with every months 3rd 'Thursday' with week number from January)
CREATE FUNCTION [dbo].[FunGetIntervalDates]
(@EntityId INT,/*( (Not mandatory) can be pass as null or 0) */ @Frequency VARCHAR(40),/*( From this 1)'Daily', 2)'Monthly', 3)'Yearly')*/ @StartDate DATETIME,/*( Start date of month from where range date starts eg.'01/01/2015' or '01-Jan-2015')*/ @EndDateAvailable BIT,/*( '0' for false and '1' for true)*/ @EndDate DATETIME,/*( First date of last month if @EndDateAvailable is true (till this month range will get set))*/
@RepeatedTimes INT,/*( This is for how many records you want to see from date range)*/ @Intervals INT,/*( As per @Frequency how much distance you want to set eg. in monthly you want date after every 2 month then set '2')*/ @DateType VARCHAR(40),/*( From this 1)'FixedDate', 2)'FixedDay')*/ @FixedMOnth INT,/*( Put month number if required)*/ @FixedDATE INT,/*( If @DateType = 'FixedDate' then set date between 1 to 31 (if @DateType is 'FixedDay' then set null))*/ @FixedDay VARCHAR(40),/*( If @DateType = 'FixedDay' then set day between 'Monday' to 'Sunday' (if @DateType is 'FixedDate' then set null))*/ @DayNo INT,/*( If You want perticular date like 4th sunday of every month then set it as '4' with @DateType = 'FixedDay')*/ @FixedMonthName VARCHAR(40))/*( This is required when you are using @DayNo functionality and want to find for perticaular month eg. 'January')*/returns @Table TABLE (
EntityInt INT,
Id INT,
Label INT,
RecuringDate DATETIME,
[DayName] VARCHAR(40),
WeekName VARCHAR(40),
Weeks INT,
[year] INT )
AS
BEGIN
DECLARE @MaxRepeat INT = 99
DECLARE @EntriesCount INT
DECLARE @NextDATE DATE
DECLARE @OriginalFreq VARCHAR(40)
SET @NextDATE = Dateadd(dd, @Intervals, @StartDate)
SET @OriginalFreq=@Frequency
IF @Frequency = 'Monthly'
AND @DateType = 'FixedDay'
BEGIN
SET @Intervals=1
SET @Frequency='Daily'
SET @RepeatedTimes=@RepeatedTimes * 366
END
IF @Frequency = 'Yearly'
AND @DateType = 'FixedDay'
BEGIN
SET @Intervals=1
SET @Frequency='Daily'
SET @RepeatedTimes=@RepeatedTimes * 366
END
IF @RepeatedTimes IS NOT NULL
AND @RepeatedTimes > 0
BEGIN
SET @MaxRepeat= @RepeatedTimes
END
DECLARE @tempDATE TABLE
(
id INT IDENTITY(1, 1),
RecuringDATE DATE
)
/* Daily Callucation Start */ IF ( @Frequency = 'Daily' )
BEGIN
SET @EntriesCount = 1
SELECT @NextDATE = Dateadd(dd, @Intervals * -1, @NextDATE)
WHILE ( @EntriesCount <= @MaxRepeat )
BEGIN
IF @EndDate IS NOT NULL
BEGIN
IF( Dateadd(dd, @Intervals, @NextDATE) <= @EndDate )
BEGIN
SELECT @NextDATE = Dateadd(dd, @Intervals, @NextDATE)
INSERT INTO @tempDATE
VALUES (@NextDATE)
END
END
ELSE
BEGIN
SELECT @NextDATE = Dateadd(dd, @Intervals, @NextDATE)
INSERT INTO @tempDATE
VALUES (@NextDATE)
END
SET @EntriesCount=@EntriesCount + 1
END
END
/* Daily Callucation END */ /* Month Callucation Start */ ELSE IF ( @Frequency = 'Monthly' )
BEGIN
IF ( @FixedDATE > 0 )
BEGIN
SET @EntriesCount=1
DECLARE @MM INT
DECLARE @YY INT
DECLARE @CurDATE DATE
SET @MM = Datepart(mm, @NextDATE)
SET @YY = Datepart(yyyy, @NextDATE)
SET @MM=@MM - @Intervals
WHILE ( @EntriesCount <= @MaxRepeat )
BEGIN
/* Check Leap Year or Incorrect DATE start */ SET @MM=@MM + @Intervals
IF( @MM > 12 )
BEGIN
SET @MM=@MM - 12
SET @YY=@YY + 1
END
DECLARE @FD INT
IF( @FixedDATE >= 28
AND @MM = 2 )
BEGIN
IF ( @YY%4 ) > 0
BEGIN
SET @FD=28
END
ELSE
BEGIN
SET @FD=29
END
END
ELSE IF( @FixedDATE > 30
AND ( @MM IN ( 4, 6, 9, 11 ) ) )
BEGIN
SET @FD=30
END
ELSE
BEGIN
SET @FD=@FixedDATE
END
/* Check Leap Year or Incorrect DATE END */ SELECT @CurDATE = CONVERT(DATE, ( CONVERT(VARCHAR(4), @MM) + '-'
+ CONVERT(VARCHAR(4), @FD) + '-'
+ CONVERT(VARCHAR(4), @YY) ))
IF( @CurDATE <= @EndDate )
BEGIN
INSERT INTO @tempDATE
VALUES (@CurDATE)
END
SET @EntriesCount =@EntriesCount + 1
END
END
END
/* Month Callucation END */ /* Yearly Callucation Start */ ELSE IF ( @Frequency = 'Yearly'
AND @DateType = 'FixedDate' )
BEGIN
IF ( @FixedDATE > 0
AND @FixedMOnth > 0 )
BEGIN
SET @EntriesCount=1
DECLARE @year INT
SET @year = Datepart(yyyy, @NextDATE)
SET @year=@year - @Intervals
IF( @FixedDATE > 30
AND @FixedMOnth IN ( 4, 6, 9, 11 ) )
BEGIN
SET @FixedDATE = 30
END
ELSE IF ( @FixedMOnth = 2
AND @FixedDATE >= 28 )
BEGIN
IF( @year%4 ) = 0
BEGIN
SET @FixedDATE =29
END
ELSE
BEGIN
SET @FixedDATE =28
END
END
ELSE IF( @FixedDATE > 30
AND @FixedMOnth IN ( 1, 3, 5, 7,
8, 10, 12 ) )
BEGIN
SET @FixedDATE =31
END
ELSE
BEGIN
SET @FixedDATE =@FixedDATE
END
SELECT @CurDATE = CONVERT(DATE, ( CONVERT(VARCHAR(4), @FixedMOnth) + '-'
+ CONVERT(VARCHAR(4), @FixedDATE) + '-'
+ CONVERT(VARCHAR(4), @year) ))
WHILE ( @EntriesCount <= @MaxRepeat )
BEGIN
SET @year=@year + @Intervals
SELECT @CurDATE = CONVERT(DATE, ( CONVERT(VARCHAR(4), @FixedMOnth) + '-'
+ CONVERT(VARCHAR(4), @FixedDATE) + '-'
+ CONVERT(VARCHAR(4), @year) ))
IF( @CurDATE <= @EndDate )
BEGIN
INSERT INTO @tempDATE
VALUES (@CurDATE)
END
SET @EntriesCount =@EntriesCount + 1
END
END
END
ELSE IF ( @Frequency = 'Yearly'
AND @DateType = 'FixedDay' )
BEGIN
SET @Frequency='Daily'
SET @DateType='FixedDay'
END
IF ( @Frequency = 'Monthly'
AND @DateType = 'FixedDate'
AND @EndDate IS NULL )
BEGIN
SET @EndDate='01-Jan-2099'
END
/* Yearly Callucation END */ IF ( @Frequency = 'Daily'
AND @DateType = 'FixedDay'
AND @OriginalFreq = 'Monthly'
AND @FixedDay <> 'Weekday' )
BEGIN
INSERT @Table
SELECT @EntityId AS Value,
*
FROM (SELECT Rank()
OVER(
ORDER BY RecuringDATE) AS id,
*
FROM (SELECT Rank()
OVER (
partition BY DayName, years
ORDER BY RecuringDATE) AS Label,
*
FROM (SELECT RecuringDATE,
Datename(MM, RecuringDATE) AS DayName,
Datename(dw, RecuringDATE) AS WeekName,
((SELECT Datepart(WEEK, RecuringDATE) - Datepart(WEEK, Dateadd(MM, Datediff(MM, 0, RecuringDATE), 0)) + 1)) AS Weeks,
Datepart(yy, RecuringDATE) AS years
FROM @tempDATE) AS t1
WHERE
WeekName = @FixedDay)t2
WHERE
Label = @DayNo) t3
WHERE
id <= @RepeatedTimes / 366
ORDER BY RecuringDate
END
ELSE IF ( @Frequency = 'Daily'
AND @DateType = 'FixedDay'
AND @OriginalFreq = 'Monthly'
AND @FixedDay = 'Weekday' )
BEGIN
INSERT @Table
SELECT @EntityId AS Value,
*
FROM (SELECT Rank()
OVER(
ORDER BY RecuringDATE) AS id,
*
FROM (SELECT Rank()
OVER (
partition BY DayName, years, Weekname
ORDER BY RecuringDATE) AS Label,
*
FROM (SELECT RecuringDATE,
Datename(MM, RecuringDATE) AS DayName,
Datename(dw, RecuringDATE) AS WeekName,
((SELECT Datepart(WEEK, RecuringDATE) - Datepart(WEEK, Dateadd(MM, Datediff(MM, 0, RecuringDATE), 0)) + 1)) AS Weeks,
Datepart(yy, RecuringDATE) AS years
FROM @tempDATE) AS t1
WHERE
WeekName NOT IN ( 'Saturday', 'Sunday' ))t2
WHERE
Label = @DayNo) t3
WHERE
id <= @RepeatedTimes / 366
ORDER BY RecuringDate
END
ELSE IF ( @Frequency = 'Daily'
AND @DateType = 'FixedDay'
AND @OriginalFreq = 'Yearly'
AND @FixedDay <> 'Weekday' )
BEGIN
INSERT @Table
SELECT @EntityId AS Value,
*
FROM (SELECT Rank()
OVER(
ORDER BY RecuringDATE) AS id,
*
FROM (SELECT Rank()
OVER (
partition BY DayName, years
ORDER BY RecuringDATE) AS Label,
*
FROM (SELECT RecuringDATE,
Datename(MM, RecuringDATE) AS DayName,
Datename(dw, RecuringDATE) AS WeekName,
((SELECT Datepart(WEEK, RecuringDATE) - Datepart(WEEK, Dateadd(MM, Datediff(MM, 0, RecuringDATE), 0)) + 1)) AS Weeks,
Datepart(yy, RecuringDATE) AS years
FROM @tempDATE) AS t1
WHERE
WeekName = @FixedDay)t2
WHERE
Label = @DayNo
AND DayName = @FixedMonthName) t3
WHERE
id <= @RepeatedTimes / 366
ORDER BY RecuringDate
END
ELSE IF ( @Frequency = 'Daily'
AND @DateType = 'FixedDay'
AND @OriginalFreq = 'Yearly'
AND @FixedDay = 'Weekday' )
BEGIN
INSERT @Table
SELECT @EntityId AS Value,
*
FROM (SELECT Rank()
OVER(
ORDER BY RecuringDATE) AS id,
*
FROM (SELECT Rank()
OVER (
partition BY DayName, years, Weekname
ORDER BY RecuringDATE) AS Label,
*
FROM (SELECT RecuringDATE,
Datename(MM, RecuringDATE) AS DayName,
Datename(dw, RecuringDATE) AS WeekName,
((SELECT Datepart(WEEK, RecuringDATE) - Datepart(WEEK, Dateadd(MM, Datediff(MM, 0, RecuringDATE), 0)) + 1)) AS Weeks,
Datepart(yy, RecuringDATE) AS years
FROM @tempDATE) AS t1
WHERE
WeekName NOT IN ( 'Saturday', 'Sunday' ))t2
WHERE
1 = 1
AND Label = @DayNo
AND DayName = @FixedMonthName) t3
WHERE
id <= @RepeatedTimes / 366
ORDER BY RecuringDate
END
ELSE
BEGIN
INSERT @Table
SELECT @EntityId AS Value,
Rank()
OVER(
ORDER BY RecuringDATE) AS id,
Rank() OVER (
partition BY Datename(MM, RecuringDATE), Datename(yyyy, RecuringDATE), Datename(dw, RecuringDATE)
ORDER BY RecuringDATE) AS Label,
RecuringDATE,
Datename(MM, RecuringDATE) AS DayName,
Datename(dw, RecuringDATE) AS WeekName,
((SELECT Datepart(WEEK, RecuringDATE) - Datepart(WEEK, Dateadd(MM, Datediff(MM, 0, RecuringDATE), 0)) + 1)) AS Weeks,
Datename(yyyy, RecuringDATE) AS year
FROM @tempDATE
ORDER BY RecuringDate
END
RETURN;
END
GO 
