update_descriptives_new.qy

$Version=2$
update_descriptives_new "system/query"
    {
    fund "query/parameter" { type=string; style=strnull; }
    ledger "query/parameter" { type=string; style=strnull; }
    donor "query/parameter" { type=string; style=strnull;}
    lookback "query/parameter" { type=integer; default=37; }

    sql = " declare collection tmp_gift_items scope application;
	    declare collection a_descriptives scope application;
	    declare collection a_descriptives_hist scope application;
		declare collection a_pledge scope query;
        declare collection tmp_intermediate_vals;

	    --This is the main file for our donor analytics application
	    --The donor analytics is meant to make a new table, a_descriptives_hist, that keeps track of donor giving patterns and such
	    --The purpose of keeping track of all this is to be able to assist workers in keeping track of their donors
	    --If a donor randomly drops off for a month or two, we want to be able to tell the worker that so they can reach out to their donor
	    --This organizes the data so that we can easily tell what kind of pattern a donor has, which can therefore be used when someone stops doing their normal pattern

	    delete collection tmp_gift_items;
        delete collection tmp_intermediate_vals;

	    print 'Working on descriptives' + isnull(' for fund ' + :parameters:fund + ',', '') + isnull(' for ledger ' + :parameters:ledger + ',', '') + isnull(' for donor ' + :parameters:donor + ',', '') - ',';

	    -- Clear out any old descriptives
	    print 'Clearing old descriptives at ' + dateformat(getdate(), 'hh:mm:ss');
	    delete collection a_descriptives d;
	    delete collection a_descriptives_hist dh;

	    -- Make a working copy of the dataset in-memory
	    print 'Creating in-memory dataset at ' + dateformat(getdate(), 'hh:mm:ss');
	    insert
		collection tmp_gift_items
	    select
		donor = :i:p_dn_donor_partner_id,
		giftdate = isnull(:i:a_dn_gift_postmark_date, :i:a_dn_gift_received_date),
		amount = :i:a_amount,
		fund = :i:a_fund,
		ledger = :i:a_ledger_number,
		id = row_number(),
		seg = row_number() - row_number(:i:a_ledger_number, :i:a_fund, :i:p_dn_donor_partner_id, :i:a_amount)
	    from
		/apps/kardia/data/Kardia_DB/a_subtrx_gift_item/rows i
	    where
		isnull(:i:a_dn_gift_postmark_date, :i:a_dn_gift_received_date) > dateadd(month, 0 - :parameters:lookback, getdate()) and
		:i:a_posted = 1 and
		:i:a_amount != $0 and
		(:parameters:fund is null or :parameters:fund = :i:a_fund) and
		(:parameters:ledger is null or :parameters:ledger = :i:a_ledger_number) and
		(:parameters:donor is null or :parameters:donor = :i:p_dn_donor_partner_id)
	    order by
		:i:a_ledger_number,
		:i:a_fund,
		:i:p_dn_donor_partner_id,
		isnull(:i:a_dn_gift_postmark_date, :i:a_dn_gift_received_date) desc
	    ;

            -- Delete negative giving values and cancel out a
            -- corresponding positive gift
            -- t1 is the negative gift, t2 is the gift to be
            -- cancelled out, and t3 is the intermediate value
	    print 'Correcting for gift returns at ' + dateformat(getdate(), 'hh:mm:ss');
            insert
                collection tmp_intermediate_vals
            select
                ledger = :t1:ledger,
                fund = :t1:fund,
                donor = :t1:donor,
                min_diff = (select min(abs(datediff(day, :t2:giftdate, :t1:giftdate))) from collection tmp_gift_items t2 where :t1:ledger = :t2:ledger and :t1:donor = :t2:donor and :t1:fund = :t2:fund and :t2:amount = -1 * :t1:amount group by :t2:ledger, :t2:donor, :t2:fund),
                id = null
            from
                collection tmp_gift_items t1
            where
                (:parameters:fund is null or :parameters:fund = :t1:fund) and
		(:parameters:donor is null or :parameters:donor = :t1:donor) and
                :t1:amount < 0
            ;
            update
                identity collection tmp_intermediate_vals t3,
                collection tmp_gift_items t1
            set
                :t3:id = (select :t2:id from collection tmp_gift_items t2 where :t1:ledger = :t2:ledger and :t1:donor = :t2:donor and :t1:fund = :t2:fund and :t2:amount = -1 * :t1:amount and :t3:min_diff = datediff(day, :t2:giftdate, :t1:giftdate) limit 1)
            where
                (:parameters:fund is null or :parameters:fund = :t1:fund) and
		(:parameters:donor is null or :parameters:donor = :t1:donor) and
                :t1:ledger = :t3:ledger and
                :t1:donor = :t3:donor and
                :t1:fund = :t3:fund and
                :t1:amount < 0
            ;
            delete
                collection tmp_gift_items t1
            where
                (:parameters:fund is null or :parameters:fund = :t1:fund) and
		(:parameters:donor is null or :parameters:donor = :t1:donor) and
                :t1:amount < 0
            ;
            delete
                identity collection tmp_gift_items t2,
                collection tmp_intermediate_vals t3
            where
                (:parameters:fund is null or :parameters:fund = :t2:fund) and
		(:parameters:donor is null or :parameters:donor = :t2:donor) and
                :t2:ledger = :t3:ledger and
                :t2:donor = :t3:donor and
                :t2:fund = :t3:fund and
                :t2:id = :t3:id
            ;
            
	    -- Build a list of giving pattern descriptives for each of the fund's donors.  We want
	    -- to optimize this to help staff interact with donors and ask good questions, instead
	    -- of generating results simply based on math.
	    print 'Creating descriptives at ' + dateformat(getdate(), 'hh:mm:ss');
	    insert
		collection a_descriptives d
	    select
		-- Primary key
		a_ledger_number = :ledger,
		a_fund = :fund,
		p_donor_partner_key = :donor,

		-- Basic descriptives
		a_first_gift = last(:giftdate),
		a_first_gift_amount = last(:amount),
		a_last_gift = first(:giftdate),
		a_last_gift_amount = first(:amount),
		a_ntl_gift = nth(:giftdate, 2),
		a_ntl_gift_amount = nth(:amount, 2),

		-- Bootstrap the analysis process using the data we have.
		a_act_lookback_date = last(:giftdate),
		a_act_lookahead_date = first(:giftdate),
		a_act_count = count(1),
		a_act_total = sum(:amount),
		a_act_average_months = convert(integer, round(datediff(day, last(:giftdate), first(:giftdate)) / 30.5)),
		a_act_average_interval = condition(
			count(1) < 2 or datediff(day, last(:giftdate), first(:giftdate)) = 0,
			convert(double, null),
			(datediff(day, last(:giftdate), first(:giftdate)) / 30.5) / convert(double, count(1) - 1)
			),
		a_act_average_amount = condition(
			count(1) < 2 or datediff(day, last(:giftdate), first(:giftdate)) < 16,
			convert(money, null),
			sum(:amount) * (convert(double, count(1) - 1) / count(1)) / (datediff(day, last(:giftdate), first(:giftdate)) / 30.5)
			),
		s_date_created = getdate(),
		s_created_by = user_name(),
		s_date_modified = getdate(),
		s_modified_by = user_name()
	    from
		collection tmp_gift_items t
	    group by             
		:ledger,
		:fund,
		:donor
	    order by
		:ledger,
		:fund,
		:donor,
		:giftdate desc
	    ;

	    -- Generate the amount history data, starting with the most recent gift amount.
	    print 'Creating descriptives histories at ' + dateformat(getdate(), 'hh:mm:ss');
	    insert into
		collection a_descriptives_hist
	    select
		a_ledger_number = :ledger,
		p_donor_partner_key = :donor,
		a_fund = :fund,
		a_amount = :amount,
		a_first_gift = last(:giftdate),
		a_last_gift = first(:giftdate),
		a_ntl_gift = nth(:giftdate, 2),
		a_count = count(1),
		a_total = sum(:amount),
		a_act_average_months = convert(integer, round(datediff(day, last(:giftdate), first(:giftdate)) / 30.5)),
		a_act_average_interval = condition(
			count(1) < 2 or datediff(day, last(:giftdate), first(:giftdate)) = 0,
			convert(double, null),
			(datediff(day, last(:giftdate), first(:giftdate)) / 30.5) / convert(double, count(1) - 1)
			),
		a_act_average_amount = condition(
                        datediff(day, last(:giftdate), first(:giftdate)) < 16,
                        convert(money, null),
                        (sum(:amount) - :amount) / round(datediff(day, last(:giftdate), first(:giftdate)) / 30.5)
                        ),
		s_date_created = getdate(),
		s_created_by = user_name(),
		s_date_modified = getdate(),
		s_modified_by = user_name()
	    from
		collection tmp_gift_items
	    group by
		:ledger,
		:fund,
		:donor,
		:seg
	    order by
		:ledger,
		:fund,
		:donor,
		:seg,
		:giftdate desc
	    ;

		-- Creating pledge collection
		print 'Creating pledge collection at ' + dateformat(getdate(), 'hh:mm:ss');
		insert into
		collection a_pledge
		select
		a_ledger_number = :i:a_ledger_number,
		p_donor_partner_id = :i:p_donor_partner_id,
		a_fund = :i:a_fund,
		a_pledge_id = :i:a_pledge_id,
		a_is_active = i:a_is_active
		a_intent_type = i:a_intent_type
		a_amount = :i:a_amount,
		a_total_amount = :i:a_total_amount,
		a_giving_interval = :i:a_giving_interval,
		a_gift_count = :i:a_gift_count,
		a_pledge_date = :i:a_pledge_date,
		a_start_date = :i:a_start_date,
		a_end_date = :i:a:a_end_date
		from 
		/apps/kardia/data/Kardia_DB/a_pledge/rows i

	    -- Update previous/next interval start/end dates
	    print 'Updating segment start/end dates at ' + dateformat(getdate(), 'hh:mm:ss');
	    update
		collection a_descriptives_hist dh1
	    set
		:dh1:a_prev_end = (select :a_last_gift from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh1:a_ledger_number and :dh2:a_fund = :dh1:a_fund and :dh2:p_donor_partner_key = :dh1:p_donor_partner_key and :dh2:a_last_gift < :dh1:a_first_gift order by :dh2:a_last_gift desc limit 1),
		:dh1:a_next_start = (select :a_first_gift from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh1:a_ledger_number and :dh2:a_fund = :dh1:a_fund and :dh2:p_donor_partner_key = :dh1:p_donor_partner_key and :dh2:a_first_gift > :dh1:a_last_gift order by :dh2:a_first_gift asc limit 1)
	    where
		(:parameters:fund is null or :parameters:fund = :dh1:a_fund) and
		(:parameters:donor is null or :parameters:donor = :dh1:p_donor_partner_key)
	    ;

            -- These are the merges that allow the gift histories to come out
            -- very neat and clean. Some of them are repeated, which is a
            -- known limitation. These repeats provide good data, but are finite
            -- and may need to be repeated more for larger data sets.

            -- Merge in 'catch up' giving
	    print 'Merge catch-up at ' + dateformat(getdate(), 'hh:mm:ss');
	    exec /apps/kardia/modules/rcpt/update_descriptives_merge_catchup.qy
		fund=:parameters:fund,
		donor=:parameters:donor
	    ;
            -- Merge in adjacent giving segments
	    print 'Merge adjacent at ' + dateformat(getdate(), 'hh:mm:ss');
	    exec /apps/kardia/modules/rcpt/update_descriptives_merge_adjacent.qy
		fund=:parameters:fund,
		donor=:parameters:donor
	    ;
            -- Merge in 'catch up' giving that includes extra gifts
	    print 'Merge catch-up at ' + dateformat(getdate(), 'hh:mm:ss');
	    exec /apps/kardia/modules/rcpt/update_descriptives_merge_catchup_with_extra.qy
		fund=:parameters:fund,
		donor=:parameters:donor
	    ;
            -- Merge in extra giving
	    print 'Merge extra amounts at ' + dateformat(getdate(), 'hh:mm:ss');
	    exec /apps/kardia/modules/rcpt/update_descriptives_merge_extra.qy
		fund=:parameters:fund,
		donor=:parameters:donor
	    ;
            -- Merge in adjacent giving segments
	    print 'Merge adjacent(2) at ' + dateformat(getdate(), 'hh:mm:ss');
	    exec /apps/kardia/modules/rcpt/update_descriptives_merge_adjacent_2.qy
		fund=:parameters:fund,
		donor=:parameters:donor
	    ;
            -- Merge in adjacent single-gift segments
	    print 'Merge adjacent singles at ' + dateformat(getdate(), 'hh:mm:ss');
	    exec /apps/kardia/modules/rcpt/update_descriptives_merge_adjacent_singles.qy
		fund=:parameters:fund,
		donor=:parameters:donor
	    ;
            -- Merge in adjacent giving segments
	    print 'Merge adjacent(2) at ' + dateformat(getdate(), 'hh:mm:ss');
	    exec /apps/kardia/modules/rcpt/update_descriptives_merge_adjacent_2.qy
		fund=:parameters:fund,
		donor=:parameters:donor
	    ;
            -- Merge in adjacent giving segments
	    print 'Merge adjacent(2) at ' + dateformat(getdate(), 'hh:mm:ss');
	    exec /apps/kardia/modules/rcpt/update_descriptives_merge_adjacent_2.qy
		fund=:parameters:fund,
		donor=:parameters:donor
	    ;
            -- Approximate segments that did not fit in anywhere else
	    print 'Merge approximates at ' + dateformat(getdate(), 'hh:mm:ss');
	    exec /apps/kardia/modules/rcpt/update_descriptives_merge_approx.qy
		fund=:parameters:fund,
		donor=:parameters:donor
	    ;

            -- Correct previous/next interval start/end dates
            --  Extra gifts are all linked together, and regular gifts are
            --  linked together.
	    print 'Correcting start/end dates at ' + dateformat(getdate(), 'hh:mm:ss');
	    update
		collection a_descriptives_hist dh1
	    set
		:dh1:a_prev_end = (select :a_last_gift from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh1:a_ledger_number and :dh2:a_fund = :dh1:a_fund and :dh2:p_donor_partner_key = :dh1:p_donor_partner_key and :dh2:a_last_gift < :dh1:a_first_gift and not :dh2:a_is_extra order by :dh2:a_last_gift desc limit 1),
		:dh1:a_next_start = (select :a_first_gift from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh1:a_ledger_number and :dh2:a_fund = :dh1:a_fund and :dh2:p_donor_partner_key = :dh1:p_donor_partner_key and :dh2:a_first_gift > :dh1:a_last_gift and not :dh2:a_is_extra order by :dh2:a_first_gift asc limit 1)
	    where
		(:parameters:fund is null or :parameters:fund = :dh1:a_fund) and
		(:parameters:donor is null or :parameters:donor = :dh1:p_donor_partner_key) and
                not :dh1:a_is_extra
	    ;
        update
		collection a_descriptives_hist dh1
	    set
		:dh1:a_prev_end = (select :a_last_gift from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh1:a_ledger_number and :dh2:a_fund = :dh1:a_fund and :dh2:p_donor_partner_key = :dh1:p_donor_partner_key and :dh2:a_last_gift < :dh1:a_first_gift and :dh2:a_is_extra order by :dh2:a_last_gift desc limit 1),
		:dh1:a_next_start = (select :a_first_gift from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh1:a_ledger_number and :dh2:a_fund = :dh1:a_fund and :dh2:p_donor_partner_key = :dh1:p_donor_partner_key and :dh2:a_first_gift > :dh1:a_last_gift and :dh2:a_is_extra order by :dh2:a_first_gift asc limit 1)
	    where
		(:parameters:fund is null or :parameters:fund = :dh1:a_fund) and
		(:parameters:donor is null or :parameters:donor = :dh1:p_donor_partner_key) and
                :dh1:a_is_extra
	    ;

            -- Correct NTL Tracking
	    print 'Fixing next-to-last tracking at ' + dateformat(getdate(), 'hh:mm:ss');
        update
		collection a_descriptives_hist dh1
	    set
		:dh1:a_ntl_gift = (select nth(:t:giftdate, 2) from collection tmp_gift_items t where :t:ledger = :dh1:a_ledger_number and :t:fund = :dh1:a_fund and :t:donor = :dh1:p_donor_partner_key and :t:giftdate <= :dh1:a_last_gift order by :t:giftdate desc)
            where
		(:parameters:fund is null or :parameters:fund = :dh1:a_fund) and
		(:parameters:donor is null or :parameters:donor = :dh1:p_donor_partner_key) and
                :dh1:a_ntl_gift is null and
                :dh1:a_count > 1
            ;

	    -- First history value
	    print 'Propagating history #1 at ' + dateformat(getdate(), 'hh:mm:ss');
	    update
		identity collection a_descriptives d
	    set
		:a_hist_1_amount = (select
					first(:dh:a_amount)
				    from
					collection a_descriptives_hist dh
				    where
					:d:a_ledger_number = :dh:a_ledger_number and
					:d:a_fund = :dh:a_fund and
					:d:p_donor_partner_key = :dh:p_donor_partner_key
				    order by
					:dh:a_last_gift desc
				    ),
                :a_hist_1_last = (select
					first(:dh:a_last_gift)
				    from
					collection a_descriptives_hist dh
				    where
					:d:a_ledger_number = :dh:a_ledger_number and
					:d:a_fund = :dh:a_fund and
					:d:p_donor_partner_key = :dh:p_donor_partner_key
				    order by
					:dh:a_last_gift desc
				    )
	    where
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;
	    update
		identity collection a_descriptives d,
		collection a_descriptives_hist dh
	    set
		:d:a_hist_1_count = :dh:a_count,
		:d:a_hist_1_first = :dh:a_first_gift,
		:d:a_hist_1_last = :dh:a_last_gift
	    where
		:d:a_ledger_number = :dh:a_ledger_number and
		:d:a_fund = :dh:a_fund and
		:d:p_donor_partner_key = :dh:p_donor_partner_key and
		:d:a_hist_1_amount = :dh:a_amount and
                :d:a_hist_1_last = :dh:a_last_gift and
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;

	    -- Second history value
	    print 'Propagating history #2 at ' + dateformat(getdate(), 'hh:mm:ss');
	    update
		identity collection a_descriptives d
	    set
		:a_hist_2_amount = (select
					first(:dh:a_amount)
				    from
					collection a_descriptives_hist dh
				    where
					:d:a_ledger_number = :dh:a_ledger_number and
					:d:a_fund = :dh:a_fund and
					:d:p_donor_partner_key = :dh:p_donor_partner_key and
					:dh:a_last_gift < :d:a_hist_1_last
				    order by
					:dh:a_last_gift desc
				    ),
                :a_hist_2_last = (select
					first(:dh:a_last_gift)
				    from
					collection a_descriptives_hist dh
				    where
					:d:a_ledger_number = :dh:a_ledger_number and
					:d:a_fund = :dh:a_fund and
					:d:p_donor_partner_key = :dh:p_donor_partner_key and
					:dh:a_last_gift < :d:a_hist_1_last
				    order by
					:dh:a_last_gift desc
				    )
	    where
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;
	    update
		identity collection a_descriptives d,
		collection a_descriptives_hist dh
	    set
		:d:a_hist_2_count = :dh:a_count,
		:d:a_hist_2_first = :dh:a_first_gift
	    where
		:d:a_ledger_number = :dh:a_ledger_number and
		:d:a_fund = :dh:a_fund and
		:d:p_donor_partner_key = :dh:p_donor_partner_key and
		:d:a_hist_2_amount = :dh:a_amount and
                :d:a_hist_2_last = :dh:a_last_gift and
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;

	    -- Third history value
	    print 'Propagating history #3 at ' + dateformat(getdate(), 'hh:mm:ss');
	    update
		identity collection a_descriptives d
	    set
		:a_hist_3_amount = (select
					first(:dh:a_amount)
				    from
					collection a_descriptives_hist dh
				    where
					:d:a_ledger_number = :dh:a_ledger_number and
					:d:a_fund = :dh:a_fund and
					:d:p_donor_partner_key = :dh:p_donor_partner_key and
					:dh:a_last_gift < :d:a_hist_1_last and
					:dh:a_last_gift < :d:a_hist_2_last
				    order by
					:dh:a_last_gift desc
				    ),
                :a_hist_3_last = (select
					first(:dh:a_last_gift)
				    from
					collection a_descriptives_hist dh
				    where
					:d:a_ledger_number = :dh:a_ledger_number and
					:d:a_fund = :dh:a_fund and
					:d:p_donor_partner_key = :dh:p_donor_partner_key and
					:dh:a_last_gift < :d:a_hist_1_last and
					:dh:a_last_gift < :d:a_hist_2_last
				    order by
					:dh:a_last_gift desc
				    )
	    where
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;
	    update
		identity collection a_descriptives d,
		collection a_descriptives_hist dh
	    set
		:d:a_hist_3_count = :dh:a_count,
		:d:a_hist_3_first = :dh:a_first_gift
	    where
		:d:a_ledger_number = :dh:a_ledger_number and
		:d:a_fund = :dh:a_fund and
		:d:p_donor_partner_key = :dh:p_donor_partner_key and
		:d:a_hist_3_amount = :dh:a_amount and
                :d:a_hist_3_last = :dh:a_last_gift and
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;

	    -- Overall adjustments to a_act_average_xyz for a_descriptives
	    -- if the interval is biannual or less, lookback slightly less than a year from the most recent gift
            -- the lookahead date is half an interval past the most recent gift
	    print 'Setting general stats at ' + dateformat(getdate(), 'hh:mm:ss');
        update
		identity collection a_descriptives d
	    set
		:a_act_lookahead_date = dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_last_gift),
		:a_act_lookback_date = condition(dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_first_gift) < dateadd(day, -366, dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_last_gift)), dateadd(day, -366, dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_last_gift)), dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_first_gift)),
                :a_act_average_interval = convert(double, null),
		:a_act_average_months = null,
		:a_act_average_amount = convert(money, null)
	    where
		:a_act_average_interval < 7 and
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;
            -- if the interval is greater than bi-annually, check back the last three intervals
        update
		identity collection a_descriptives d
	    set
		:a_act_lookahead_date = dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_last_gift),
		:a_act_lookback_date = condition(dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_first_gift) < dateadd(day, convert(integer, round(-3.0 * 30.5 * :a_act_average_interval)), dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_last_gift)), dateadd(day, convert(integer, round(-3.0 * 30.5 * :a_act_average_interval)), dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_last_gift)), dateadd(day, convert(integer, round(0.5 * 30.5 * :a_act_average_interval)), :a_first_gift)),
                :a_act_average_interval = convert(double, null),
                :a_act_average_months = null,
		:a_act_average_amount = convert(money, null)
	    where
		:a_act_average_interval >= 7 and
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;
            -- this handles one time gifts (and giving that falls all within half a month)
	    update 
		identity collection a_descriptives d
	    set
		:a_act_average_interval = convert(double, null),
		:a_act_average_months = null,
		:a_act_average_amount = convert(money, null),
		:a_act_lookahead_date = condition(getdate() < dateadd(day, 366, :a_last_gift), getdate(), dateadd(day, 366, :a_last_gift)),
		:a_act_lookback_date = :a_first_gift
	    where
		:a_act_average_months = 0 and
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;
	    update 
		identity collection a_descriptives d
	    set
		:a_act_average_months = convert(integer, round(datediff(day, :d:a_act_lookback_date, :d:a_act_lookahead_date) / 30.5)),
		:a_act_average_amount = isnull((select condition(
			count(1) = 0 or datediff(day, :d:a_act_lookback_date, :d:a_act_lookahead_date) < 16,
			convert(money, null),
			sum(:i:amount) / (datediff(day, :d:a_act_lookback_date, :d:a_act_lookahead_date) / 30.5)
		) from collection tmp_gift_items i where :i:giftdate >= :d:a_act_lookback_date and :i:giftdate <= :d:a_act_lookahead_date and :i:fund = :d:a_fund and :i:ledger = :d:a_ledger_number and :i:donor = :d:p_donor_partner_key), $0),
		:a_act_average_interval = (select condition(
			count(1) = 0 or datediff(day, :d:a_act_lookback_date, :d:a_act_lookahead_date) < 16,
			convert(double, null),
			(datediff(day, :d:a_act_lookback_date, :d:a_act_lookahead_date) / 30.5) / convert(double, count(1))
		) from collection tmp_gift_items i where :i:giftdate >= :d:a_act_lookback_date and :i:giftdate <= :d:a_act_lookahead_date and :i:fund = :d:a_fund and :i:ledger = :d:a_ledger_number and :i:donor = :d:p_donor_partner_key)
	    where
		:d:a_act_average_interval is null and
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;

            -- Update a_act_average_xyz for a_descriptives_hist
            update
                identity collection a_descriptives_hist dh
            set
                :a_act_average_amount = condition(
                                datediff(day, :dh:a_first_gift, :dh:a_last_gift) < 16,
                                convert(money, null),
                                (:dh:a_total - :dh:a_amount) / round(datediff(day, :dh:a_first_gift, :dh:a_last_gift) / 30.5)
                                ),
                :a_act_average_months = convert(integer, round(datediff(day, :dh:a_first_gift, :dh:a_last_gift) / 30.5)),
                :a_act_average_interval = condition(
                                :dh:a_count = 1,
                                convert(double, null),
                                (datediff(day, :dh:a_first_gift, :dh:a_last_gift) / 30.5) / (:dh:a_total / :dh:a_amount - 1)
                                )
            where
                (:parameters:fund is null or :parameters:fund = :dh:a_fund) and
                (:parameters:donor is null or :parameters:donor = :dh:p_donor_partner_key) and
                :dh:is_approximate = 0
            ;

		-- Update pledge information for a_descriptives_hist
		update
			identity collection a_descriptives_hist dh,
			collection a_pledge p
		set 
			:dh:a_pledge_id = :p:a_pledge_id,
			:dh:a_pledge_is_active = :p:a_is_active,
			:dh:a_pledge_intent_type = :p:a_intent_type,
			:dh:a_pledge_amount = :p:a_amount,
			:dh:a_pledge_total_amount = :p:a_total_amount,
			:dh:a_pledge_giving_interval = :p:a_giving_interval,
			:dh:a_pledge_gift_count = :p:a_gift_count,
			:dh:a_pledge_made_date = :p:a_pledge_date,
			:dh:a_pledge_start_date = :p:a_start_date,
			:dh:a_pledge_end_date = :p:a_end_date
		where
			:dh:a_ledger_number *= :p:a_ledger_number
				and :dh:a_fund *= :p:a_fund
				and :dh:p_donor_partner_key *= :p:p_donor_partner_id

	    -- Update past/current flags on histories and on descriptives. For a history entry with
	    -- more than one gift, we base the current/lapsed indicator on the gift interval compared to
	    -- the most recent gift and current date.  For a one-gift history entry, we look back 13
	    -- months, considering the gift a onetime/annual gift.
	    --
	    -- a_is_current  Values: 0 = lapsed, 1 = due / possibly current, 2 = current
	    -- a_lapsed_days Values: negative = days before the statistical next gift date,
	    --                       positive = days after the next statistical gift date.
	    print 'Setting lapsed stats at ' + dateformat(getdate(), 'hh:mm:ss');
	    update
		identity collection a_descriptives_hist dh
	    set
		:a_is_current = condition(:a_count >= 2,
				    condition(:a_act_average_interval < 7.0,
					condition(:a_last_gift > dateadd(day, -7 - convert(integer, round(30.5 * :a_act_average_interval)), getdate()),
					    2,
					    condition(:a_last_gift > dateadd(day, -30 - convert(integer, round(30.5 * :a_act_average_interval)), getdate()),
						1,
						0),
					    ),
					condition(:a_last_gift > dateadd(day, 30 - convert(integer, round(30.5 * :a_act_average_interval)), getdate()),
					    2,
					    condition(:a_last_gift > dateadd(day, -30 - convert(integer, round(30.5 * :a_act_average_interval)), getdate()),
						1,
						0),
					    )
					),
				    condition(:a_last_gift > dateadd(day, -334, getdate()),
					2,
					condition(:a_last_gift > dateadd(day, -396, getdate()),
					    1,
					    0)
					)
				    ),
		:a_lapsed_days = condition(:a_count >= 2,
				    datediff(day, dateadd(day, convert(integer, round(30.5 * :a_act_average_interval)), :a_last_gift), getdate()),
				    datediff(day, dateadd(day, 365, :a_last_gift), getdate())
				    )
	    where
		(:parameters:fund is null or :parameters:fund = :dh:a_fund) and
		(:parameters:donor is null or :parameters:donor = :dh:p_donor_partner_key)
	    ;

	    -- For the past/current flag for the overall giving to a fund, we use the history entry
	    -- with the most recent gift in it, ignoring other history entries even if they seem to
	    -- still be current.
	    update
		identity collection a_descriptives d
	    set
		:a_is_current = (select first(:dh:a_is_current) from collection a_descriptives_hist dh where :dh:a_ledger_number = :d:a_ledger_number and :dh:a_fund = :d:a_fund and :dh:p_donor_partner_key = :d:p_donor_partner_key order by :a_last_gift desc limit 1),
		:a_lapsed_days = (select first(:dh:a_lapsed_days) from collection a_descriptives_hist dh where :dh:a_ledger_number = :d:a_ledger_number and :dh:a_fund = :d:a_fund and :dh:p_donor_partner_key = :d:p_donor_partner_key order by :a_last_gift desc limit 1)
	    where
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;

	    -- Identify increases in giving.  An increase is when the history entry has a larger monthly
	    -- average than the immediately previous history entry that is not a one-time-extra gift. We
	    -- also require that the history entry and its predecessor both have at least two gifts,
            -- unless the giving interval is greater than 7 (effectively yearly), in which case any
            -- number of gifts for either produces useful results.
	    print 'Identifying increases at ' + dateformat(getdate(), 'hh:mm:ss');
	    update
		identity collection a_descriptives_hist dh
	    set
		:dh:a_increase_pct = condition(
                                :dh:a_act_average_interval > 7 or (datediff(day, :dh:a_prev_end, :dh:a_first_gift) > 212 and :dh:a_count = 1),
                                convert(double, :dh:a_amount) / convert(double, isnull((select :dh2:a_amount from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh:a_ledger_number and :dh2:a_fund = :dh:a_fund and :dh2:p_donor_partner_key = :dh:p_donor_partner_key and isnull(:dh2:a_is_extra, 0) = 0 and :dh2:a_last_gift < :dh:a_first_gift order by :dh2:a_last_gift desc limit 1), :dh:a_amount)) - 1.0,
                                condition(
                                        :dh:a_count > 1,
                                        convert(double, :dh:a_amount) / condition(round(:dh:a_act_average_interval) < 1, 1, round(:dh:a_act_average_interval)) / convert(double, isnull((select :dh2:a_amount / condition(round(:dh2:a_act_average_interval) < 1, 1, round(:dh2:a_act_average_interval)) from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh:a_ledger_number and :dh2:a_fund = :dh:a_fund and :dh2:p_donor_partner_key = :dh:p_donor_partner_key and isnull(:dh2:a_is_extra, 0) = 0 and :dh2:a_last_gift < :dh:a_first_gift and :dh2:a_count > 1 order by :dh2:a_last_gift desc limit 1), :dh:a_amount / condition(round(:dh:a_act_average_interval) < 1, 1, round(:dh:a_act_average_interval)))) - 1.0,
                                        null
                                        )
                                )
	    where
		(:parameters:fund is null or :parameters:fund = :dh:a_fund) and
		(:parameters:donor is null or :parameters:donor = :dh:p_donor_partner_key) and
                not isnull(:dh:a_is_extra, 0) != 0
	    ;
	    update
		identity collection a_descriptives_hist dh,
		collection a_descriptives d
	    set
		:dh:a_increase_pct = condition((:dh:a_count > 1 or :d:a_act_average_interval > 1.8) and :dh:a_increase_pct > 0.001, :dh:a_increase_pct, null)
	    where
		:d:a_ledger_number = :dh:a_ledger_number and
		:d:a_fund = :dh:a_fund and
		:d:p_donor_partner_key = :dh:p_donor_partner_key and
		(:parameters:fund is null or :parameters:fund = :dh:a_fund) and
		(:parameters:donor is null or :parameters:donor = :dh:p_donor_partner_key)
	    ;

	    -- Identify decreases in giving. A decrease is when the history entry has a smaller monthly
	    -- average than the immediately previous history entry that is not a one-time-extra gift. We
	    -- also require that the history entry and its predecessor both have at least two gifts,
            -- unless the giving interval is greater than 7 (effectively yearly), in which case any
            -- number of gifts for either produces useful results.
	    print 'Identifying decreases at ' + dateformat(getdate(), 'hh:mm:ss');
	    update
		identity collection a_descriptives_hist dh
	    set
		:dh:a_decrease_pct = condition(
                                :dh:a_act_average_interval > 7 or (datediff(day, :dh:a_prev_end, :dh:a_first_gift) > 212 and :dh:a_count = 1),
                                1.0 - convert(double, :dh:a_amount) / convert(double, isnull((select :dh2:a_amount from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh:a_ledger_number and :dh2:a_fund = :dh:a_fund and :dh2:p_donor_partner_key = :dh:p_donor_partner_key and isnull(:dh2:a_is_extra, 0) = 0 and :dh2:a_last_gift < :dh:a_first_gift order by :dh2:a_last_gift desc limit 1), :dh:a_amount)),
                                condition(
                                        :dh:a_count > 1,
                                        1.0 - convert(double, :dh:a_amount) / condition(round(:dh:a_act_average_interval) < 1, 1, round(:dh:a_act_average_interval)) / convert(double, isnull((select convert(double, :dh2:a_amount) / condition(round(:dh2:a_act_average_interval) < 1, 1, round(:dh2:a_act_average_interval)) from collection a_descriptives_hist dh2 where :dh2:a_ledger_number = :dh:a_ledger_number and :dh2:a_fund = :dh:a_fund and :dh2:p_donor_partner_key = :dh:p_donor_partner_key and isnull(:dh2:a_is_extra, 0) = 0 and :dh2:a_last_gift < :dh:a_first_gift and :dh2:a_count > 1 order by :dh2:a_last_gift desc limit 1), :dh:a_amount / condition(round(:dh:a_act_average_interval) < 1, 1, round(:dh:a_act_average_interval)))),
                                        null
                                        )
                                )
	    where
		(:parameters:fund is null or :parameters:fund = :dh:a_fund) and
		(:parameters:donor is null or :parameters:donor = :dh:p_donor_partner_key) and
                not isnull(:dh:a_is_extra, 0) != 0
	    ;
	    update
		identity collection a_descriptives_hist dh
	    set
		:dh:a_decrease_pct = condition(:dh:a_decrease_pct > 0.001, :dh:a_decrease_pct, null)
	    where
		(:parameters:fund is null or :parameters:fund = :dh:a_fund) and
		(:parameters:donor is null or :parameters:donor = :dh:p_donor_partner_key)
	    ;

	    -- Propagate informational flags/data to the overall giving descriptives
	    print 'Setting informational flags at ' + dateformat(getdate(), 'hh:mm:ss');
	    update
		identity collection a_descriptives d
	    set
		:a_increase_pct = (select :dh:a_increase_pct from collection a_descriptives_hist dh where :dh:a_ledger_number = :d:a_ledger_number and :dh:a_fund = :d:a_fund and :dh:p_donor_partner_key = :d:p_donor_partner_key where :a_is_extra = 0 order by :a_last_gift desc limit 1),
		:a_increase_date = (select condition(:dh:a_increase_pct is not null, :dh:a_first_gift, convert(datetime, null)) from collection a_descriptives_hist dh where :dh:a_ledger_number = :d:a_ledger_number and :dh:a_fund = :d:a_fund and :dh:p_donor_partner_key = :d:p_donor_partner_key where :a_is_extra = 0 order by :a_last_gift desc limit 1),
		:a_decrease_pct = (select :dh:a_decrease_pct from collection a_descriptives_hist dh where :dh:a_ledger_number = :d:a_ledger_number and :dh:a_fund = :d:a_fund and :dh:p_donor_partner_key = :d:p_donor_partner_key where :a_is_extra = 0 order by :a_last_gift desc limit 1),
		:a_decrease_date = (select condition(:dh:a_decrease_pct is not null, :dh:a_first_gift, convert(datetime, null)) from collection a_descriptives_hist dh where :dh:a_ledger_number = :d:a_ledger_number and :dh:a_fund = :d:a_fund and :dh:p_donor_partner_key = :d:p_donor_partner_key where :a_is_extra = 0 order by :a_last_gift desc limit 1),
		:a_is_extra = (select max(:dh:a_is_extra) from collection a_descriptives_hist dh where :dh:a_ledger_number = :d:a_ledger_number and :dh:a_fund = :d:a_fund and :dh:p_donor_partner_key = :d:p_donor_partner_key order by :a_last_gift desc limit 3),
                :a_is_approximate = (select max(:dh:a_is_approximate) from collection a_descriptives_hist dh where :dh:a_ledger_number = :d:a_ledger_number and :dh:a_fund = :d:a_fund and :dh:p_donor_partner_key = :d:p_donor_partner_key order by :a_last_gift desc limit 3)
	    where
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;

	    -- Clear out any old descriptives
	    print 'Clearing old DB descriptives at ' + dateformat(getdate(), 'hh:mm:ss');
	    delete
		/apps/kardia/data/Kardia_DB/a_descriptives/rows d
	    where
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:ledger is null or :parameters:ledger = :d:a_ledger_number) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;
	    delete
		/apps/kardia/data/Kardia_DB/a_descriptives_hist/rows dh
	    where
		(:parameters:fund is null or :parameters:fund = :dh:a_fund) and
		(:parameters:ledger is null or :parameters:ledger = :dh:a_ledger_number) and
		(:parameters:donor is null or :parameters:donor = :dh:p_donor_partner_key)
	    ;

	    -- Save to database
	    print 'Saving DB descriptives at ' + dateformat(getdate(), 'hh:mm:ss');
	    insert
		/apps/kardia/data/Kardia_DB/a_descriptives/rows
	    select
		*
	    from
		collection a_descriptives d
	    where
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:ledger is null or :parameters:ledger = :d:a_ledger_number) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    ;
	    insert
		/apps/kardia/data/Kardia_DB/a_descriptives_hist/rows dh
	    select
		*
	    from
		collection a_descriptives_hist dh
	    where
		(:parameters:fund is null or :parameters:fund = :dh:a_fund) and
		(:parameters:ledger is null or :parameters:ledger = :dh:a_ledger_number) and
		(:parameters:donor is null or :parameters:donor = :dh:p_donor_partner_key)
	    ;

	    -- Return the dataset to the caller
	    print 'Done at ' + dateformat(getdate(), 'hh:mm:ss');
	    select
		*
	    from
		collection a_descriptives_hist d
	    where
		(:parameters:fund is null or :parameters:fund = :d:a_fund) and
		(:parameters:ledger is null or :parameters:ledger = :d:a_ledger_number) and
		(:parameters:donor is null or :parameters:donor = :d:p_donor_partner_key)
	    order by
		:a_hist_id desc
	    ";
    }