The table I'm using has an amount field, a fiscal year, and a period field (among other fields). I need to select the amount field twice based on the fiscal year and period fields. The issue I'm having is how to write the SQL to select the record with fiscal year 2014 and period 10 for the prior amount. I also need to make sure that it works for 2014 / 01 and the prior period is 2013 / 12. Thanks for your help......
SELECT
a.business_unit,
a.amount as CURRENT_AMOUNT,
a.amount as PRIOR_AMOUNT,
a.fiscal_year as FY,
a.period as AP
FROM Asset_Depreciation a
WHERE
a.business_unit = 'A0001'
AND a.fiscal_year = 2014
AND a.period = 11
RESULTS
A0001 500.00 (blank) 2014 11