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

Rate

4.5 (2)

Share

Share

Rate

4.5 (2)