UFN_BILLINGINDIVIDUALCHARGETOTAL

            CREATE function dbo.UFN_BILLINGINDIVIDUALCHARGETOTAL
            (
                @CONSTITUENTID uniqueidentifier,
                @STARTDATE datetime = null,
                @DISPLAYTOTALSBY tinyint = 0 -- See BillingIndividualChargeTotal.View.xml for more information

            )
            returns @CHARGETOTALS table
            (
                ISTOTAL bit default(0),
                BILLINGITEMID uniqueidentifier,
                BILLINGITEMNAME nvarchar(100),
                TOTAL1LABEL nvarchar(100),
                TOTAL1AMOUNT money default(0),
                TOTAL2LABEL nvarchar(100),
                TOTAL2AMOUNT money default(0),
                TOTAL3LABEL nvarchar(100),
                TOTAL3AMOUNT money default(0)
            )
            with execute as caller
            as begin

                -- Setup the passed in start date

                if (@STARTDATE is null)
                    set @STARTDATE = getdate();

                set @STARTDATE = dbo.UFN_DATE_GETEARLIESTTIME(@STARTDATE);

                declare @ENDDATE datetime;
                declare @CURRENTBILLINGCYCLEID uniqueidentifier = null;

                -- Get a table for the billing cycle information

                declare @BILLINGCYCLETABLE table
                (
                    BILLINGCYCLENAME nvarchar(100),
                    BILLINGSTARTDATE datetime,
                    BILLINGENDDATE datetime,
                    SEQUENCE int identity(0, 1)
                );

                declare @VALIDBILLINGCYCLECOUNT int;

                -- Get the current span

                if (@DISPLAYTOTALSBY = 0)
                begin
                    -- Get the billing cycles whose start date is greater than today that have transactions

                    --   or if there are none then any with transactions

                    insert into @BILLINGCYCLETABLE
                        (BILLINGCYCLENAME, BILLINGSTARTDATE, BILLINGENDDATE)
                    select LAST3BILLINGCYCLES.NAME,
                            LAST3BILLINGCYCLES.BILLINGSTARTDATE,
                            LAST3BILLINGCYCLES.BILLINGENDDATE
                    from (select top 3 NAME,
                                    BILLINGSTARTDATE,
                                    BILLINGENDDATE
                        from dbo.UFN_GETBILLINGCYCLES() as BILLINGCYCLES
                        where ((BILLINGSTARTDATE <= @STARTDATE) or (not exists (select ACADEMICYEARID from dbo.UFN_GETBILLINGCYCLES() where BILLINGENDDATE >= @STARTDATE))) and 
                                (exists (select FINANCIALTRANSACTION.ID
                                            from dbo.FINANCIALTRANSACTION
                                            where (FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID) and
                                                    (FINANCIALTRANSACTION.DATE between BILLINGSTARTDATE and BILLINGENDDATE)))
                        order by BILLINGSTARTDATE desc) as LAST3BILLINGCYCLES
                    order by LAST3BILLINGCYCLES.BILLINGSTARTDATE asc;

                    -- See how many we got, we want at least one and at most three

                    select @VALIDBILLINGCYCLECOUNT = count(BILLINGCYCLENAME) from @BILLINGCYCLETABLE;

                    if (@VALIDBILLINGCYCLECOUNT = 0)
                    begin
                        -- We didn't find any in this case just pick the last three years

                        insert into @BILLINGCYCLETABLE
                            (BILLINGCYCLENAME, BILLINGSTARTDATE, BILLINGENDDATE)
                        select REMAININGBILLINGCYCLES.NAME,
                                REMAININGBILLINGCYCLES.BILLINGSTARTDATE,
                                REMAININGBILLINGCYCLES.BILLINGENDDATE
                        from (select top 3 NAME,
            BILLINGSTARTDATE,
                                            BILLINGENDDATE
                            from dbo.UFN_GETBILLINGCYCLES() as BILLINGCYCLES
                            where (BILLINGSTARTDATE <= @STARTDATE)
                            order by BILLINGSTARTDATE) as REMAININGBILLINGCYCLES
                        order by REMAININGBILLINGCYCLES.BILLINGSTARTDATE asc;
                    end
                    else if (@VALIDBILLINGCYCLECOUNT = 1)
                    begin
                        -- We only had one so get the two previous years

                        insert into @BILLINGCYCLETABLE
                            (BILLINGCYCLENAME, BILLINGSTARTDATE, BILLINGENDDATE)
                        select REMAININGBILLINGCYCLES.NAME,
                                REMAININGBILLINGCYCLES.BILLINGSTARTDATE,
                                REMAININGBILLINGCYCLES.BILLINGENDDATE
                        from (select top 2 NAME,
                                            BILLINGSTARTDATE,
                                            BILLINGENDDATE
                            from dbo.UFN_GETBILLINGCYCLES() as BILLINGCYCLES
                            where (BILLINGENDDATE < (select min(BILLINGCYCLETABLE.BILLINGSTARTDATE) from @BILLINGCYCLETABLE as BILLINGCYCLETABLE))
                            order by BILLINGSTARTDATE) as REMAININGBILLINGCYCLES
                        order by REMAININGBILLINGCYCLES.BILLINGSTARTDATE asc;
                    end
                    else if (@VALIDBILLINGCYCLECOUNT = 2)
                    begin
                        -- We only had two so get the previous year

                        insert into @BILLINGCYCLETABLE
                            (BILLINGCYCLENAME, BILLINGSTARTDATE, BILLINGENDDATE)
                        select REMAININGBILLINGCYCLES.NAME,
                                REMAININGBILLINGCYCLES.BILLINGSTARTDATE,
                                REMAININGBILLINGCYCLES.BILLINGENDDATE
                        from (select top 1 NAME,
                                            BILLINGSTARTDATE,
                                            BILLINGENDDATE
                            from dbo.UFN_GETBILLINGCYCLES() as BILLINGCYCLES
                            where (BILLINGENDDATE < (select min(BILLINGCYCLETABLE.BILLINGSTARTDATE) from @BILLINGCYCLETABLE as BILLINGCYCLETABLE))
                            order by BILLINGSTARTDATE) as REMAININGBILLINGCYCLES
                        order by REMAININGBILLINGCYCLES.BILLINGSTARTDATE asc;                    
                    end

                    -- Get the count again, it could be less than three if no years are defined in the system.

                    select @VALIDBILLINGCYCLECOUNT = count(BILLINGCYCLENAME) from @BILLINGCYCLETABLE;

                    -- Get the start and end date we will be using

                    select @STARTDATE = BILLINGCYCLETABLE.BILLINGSTARTDATE,
                            @ENDDATE = BILLINGCYCLETABLE.BILLINGENDDATE
                    from @BILLINGCYCLETABLE as BILLINGCYCLETABLE
                    where BILLINGCYCLETABLE.SEQUENCE = 0;
                end
                else if (@DISPLAYTOTALSBY = 1)
                begin
                    -- Going by calendar years so get the first and last day of the year.

                    set @VALIDBILLINGCYCLECOUNT = 3;
                    set @STARTDATE = CONVERT(datetime, '01/01/' + cast((datepart(yy, @STARTDATE) - 2) as nvarchar(4)), 101);
                    set @ENDDATE = CONVERT(datetime, '12/31/' + cast(datepart(yy, @STARTDATE) as nvarchar(4)), 101);
                end

                -- Setup a temp table for the latest totals

                declare @TEMP table
                (
                    BILLINGITEMID uniqueidentifier,
                    TOTALAMOUNT money
                )

                declare @CURRENTLABEL nvarchar(100);

                -- Start looping!

                declare @INDEX int = 0;
                while (@INDEX < @VALIDBILLINGCYCLECOUNT)
                begin

                    -- Get what will be the label for the current bucket.

                    set @CURRENTLABEL = (case when (@DISPLAYTOTALSBY = 1) then
                                            cast(datepart(yy, @STARTDATE) as nvarchar(4))
                                        else
                                            (select BILLINGCYCLETABLE.BILLINGCYCLENAME 
                                                from @BILLINGCYCLETABLE as BILLINGCYCLETABLE
                                                where BILLINGCYCLETABLE.SEQUENCE = @INDEX)
                                        end);

                    -- Get the totals for this year

                    insert into @TEMP
                        (BILLINGITEMID,
                         TOTALAMOUNT)
                    select CHARGELINEITEM.BILLINGITEMID,
                            sum(FINANCIALTRANSACTIONLINEITEM.TRANSACTIONAMOUNT)
                    from dbo.FINANCIALTRANSACTION
                        inner join FINANCIALTRANSACTIONLINEITEM
                            on (FINANCIALTRANSACTIONLINEITEM.FINANCIALTRANSACTIONID = FINANCIALTRANSACTION.ID) and
                                (FINANCIALTRANSACTIONLINEITEM.DELETEDON is null) and
                                (FINANCIALTRANSACTIONLINEITEM.TYPECODE = 0)
                        inner join dbo.CHARGELINEITEM
                            on CHARGELINEITEM.ID = FINANCIALTRANSACTIONLINEITEM.ID
                        inner join dbo.BILLINGITEM
                            on CHARGELINEITEM.BILLINGITEMID = BILLINGITEM.ID
                    where (FINANCIALTRANSACTION.TYPECODE = 104) and
                            (FINANCIALTRANSACTION.DATE between @STARTDATE and @ENDDATE) and
                            (FINANCIALTRANSACTION.CONSTITUENTID = @CONSTITUENTID)
                    group by CHARGELINEITEM.BILLINGITEMID;

                    -- Update the totals table

                    if (@INDEX = 0)
                    begin
                        -- If we are working on the first bucket then fill it in

                        insert into @CHARGETOTALS
                            (BILLINGITEMID,
                             BILLINGITEMNAME,
                             TOTAL1LABEL,
                             TOTAL1AMOUNT)
                        select TEMP.BILLINGITEMID, 
                                 BILLINGITEM.NAME,
                                 @CURRENTLABEL,
                                 TEMP.TOTALAMOUNT
                        from @TEMP as TEMP
                            inner join dbo.BILLINGITEM
                                on TEMP.BILLINGITEMID = BILLINGITEM.ID;

                        if ((select count(TOTALAMOUNT) from @TEMP) = 0)
                        begin
                            -- If no records where found then load a totals row with zero

                            insert into @CHARGETOTALS
                                (ISTOTAL,
                                 BILLINGITEMID,
                                 BILLINGITEMNAME,
                                 TOTAL1LABEL,
                                 TOTAL1AMOUNT)
                            values (1,
                                   null,
                                   '',
                                   @CURRENTLABEL,
                                   0);
                        end
                        else
                        begin
                            -- If we did have some rows then add them together for the total row

                            insert into @CHARGETOTALS
                                (ISTOTAL,
                                 BILLINGITEMID,
                                 BILLINGITEMNAME,
                                 TOTAL1LABEL,
                                 TOTAL1AMOUNT)
                            select 1,
                                   null,
                                   '',
                                   @CURRENTLABEL,
                                   SUM(isnull(TEMP.TOTALAMOUNT, 0))
                            from @TEMP as TEMP;
     end
                    end
                    else if (@INDEX = 1)
                    begin
                        update @CHARGETOTALS
                            set TOTAL2LABEL = @CURRENTLABEL,
                                TOTAL2AMOUNT = TOTALAMOUNT
                        from @CHARGETOTALS as CHARGETOTALS
                            inner join @TEMP as TEMP
                                on CHARGETOTALS.BILLINGITEMID = TEMP.BILLINGITEMID;

                        insert into @CHARGETOTALS
                            (BILLINGITEMID,
                             BILLINGITEMNAME,
                             TOTAL2LABEL,
                             TOTAL2AMOUNT)
                        select TEMP.BILLINGITEMID, 
                                 BILLINGITEM.NAME,
                                 @CURRENTLABEL,
                                 TEMP.TOTALAMOUNT
                        from @TEMP as TEMP
                            left outer join @CHARGETOTALS as CHARGETOTALS
                                on TEMP.BILLINGITEMID = CHARGETOTALS.BILLINGITEMID
                            inner join dbo.BILLINGITEM
                                on TEMP.BILLINGITEMID = BILLINGITEM.ID
                        where CHARGETOTALS.BILLINGITEMID is null;

                        if ((select COUNT(BILLINGITEMID) from @TEMP) = 0)
                        begin
                            update @CHARGETOTALS
                                set TOTAL2LABEL = @CURRENTLABEL;
                        end
                        else
                        begin
                            update @CHARGETOTALS
                                set TOTAL2LABEL = @CURRENTLABEL,
                                    TOTAL2AMOUNT = (select SUM(TEMP.TOTALAMOUNT) as TOTALAMOUNT
                                                    from @TEMP as TEMP)
                            from @CHARGETOTALS as CHARGETOTALS
                            where CHARGETOTALS.ISTOTAL = 1;
                        end
                    end
                    else if (@INDEX = 2)
                    begin
                        update @CHARGETOTALS
                            set TOTAL3LABEL = @CURRENTLABEL,
                                TOTAL3AMOUNT = TOTALAMOUNT
                        from @CHARGETOTALS as CHARGETOTALS
                            inner join @TEMP as TEMP
                                on CHARGETOTALS.BILLINGITEMID = TEMP.BILLINGITEMID;

                        insert into @CHARGETOTALS
                            (BILLINGITEMID,
                             BILLINGITEMNAME,
                             TOTAL3LABEL,
                             TOTAL3AMOUNT)
                        select TEMP.BILLINGITEMID, 
                                 BILLINGITEM.NAME,
                                 @CURRENTLABEL,
                                 TEMP.TOTALAMOUNT
                        from @TEMP as TEMP
                            left outer join @CHARGETOTALS as CHARGETOTALS
                                on TEMP.BILLINGITEMID = CHARGETOTALS.BILLINGITEMID
                            inner join dbo.BILLINGITEM
                                on TEMP.BILLINGITEMID = BILLINGITEM.ID
                        where CHARGETOTALS.BILLINGITEMID is null;

                        if ((select COUNT(BILLINGITEMID) from @TEMP) = 0)
                        begin
                            update @CHARGETOTALS
                                set TOTAL3LABEL = @CURRENTLABEL;
                        end
                        else
                        begin
                            update @CHARGETOTALS
                                set TOTAL3LABEL = @CURRENTLABEL,
                                    TOTAL3AMOUNT = (select SUM(TEMP.TOTALAMOUNT) as TOTALAMOUNT
                                from @TEMP as TEMP)
                            from @CHARGETOTALS as CHARGETOTALS
                            where CHARGETOTALS.ISTOTAL = 1;
                        end
                    end

                    delete from @TEMP;

                    -- Get the next span

                    if (@DISPLAYTOTALSBY = 0)
                    begin
                        select @STARTDATE = BILLINGCYCLETABLE.BILLINGSTARTDATE,
                                @ENDDATE = BILLINGCYCLETABLE.BILLINGENDDATE
                        from @BILLINGCYCLETABLE as BILLINGCYCLETABLE
                        where BILLINGCYCLETABLE.SEQUENCE = (@INDEX + 1);
                    end
                    else if (@DISPLAYTOTALSBY = 1)
                    begin
                        set @STARTDATE = CONVERT(datetime, '01/01/' + cast((datepart(yy, @STARTDATE) + 1) as nvarchar(4)), 101);
                        set @ENDDATE = CONVERT(datetime, '12/31/' + cast((datepart(yy, @ENDDATE) + 1) as nvarchar(4)), 101);
                    end

                    --Advance the index

                    set @INDEX = @INDEX + 1;
                end

                -- Clean up the labels

                if (@VALIDBILLINGCYCLECOUNT >= 2)
                begin
                    update @CHARGETOTALS
                        set TOTAL1LABEL = AMOUNT1LABEL_DISTINCT.TOTAL1LABEL
                    from (select top 1 TOTAL1LABEL from @CHARGETOTALS where (not TOTAL1LABEL is null)) as AMOUNT1LABEL_DISTINCT;
                end

                if (@VALIDBILLINGCYCLECOUNT >= 2)
                begin
                    update @CHARGETOTALS
                        set TOTAL2LABEL = AMOUNT2LABEL_DISTINCT.TOTAL2LABEL
                    from (select top 1 TOTAL2LABEL from @CHARGETOTALS where (not TOTAL2LABEL is null)) as AMOUNT2LABEL_DISTINCT;
                end

                if (@VALIDBILLINGCYCLECOUNT = 3)
                begin
                    update @CHARGETOTALS
                        set TOTAL3LABEL = AMOUNT3LABEL_DISTINCT.TOTAL3LABEL
                    from (select top 1 TOTAL3LABEL from @CHARGETOTALS where (not TOTAL3LABEL is null)) as AMOUNT3LABEL_DISTINCT;
                end

                return;
            end