Accounts Payable Annual Activity and Balance

Here's a quick and dirty little query that you can run at the beginning of the year to see a summary of the prior year's accounts payable activity and current balances by vendor. Transactions are converted to firm currency using the average method for the month of the transaction, and balance is converted to firm currency using the daily rate as of the end of the year. Note that the balance table is outer joined, so not all balances will be returned, just ones with prior payment activity. If you want both, you would need to do some fancy footwork with a full outer join and some coalescing, which is beyond the scope of this post =).

SELECT
ap.apnum
,ap.apname
,SUM(CASE WHEN (apyr=2013) THEN apntpr/trancur.cdrate ELSE 0 END) AS '2013'
,SUM(CASE WHEN (apyr=2012) THEN apntpr/trancur.cdrate ELSE 0 END) AS '2012'
,ISNULL(apbal.apbal/balcur.cdrate,0) AS 'Balance'
,apamt.currency AS 'Currency'
FROM ap
INNER JOIN apamt ON ap.apnum=apamt.apnum
INNER JOIN currates trancur ON DATEADD(mm,apper-1,DATEADD(yy,apyr-1900,0)) BETWEEN trancur.cddate1 AND trancur.cddate2
 AND trancur.trtype='A'
 AND trancur.curcode=apamt.currency
LEFT OUTER JOIN apbal ON ap.apnum=apbal.apnum AND apbal.currency=apamt.currency
LEFT OUTER JOIN currates balcur ON '12/31/2013' BETWEEN balcur.cddate1 AND balcur.cddate2
 AND balcur.trtype='D' AND balcur.curcode=apbal.currency
GROUP BY ap.apnum,ap.apname,apbal.apbal,apamt.currency,balcur.cdrate
ORDER BY SUM(CASE WHEN (apyr=2013) THEN apntpr ELSE 0 END) DESC