Electronic payments script

Overview

This script sums electronic payments and groups them by month and year.

Script

SELECT year(pin_add_date) AS 'Year', datename(month,pin_add_date) AS 'Month', sum(pin_check_amount) AS amount
FROM ac_payment_info (nolock)
JOIN ac_payment_method (nolock) ON apm_key = pin_apm_key
WHERE (apm_type = 'credit card' OR apm_type = 'ach' OR apm_type = 'telecheck')
AND pin_cc_preauth_flag = 0 AND pin_add_date >= '01/01/2011'
GROUP BY year(pin_add_date), month(pin_add_date), datename(month,pin_add_date)
ORDER BY year(pin_add_date), month(pin_add_date) ASC

Key tables

• ac_payment_info
• ac_payment_method