USP_DATALIST_RECOGNITIONCOUNTREPORT

                CREATE procedure dbo.USP_DATALIST_RECOGNITIONCOUNTREPORT(
                    @DATEFROM datetime,
                    @DATETO datetime,
                    @PROGRAMID uniqueidentifier,
                    @BY tinyint,
                    @GROUPBY tinyint)
                as
                    set nocount on;
                    declare @TODAY datetime;
                    set @TODAY = getdate();

                    DECLARE @PERIODTABLE TABLE(
                        PERIOD nvarchar(26),
                        PERIODSTARTDATE datetime,
                        PERIODENDDATE datetime
                        );

                    --create a data table with all possible period's for a given range (@DATEFROM - @DATETO)


                    with  datetable as(
                        select  DateValue = @DATEFROM
                        union all
                        select DateValue + 1
                        from    datetable 
                        where   DateValue + 1 < @DATETO 
                    )    
                    INSERT INTO @PERIODTABLE(PERIOD, PERIODSTARTDATE, PERIODENDDATE) 

                    --these case statements determine how the date range table will be grouped; by month, quarter, or year


                    select distinct 
                    case
                        when @BY = 0 then convert(nvarchar(2),datepart(month,DateValue)) + ' / ' + convert(nvarchar(4),datepart(year,DateValue))
                        when @BY = 1 then 'Q' + convert(nvarchar(2),datepart(quarter,DateValue))+ ' - '+convert(nvarchar(4),datepart(year,DateValue)) 
                        when @BY = 2 then convert(nvarchar(4),datepart(year,DateValue))
                    end as PERIOD,
                    case
                        when @BY = 0 then dbo.UFN_DATE_THISMONTH_FIRSTDAY(DateValue,0)
                        when @BY = 1 then dbo.UFN_DATE_THISQUARTER_FIRSTDAY(DateValue,0)
                        when @BY = 2 then dbo.UFN_DATE_THISCALENDARYEAR_FIRSTDAY(DateValue,0)
                    end as PERIODSTARTDATE,
                    case
                        when @BY = 0 then dbo.UFN_DATE_THISMONTH_LASTDAY(DateValue,1)
                        when @BY = 1 then dbo.UFN_DATE_THISQUARTER_LASTDAY(DateValue,0)
                        when @BY = 2 then dbo.UFN_DATE_THISCALENDARYEAR_LASTDAY(DateValue,0)
                    end    as PERIODENDDATE    
                    from datetable
                    OPTION (MAXRECURSION 0);

                    --check to make sure nothing outside the given date range is used


                    update @PERIODTABLE
                    set PERIODSTARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@DATEFROM)
                    where PERIODSTARTDATE < @DATEFROM;

                    update @PERIODTABLE
                    set PERIODENDDATE = dbo.UFN_DATE_GETLATESTTIME(@DATETO)
                    where PERIODENDDATE > @DATETO;

                    --these case statements determine how the datalist will be grouped; by level or by type

                    select 
                        PERIOD,
                        dbo.UFN_CONSTITUENTRECOGNITION_GETCOUNT_BYDATERANGE(
                            @PROGRAMID,
                            case
                                when @GROUPBY = 0 then RECOGNITIONLEVEL.TIERCODEID 
                                when @GROUPBY = 1 then RECOGNITIONLEVELID
                            end,
                            @GROUPBY,
                            PERIODSTARTDATE,
                            PERIODENDDATE
                        ) as COUNT,
                        case
                                when @GROUPBY = 0 then case when TIERCODE.DESCRIPTION is null then 'No Tier' else TIERCODE.DESCRIPTION end
                                when @GROUPBY = 1 then RECOGNITIONLEVEL.NAME
                        end as GROUPEDBYCOLUMN,
                        PERIODENDDATE
                    from 
                        dbo.CONSTITUENTRECOGNITION
                        left join dbo.RECOGNITIONLEVEL on CONSTITUENTRECOGNITION.RECOGNITIONLEVELID = RECOGNITIONLEVEL.ID
                        left join dbo.TIERCODE on RECOGNITIONLEVEL.TIERCODEID = TIERCODE.ID
                        cross join @PERIODTABLE 
                    where RECOGNITIONLEVEL.RECOGNITIONPROGRAMID = @PROGRAMID

                    group by
                        PERIODSTARTDATE,
                        PERIODENDDATE,
                        case
                            when @GROUPBY = 0 then RECOGNITIONLEVEL.TIERCODEID 
                            when @GROUPBY = 1 then RECOGNITIONLEVELID
                        end,
                        case
                                when @GROUPBY = 0 then case when TIERCODE.DESCRIPTION is null then 'No Tier' else TIERCODE.DESCRIPTION end
                                when @GROUPBY = 1 then RECOGNITIONLEVEL.NAME
                        end,
                        case
                                when @GROUPBY = 0 then TIERCODE.SEQUENCE
                                when @GROUPBY = 1 then RECOGNITIONLEVEL.AMOUNT
                        end,                        
                        PERIOD

                    order by
                        PERIODENDDATE,
                        case
                                when @GROUPBY = 0 then TIERCODE.SEQUENCE
                                when @GROUPBY = 1 then RECOGNITIONLEVEL.AMOUNT
                        end