USP_REPORT_ELEMENTACTIVITY_GETSTARTENDDATES

CREATE procedure dbo.USP_REPORT_ELEMENTACTIVITY_GETSTARTENDDATES(
  @DATETYPE nvarchar(36) = '6',
  @STARTDATE datetime = null output, 
  @ENDDATE datetime = null output
)
as
set nocount on;
declare @CURRENTDATE datetime = dbo.UFN_DATE_GETEARLIESTTIME(getdate())
declare @DATETYPECODE tinyint
declare @FISCALYEARID uniqueidentifier

if len(@DATETYPE) > 0
begin
  if len(@DATETYPE) = 1
    set @DATETYPECODE = cast(@DATETYPE as tinyint)
  else
    set @FISCALYEARID = cast(@DATETYPE as uniqueidentifier)
end

if @FISCALYEARID is not null
  select @STARTDATE = MIN(STARTDATE), @ENDDATE = MAX(ENDDATE) from dbo.GLFISCALPERIOD FP
  where FP.GLFISCALYEARID = @FISCALYEARID
if @DATETYPECODE = 0 -- This fiscal year

begin
  set @STARTDATE = null
  set @ENDDATE = null
  select @STARTDATE = MIN(STARTDATE), @ENDDATE = MAX(ENDDATE) from dbo.GLFISCALPERIOD FP
  where FP.GLFISCALYEARID = 
    (select top 1 GLFISCALYEARID from GLFISCALPERIOD P 
    where @CURRENTDATE between P.STARTDATE and P.ENDDATE)
end
if @DATETYPECODE = 1 -- Last fiscal year

begin
  set @STARTDATE = null
  set @ENDDATE = null
  select @STARTDATE = MIN(FP.STARTDATE), @ENDDATE = MAX(FP.ENDDATE) from dbo.GLFISCALYEAR FY
  inner join dbo.GLFISCALPERIOD FP on FP.GLFISCALYEARID = FY.ID
  where (FY.YEARSEQUENCE+1) =
    (select YEARSEQUENCE from GLFISCALYEAR
    where ID = 
      (select GLFISCALYEARID from GLFISCALPERIOD P 
      where @CURRENTDATE between P.STARTDATE and P.ENDDATE)
    )
  group by FP.GLFISCALYEARID
end
if @DATETYPECODE = 2 -- Next fiscal year

begin
  set @STARTDATE = null
  set @ENDDATE = null
  select @STARTDATE = MIN(FP.STARTDATE), @ENDDATE = MAX(FP.ENDDATE) from dbo.GLFISCALYEAR FY
  inner join dbo.GLFISCALPERIOD FP on FP.GLFISCALYEARID = FY.ID
  where (FY.YEARSEQUENCE-1) =
    (select YEARSEQUENCE from GLFISCALYEAR
    where ID = 
      (select GLFISCALYEARID from GLFISCALPERIOD P 
      where @CURRENTDATE between P.STARTDATE and P.ENDDATE)
    )
  group by FP.GLFISCALYEARID
end
if @DATETYPECODE = 3 -- This fiscal period

begin
  set @STARTDATE = null
  set @ENDDATE = null
  select @STARTDATE = STARTDATE, @ENDDATE = ENDDATE from dbo.GLFISCALPERIOD
  where @CURRENTDATE between STARTDATE and ENDDATE
end
if @DATETYPECODE = 4 -- Last fiscal period

begin
  set @STARTDATE = null
  set @ENDDATE = null
  select @STARTDATE = FP.STARTDATE, @ENDDATE = FP.ENDDATE from dbo.GLFISCALPERIOD FP
  where (FP.SEQUENCE+1) = 
    (select top 1 SEQUENCE from dbo.GLFISCALPERIOD P
    where @CURRENTDATE between STARTDATE and ENDDATE 
    and FP.GLFISCALYEARID = P.GLFISCALYEARID
    order by SEQUENCE)
end
if @DATETYPECODE = 5 -- Next fiscal period

begin
  set @STARTDATE = null
  set @ENDDATE = null
  select @STARTDATE = FP.STARTDATE, @ENDDATE = FP.ENDDATE from GLFISCALPERIOD FP
  where (FP.SEQUENCE-1) =
    (select top 1 SEQUENCE from dbo.GLFISCALPERIOD P
    where @CURRENTDATE between STARTDATE and ENDDATE
    and FP.GLFISCALYEARID = P.GLFISCALYEARID
    order by SEQUENCE)
end
if @DATETYPECODE = 6 -- Specific dates

  select @STARTDATE = @STARTDATE, @ENDDATE = @ENDDATE
if @DATETYPECODE = 7 -- Year to date

begin
  set @STARTDATE = dateadd(year, datediff(year, 0, @CURRENTDATE), 0)
  set @ENDDATE = @CURRENTDATE
end

return 0;