Hello all the SQL experts out there….I am trying to get YTD values using the PIVOT function in SQL. I am using the budget_period_amount1_cfv custom view that also has custom fields in the output.
So far my query looks like this:
select * from
(select account as Account,account_desc as AccountDesc,
budget_period ,amount,cf$_sp_actual_costs,cf$_sp_variance,cf$_sp_percentage,budget_year as BudgetYear, code_d as Dept from
budget_period_amount1_cfv where code_d like nvl(upper('&Dept'),'%')
and budget_year like nvl(upper('&Year'),'%') and budget_version like nvl(upper('&Version'),'%') and budget_period between nvl(upper('&From'),'%') and nvl(upper('&To'),'%') order By account )
pivot(sum(amount) as Budget,sum(cf$_sp_actual_costs) as Actual,sum(cf$_sp_variance) as Variance,sum(cf$_sp_percentage) as Pecentage for budget_period in
(1 as JAN,2 as FEB,3 as MAR,4 as APR,5 as MAY,6 as JUN,7 as JUL,8 as AUG,9 as SEP,10 as OCT,11 as NOV,12 as DEC))
I can get all the month values, (1...12) but would like a column at the end of the data output that sums all months to get a YTD value. Does anyone know how to do this?