UFN_BILLINGINDIVIDUALAGING

            CREATE function dbo.UFN_BILLINGINDIVIDUALAGING
            (
                @CONSTITUENTID uniqueidentifier,
                @ASOFDATE datetime = null
            )
            returns @AGINGBUCKETS table
            (
                SEQUENCE int,
                LABEL nvarchar(100),
                AMOUNT money
            )
            with execute as caller
            as begin

                -- If past in null then get today's date

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

                -- Get the earliest time for this day

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

                -- Setup a temporary variable for the total unapplied payment amount

                declare @TOTALUNAPPLIEDPAYMENTAMOUNT money;
                set @TOTALUNAPPLIEDPAYMENTAMOUNT = dbo.UFN_BILLINGUNAPPLIEDPAYMENTCREDITTOTALINDIVIDUAL(@CONSTITUENTID, null, @ASOFDATE);

                declare @CURRENTBUCKETAMOUNT money;
                set @CURRENTBUCKETAMOUNT = dbo.UFN_BILLINGTRANSACTIONTYPETOTAL(104, @CONSTITUENTID, null , dateadd(d, -91, @ASOFDATE), 1, 1, 1);

                -- Get the last bucket first

                insert into @AGINGBUCKETS
                    (SEQUENCE, LABEL, AMOUNT)
                select 4,
                        'Over 90 days:',
                        case when (@CURRENTBUCKETAMOUNT - @TOTALUNAPPLIEDPAYMENTAMOUNT) <=  0 then
                                0
                            else
                                @CURRENTBUCKETAMOUNT - @TOTALUNAPPLIEDPAYMENTAMOUNT
                            end;

                declare @LASTBUCKETAMOUNT money;
                set @LASTBUCKETAMOUNT = @CURRENTBUCKETAMOUNT;

                -- Get the next bucket

                set @CURRENTBUCKETAMOUNT = dbo.UFN_BILLINGTRANSACTIONTYPETOTAL(104, @CONSTITUENTID, null , dateadd(d, -61, @ASOFDATE), 1, 1, 1);

                insert into @AGINGBUCKETS
                    (SEQUENCE, LABEL, AMOUNT)
                select 3,
                        '61-90 days:',
                        case when (@CURRENTBUCKETAMOUNT - @TOTALUNAPPLIEDPAYMENTAMOUNT) <=  0 then
                                0
                            when (@LASTBUCKETAMOUNT > @TOTALUNAPPLIEDPAYMENTAMOUNT) then
                                @CURRENTBUCKETAMOUNT - @LASTBUCKETAMOUNT 
                            else
                                (@CURRENTBUCKETAMOUNT - @LASTBUCKETAMOUNT) - (@TOTALUNAPPLIEDPAYMENTAMOUNT - @LASTBUCKETAMOUNT)
                            end;

                set @LASTBUCKETAMOUNT = @CURRENTBUCKETAMOUNT;

                -- Get the next bucket

                set @CURRENTBUCKETAMOUNT = dbo.UFN_BILLINGTRANSACTIONTYPETOTAL(104, @CONSTITUENTID, null , dateadd(d, -31, @ASOFDATE), 1, 1, 1);

                insert into @AGINGBUCKETS
                    (SEQUENCE, LABEL, AMOUNT)
                select 2,
                        '31-60 days:',
                        case when (@CURRENTBUCKETAMOUNT - @TOTALUNAPPLIEDPAYMENTAMOUNT) <=  0 then
                                0
                            when (@LASTBUCKETAMOUNT > @TOTALUNAPPLIEDPAYMENTAMOUNT) then
                                @CURRENTBUCKETAMOUNT - @LASTBUCKETAMOUNT
                            else
                                (@CURRENTBUCKETAMOUNT - @LASTBUCKETAMOUNT) - (@TOTALUNAPPLIEDPAYMENTAMOUNT - @LASTBUCKETAMOUNT)
                            end;

                set @LASTBUCKETAMOUNT = @CURRENTBUCKETAMOUNT;

                -- Get the next bucket

                set @CURRENTBUCKETAMOUNT = dbo.UFN_BILLINGTRANSACTIONTYPETOTAL(104, @CONSTITUENTID, null , @ASOFDATE, 1, 1, 1);

                insert into @AGINGBUCKETS
                    (SEQUENCE, LABEL, AMOUNT)
                select 1,
                        'Current:',
                        case when (@CURRENTBUCKETAMOUNT - @TOTALUNAPPLIEDPAYMENTAMOUNT) <=  0 then
                                0
                            when (@LASTBUCKETAMOUNT > @TOTALUNAPPLIEDPAYMENTAMOUNT) then
                                @CURRENTBUCKETAMOUNT - @LASTBUCKETAMOUNT
                            else
                                (@CURRENTBUCKETAMOUNT - @LASTBUCKETAMOUNT) - (@TOTALUNAPPLIEDPAYMENTAMOUNT - @LASTBUCKETAMOUNT)
                            end;

                return;
            end