USP_DATALIST_NAMINGOPPORTUNITYRECOGNITION

CREATE procedure dbo.USP_DATALIST_NAMINGOPPORTUNITYRECOGNITION
(
  @NAMINGOPPORTUNITYID uniqueidentifier = null,
  @SHOWFILTER tinyint = 0
)
as
  set nocount on;

  declare @CURRENTDATE datetime; 
  set @CURRENTDATE = dbo.UFN_DATE_GETEARLIESTTIME(getdate());

  with [CTE] as
  (
    select
      NAMINGOPPORTUNITYRECOGNITION.ID,
      NF.NAME as CONSTITUENT, 
      NAMINGOPPORTUNITYRECOGNITION.CONSTITUENTNAME,
      NAMINGOPPORTUNITYRECOGNITION.STARTDATE,
      NAMINGOPPORTUNITYRECOGNITION.ENDDATE,
      NAMINGOPPORTUNITYRECOGNITION.AMOUNT,
      NAMINGOPPORTUNITYRECOGNITION.QUANTITY,
      NAMINGOPPORTUNITYRECOGNITION.INSCRIPTION,

      --JamesWill 2008-09-26 WI 14576 UFN_DATE_FROMFUZZYDATE does not behave in a manner useful for this filter when the fuzzy date does not have a day component; so compensate in these cases

      case 
        when not dbo.UFN_DATE_FROMFUZZYDATE(NAMINGOPPORTUNITYRECOGNITION.STARTDATE) is null then
          --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

          cast(cast(dbo.UFN_DATE_FROMFUZZYDATE(NAMINGOPPORTUNITYRECOGNITION.STARTDATE) as date) as datetime)
        when substring(NAMINGOPPORTUNITYRECOGNITION.STARTDATE, 1, 4) <> '0000' and substring(NAMINGOPPORTUNITYRECOGNITION.STARTDATE, 5, 2) = '00' and substring(NAMINGOPPORTUNITYRECOGNITION.STARTDATE, 7, 2) = '00' then
          convert(datetime, substring(NAMINGOPPORTUNITYRECOGNITION.STARTDATE, 1, 4) + '-01-01T00:00:00')
        when substring(NAMINGOPPORTUNITYRECOGNITION.STARTDATE, 1, 4) <> '0000' and substring(NAMINGOPPORTUNITYRECOGNITION.STARTDATE, 5, 2) <> '00' and substring(NAMINGOPPORTUNITYRECOGNITION.STARTDATE, 7, 2) = '00' then
          convert(datetime, substring(NAMINGOPPORTUNITYRECOGNITION.STARTDATE, 1, 4) + '-' + substring(NAMINGOPPORTUNITYRECOGNITION.STARTDATE, 5, 2) + '-01T00:00:00')
        else
          --The "GETEARLIESTTIME" date function has been inlined here for performance (the part with "cast(@DATE as date)")...

          cast(cast(NAMINGOPPORTUNITYRECOGNITION.DATEADDED as date) as datetime)
      end as [FILTERDATE],

      case when not NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.ID is null then 1 else 0 end as [ISREVENUERECOGNITION],
      NAMINGOPPORTUNITYRECOGNITION.BASECURRENCYID
    from dbo.NAMINGOPPORTUNITYRECOGNITION
    left join dbo.NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT on NAMINGOPPORTUNITYRECOGNITIONREVENUESPLIT.NAMINGOPPORTUNITYRECOGNITIONID = NAMINGOPPORTUNITYRECOGNITION.ID
                outer apply dbo.UFN_CONSTITUENT_DISPLAYNAME(NAMINGOPPORTUNITYRECOGNITION.CONSTITUENTID) NF
    where NAMINGOPPORTUNITYRECOGNITION.NAMINGOPPORTUNITYID = @NAMINGOPPORTUNITYID
  )
  select distinct --WI 14074

    [CTE].ID,
    [CTE].CONSTITUENT,
    [CTE].CONSTITUENTNAME,
    [CTE].STARTDATE,
    [CTE].ENDDATE,
    [CTE].AMOUNT,
    [CTE].QUANTITY,
    [CTE].INSCRIPTION,
    [CTE].FILTERDATE,
    case when [CTE].ISREVENUERECOGNITION = 1 then 1 else 0 end as [ISREVENUERECOGNITION],
    case when [CTE].ISREVENUERECOGNITION = 0 then 1 else 0 end as [ISOPPORTUNITYRECOGNITION],
    [CTE].BASECURRENCYID
  from [CTE]
  where 
    (@SHOWFILTER = 0 or @SHOWFILTER is null)
    or (@SHOWFILTER = 1 and [CTE].FILTERDATE >= dateadd(day, -7, @CURRENTDATE))
    or (@SHOWFILTER = 2 and [CTE].FILTERDATE >= dateadd(day, -30, @CURRENTDATE))
    or (@SHOWFILTER = 3 and [CTE].FILTERDATE >= dateadd(day, -90, @CURRENTDATE))    
  order by [CTE].FILTERDATE, [CTE].CONSTITUENTNAME, [CTE].AMOUNT;

  return 0;