SELECT b1.bp_internal_id ,
b1.bp_linkto_pa ,
b1.bp_type
INTO MEMORY\bp_filter
FROM billing_parties b1 
INNER JOIN billing_parties b2_pa ON
b1.bp_linkto_pa = b2_pa.bp_linkto_pa
WHERE (b2_pa.bp_level = 0) AND
((b1.bp_type = 0) OR (b1.bp_type = 1) OR (b1.bp_type = 2)) AND
(b2_pa.bp_type = 0)
GROUP BY b1.bp_internal_id;

 
CREATE INDEX i_bp_filter ON MEMORY\bp_filter (bp_internal_id);

SELECT trans_internal_id ,
trans_linkto_bp ,
trans_linkto_pa
INTO MEMORY\trans_filter
FROM transactions
WHERE (trans_date >= '0001-01-01') AND (trans_date < '2004-11-01');
 
CREATE INDEX i_trans_filter ON MEMORY\trans_filter (trans_internal_id);

SELECT ta_internal_id,
ta_linkto_bp,
bp.bp_internal_id,
SUM(ta_amount) AS total_balance ,
CAST('0001-01-01' AS DATE) AS last_sess_date ,
MAX(IF (trans_type = 1 THEN trans_date ELSE NULL )) AS last_payment_date ,
bp.bp_bill_last_print AS last_bill_date ,
bp.bp_insform_print AS last_insform_date ,
1 as age_group_id ,
0.0 as age_current ,
0.0 as age_30 ,
0.0 as age_60 ,
0.0 as age_90 ,
0.0 as age_120 ,
ta_linkto_trans ,
trans_linkto_th ,
trans_linkto_pr ,
trans_linkto_acc ,
pa.bp_internal_id AS pa_bp_internal_id ,
pa.bp_linkto_pa ,
pa.bp_last_name AS pa_last_name ,
pa.bp_first_name AS pa_first_name ,
pa.bp_middle_initial AS pa_middle_initial ,
bp.bp_internal_id ,
bp.bp_type ,
bp.bp_level ,
ref_internal_id AS group_id
FROM billing_parties pa 
INNER JOIN billing_parties bp ON
pa.bp_linkto_pa = bp.bp_linkto_pa AND pa.bp_type = 0 
INNER JOIN MEMORY\bp_filter ON
bp.bp_internal_id = bp_filter.bp_internal_id 
INNER JOIN transactions ON
bp.bp_linkto_pa = trans_linkto_pa 
INNER JOIN MEMORY\trans_filter tf ON
trans_internal_id = tf.trans_internal_id 
LEFT OUTER JOIN transaction_amount ON
bp.bp_internal_id = ta_linkto_bp AND ta_linkto_trans = trans_internal_id 
LEFT OUTER JOIN insurance_facesheet ON
bp.bp_linkto_ins = ins_internal_id 
LEFT OUTER JOIN referring_source ON
pa.bp_linkto_ref = ref_internal_id
GROUP BY ta_internal_id,
bp.bp_internal_id,
age_group_id, group_id
ORDER BY group_name,
group_id,
pa.bp_last_name,
pa.bp_first_name,
pa.bp_middle_initial,
pa.bp_internal_id,
bp.bp_type,
bp.bp_level NOCASE
/*NOJOINOPTIMIZE*/;