UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS

CREATE function dbo.UFN_CAMPAIGNPRIORITYREPORT_GETPERIODS
(
  @START datetime,
  @END datetime,
  @PERIODOPTION tinyint
)
returns @PERIODINFO table
(
  SEQUENCE int not null,
  PERIODYEAR int not null,
  STARTDATE datetime not null primary key clustered,
  ENDDATE datetime not null
)
as
begin
  select
    @START = dbo.UFN_DATE_GETEARLIESTTIME(@START),
    @END = dbo.UFN_DATE_GETLATESTTIME(@END);

  With PERIODS_CTE (SEQUENCE, PERIODYEAR, STARTDATE, ENDDATE) as
  (
    select
      case @PERIODOPTION
        when 1 then datepart(mm, @START) 
        when 2 then datepart(qq, @START) 
        else 1
      end,
      datepart(yyyy, @START),
      @START,
      case @PERIODOPTION
        when 1 then 
          case when @END < dateadd(dd, -1, dateadd(mm, 1, @START + 1 - day(@START)))
            then @END
            else dateadd(dd, -1, dateadd(mm, 1, @START + 1 - day(@START)))
          end
        when 2 then
          case when @END < dateadd(ms, -3, dateadd(qq,datediff(qq, 0,@START)+ 1, 0))
            then @END
            else dateadd(ms, -3, dateadd(qq,datediff(qq, 0,@START)+ 1, 0)) 
          end
        else
          case when @END < dateadd(ms, -3, dateadd(yyyy,datediff(yyyy, 0,@START)+ 1, 0))
            then @END
            else dateadd(ms, -3, dateadd(yyyy, datediff(yyyy, 0,@START)+ 1, 0)) 
          end
        end

      union all

      select
        case @PERIODOPTION
          when 1 then datepart(mm, dateadd(dd, 1, p.enddate)) 
          when 2 then datepart(qq, dateadd(dd, 1, p.enddate)) 
          else datediff(yyyy, @START, dateadd(dd, 1, p.enddate)) + 1
        end,
        datepart(yyyy, dateadd(dd, 1, p.enddate)),
        dateadd(dd, 1, p.enddate),
        case @PERIODOPTION
          when 1 then 
            case when @END < dateadd(dd, -1, dateadd(mm, 1, dateadd(dd, 1, p.enddate) + 1 - day(dateadd(dd, 1, p.enddate)))) 
              then @END
              else dateadd(dd, -1, dateadd(mm, 1, dateadd(dd, 1, p.enddate) + 1 - day(dateadd(dd, 1, p.enddate))))
            end
          when 2 then
            case when @END < dateadd(ms, -3, dateadd(qq,datediff(qq, 0,dateadd(dd, 1, p.enddate))+ 1, 0))
              then @END
              else dateadd(ms, -3, dateadd(qq,datediff(qq, 0,dateadd(dd, 1, p.enddate))+ 1, 0))
            end
          else
            case when @END < dateadd(dd, -1, dateadd(yyyy, 1, dateadd(dd, 1, p.enddate) + 1 - day(dateadd(dd, 1, p.enddate)))) 
              then @END
              else dateadd(dd, -1, dateadd(yyyy, 1, dateadd(dd, 1, p.enddate) + 1 - day(dateadd(dd, 1, p.enddate))))
            end
          end
      from PERIODS_CTE p
      where dateadd(dd, 1, p.enddate) < @END
  )
  insert @PERIODINFO
    select
      SEQUENCE,
      PERIODYEAR,
      --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

      cast(STARTDATE as date),
      --The "GETLATESTTIME" date function has been inlined here for performance (the part with "dateadd(ms, -003...")...

      dateadd(ms, -003, dateadd(d, 1, cast(cast(ENDDATE as date) as datetime)))
    from PERIODS_CTE;

  return;
end