ON ACCOUNT TRACKING REPORT

If the payment was made through a contribution, the sales channel will come from T_CONTRIBUTION.  If the payment was made through an order, the sales channel will come from T_ORDER.

 

Here’s a sample query that could be used to retrieve the payment amount and sales channel from Orders:

 

SELECT      a.customer_no,

            lname = MAX(o.lname),

            pmt_method = pm.description,

            channel = sc.description,

            total_pmt = sum(a.pmt_amt) * (-1)

FROM    [dbo].t_payment a (NOLOCK)

      JOIN [dbo].T_TRANSACTION t on a.transaction_no = t.transaction_no and a.sequence_no = t.sequence_no

    JOIN [dbo].TR_PAYMENT_METHOD pm on a.pmt_method = pm.id

      JOIN [dbo].t_customer o (NOLOCK) on o.customer_no = a.customer_no

      JOIN [dbo].T_ORDER d on t.order_no = d.order_no

      JOIN [dbo].VRS_SALES_CHANNEL sc on d.channel = sc.id

WHERE a.pmt_dt <= '2009-01-01'            -- Some date as your cutoff in a variable

and pm.pmt_type = 2

GROUP BY   a.customer_no,

      pm.description,

      sc.description

 

______________________________________

Ryan Creps

Network Developer |Tessitura Network, Inc

 

 

Hi,

Did anyone get a chance to look at this...

Kindly let me know........