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?
Thanks!
Page 1 / 1
Try this. I do not have your custom fields so it is untested.
SELECT p.*, nvl(p.jan,0) + nvl(p.feb,0) + nvl(p.mar,0) + nvl(p.apr,0) + nvl(p.may,0) + nvl(p.jun,0) + nvl(p.jul,0) + nvl(p.aug,0) + nvl(p.sep,0) + nvl(p.oct,0) + nvl(p.nov,0) + nvl(p.dec,0) "YTD" 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))) p
Hello Tomas,
Thank you for the response, I run the query below and get this error message:
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))) p
Sorry, I tested with another example which only has one amount column. Then the result columns are named JAN,FEB, etc.
But in your example I guess they are named JAN_ACTUAL, FEB_ACTUAL, etc.?
If that is true the SQL below will work. Otherwise rename the columns in the first SELECT to be like the column names from your original SQL statement (the inner select below).
SELECT p.*, nvl(p.jan_Actual,0) + nvl(p.feb_Actual,0) + nvl(p.mar_Actual,0) + nvl(p.apr_Actual,0) + nvl(p.may_Actual,0) + nvl(p.jun_Actual,0) + nvl(p.jul_Actual,0) + nvl(p.aug_Actual,0) + nvl(p.sep_Actual,0) + nvl(p.oct_Actual,0) + nvl(p.nov_Actual,0) + nvl(p.dec_Actual,0) "YTD" 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))) p
