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?
2FROM ((select account as Account,account_desc as AccountDesc,
3budget_period ,amount,cf$_sp_actual_costs,cf$_sp_variance,cf$_sp_percentage,budget_year as BudgetYear, code_d as Dept from
4budget_period_amount1_cfv where code_d like nvl(upper('&Dept'),'%')
5and budget_year like nvl(upper('&Year'),'%') and budget_version like nvl(upper('&Version'),'%') and budget_period between nvl(upper('&From'),'%') and nvl(upper('&To'),'%') orderBy account )
6
7pivot(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
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).
3FROM ((select account as Account,account_desc as AccountDesc,
4budget_period ,amount,cf$_sp_actual_costs,cf$_sp_variance,cf$_sp_percentage,budget_year as BudgetYear, code_d as Dept from
5budget_period_amount1_cfv where code_d like nvl(upper('&Dept'),'%')
6and budget_year like nvl(upper('&Year'),'%') and budget_version like nvl(upper('&Version'),'%') and budget_period between nvl(upper('&From'),'%') and nvl(upper('&To'),'%') orderBy account )
7
8pivot(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
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.
1SELECT 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'),'%') orderBy 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 (1as JAN,2as FEB,3as MAR,4as APR,5as MAY,6as JUN,7as JUL,8as AUG,9as SEP,10as OCT,11as NOV,12asDEC))) 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).
1SELECT 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'),'%') orderBy 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 (1as JAN,2as FEB,3as MAR,4as APR,5as MAY,6as JUN,7as JUL,8as AUG,9as SEP,10as OCT,11as NOV,12asDEC))) p
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.
