July 30, 2015 at 7:22 am
WE have a query which pulls revenue by country and client for the last 3 years. Right now we have each year being reported in separate columns but we would like to have the revenues for each year for each client to appear on one row. Below is the current query we have setup.
SELECT
p.country_code,
p.local_client_code,
wwc.local_client_name,
case when pr.fiscal_year = 2015 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end as '2015 Revenue',
case when pr.fiscal_year = 2014 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end as '2014 Revenue',
case when pr.fiscal_year = 2013 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end as '2013 Revenue'
FROM dbo.tblProject p
JOIN dbo.tblProject_Revenue pr ON p.project_number = pr.project_number and p.country_code = pr.country_code and p.fiscal_year = pr.fiscal_year
JOIN dbo.tblExchange_Rates er ON pr.rept_currency_code = er.currency_code AND er.fiscal_year = pr.fiscal_year AND er.rate_type = 'BUD'
JOIN dbo.tblWorldWide_Clients wwc ON p.local_client_code = wwc.local_client_code AND p.country_code = wwc.country_code
JOIN dbo.tblHayGroup_Organization ho ON p.unit_id = ho.unit_code AND ho.automated_status = 'Y' OR ho.country_code IN ('JPN','CHN')
GROUP BY pr.fiscal_year,p.country_code,p.local_client_code,wwc.local_client_name
July 30, 2015 at 7:33 am
tstagliano (7/30/2015)
WE have a query which pulls revenue by country and client for the last 3 years. Right now we have each year being reported in separate columns but we would like to have the revenues for each year for each client to appear on one row. Below is the current query we have setup.SELECT
p.country_code,
p.local_client_code,
wwc.local_client_name,
case when pr.fiscal_year = 2015 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end as '2015 Revenue',
case when pr.fiscal_year = 2014 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end as '2014 Revenue',
case when pr.fiscal_year = 2013 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end as '2013 Revenue'
FROM dbo.tblProject p
JOIN dbo.tblProject_Revenue pr ON p.project_number = pr.project_number and p.country_code = pr.country_code and p.fiscal_year = pr.fiscal_year
JOIN dbo.tblExchange_Rates er ON pr.rept_currency_code = er.currency_code AND er.fiscal_year = pr.fiscal_year AND er.rate_type = 'BUD'
JOIN dbo.tblWorldWide_Clients wwc ON p.local_client_code = wwc.local_client_code AND p.country_code = wwc.country_code
JOIN dbo.tblHayGroup_Organization ho ON p.unit_id = ho.unit_code AND ho.automated_status = 'Y' OR ho.country_code IN ('JPN','CHN')
GROUP BY pr.fiscal_year,p.country_code,p.local_client_code,wwc.local_client_name
What have you tried? You seem to have a cross tab currently so that each row is a new column. Remove the cross tab columns and just use the calculation. You might want to add another column so you know which year a given row is from.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2015 at 7:41 am
Not sure I am following what you are asking. My current output would look something like this.
Country_code Local_client_code Local_client_name 2015Rev 2014Rev 2013Rev
ARE 1885 client 1 1000 0 0
ARE 1885 client 1 0 2000 0
ARE 1885 client 1 0 0 1000
I ideally would want the output to look like this.
Country_code Local_client_code Local_client_name 2015Rev 2014Rev 2013Rev
ARE 1885 client 1 1000 2000 1000
July 30, 2015 at 7:51 am
tstagliano (7/30/2015)
Not sure I am following what you are asking. My current output would look something like this.Country_code Local_client_code Local_client_name 2015Rev 2014Rev 2013Rev
ARE 1885 client 1 1000 0 0
ARE 1885 client 1 0 2000 0
ARE 1885 client 1 0 0 1000
I ideally would want the output to look like this.
Country_code Local_client_code Local_client_name 2015Rev 2014Rev 2013Rev
ARE 1885 client 1 1000 2000 1000
When you don't post much for details the best we can do is guess. I am guessing that you have 99% of the cross tab here but missed the concept that you need MAX on the case expressions.
Your sql could use some help with formatting. Don't be scared to use white space so it is more legible. You might also look closely at your join to tblHayHroup_Organization. It might be ok but it might not be because the OR condition looks like it might need some parenthesis.
SELECT
p.country_code,
p.local_client_code,
wwc.local_client_name,
max(case when pr.fiscal_year = 2015 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end) as '2015 Revenue',
max(case when pr.fiscal_year = 2014 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end) as '2014 Revenue',
max(case when pr.fiscal_year = 2013 then sum(pr.local_consulting_fees*er.rate) + sum(pr.local_product_fees * er.rate) + sum(pr.local_admin_fees * er.rate) + sum(pr.local_misc_fees * er.rate) else 0 end) as '2013 Revenue'
FROM dbo.tblProject p
JOIN dbo.tblProject_Revenue pr ON p.project_number = pr.project_number
and p.country_code = pr.country_code
and p.fiscal_year = pr.fiscal_year
JOIN dbo.tblExchange_Rates er ON pr.rept_currency_code = er.currency_code
AND er.fiscal_year = pr.fiscal_year
AND er.rate_type = 'BUD'
JOIN dbo.tblWorldWide_Clients wwc ON p.local_client_code = wwc.local_client_code
AND p.country_code = wwc.country_code
JOIN dbo.tblHayGroup_Organization ho ON p.unit_id = ho.unit_code
AND ho.automated_status = 'Y' OR ho.country_code IN ('JPN','CHN')
GROUP BY pr.fiscal_year
, p.country_code
, p.local_client_code
, wwc.local_client_name
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2015 at 8:16 am
still running into issues here. So if I leave the MAX function infront of my Case statement which contains the sum function for the values I need to total up my revenue, I receive the error
"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
If I remove the sum function from the case statement, I am able to run the query, but the output is still appearing on separate rows.
Am I missing something else?
July 30, 2015 at 8:47 am
tstagliano (7/30/2015)
still running into issues here. So if I leave the MAX function infront of my Case statement which contains the sum function for the values I need to total up my revenue, I receive the error"Cannot perform an aggregate function on an expression containing an aggregate or a subquery."
If I remove the sum function from the case statement, I am able to run the query, but the output is still appearing on separate rows.
Am I missing something else?
I can't really tell here because I have nothing to work with. I didn't notice that you had a sum in there already. Oops. What would be the most help would be if you could post ddl and sample data. Please take a few minutes and look at the first link in my signature for best practices when posting questions. You might also take a look at the links in my signature about converting rows into columns since that is what you are doing here.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
July 30, 2015 at 10:49 am
tstagliano (7/30/2015)
Not sure I am following what you are asking. My current output would look something like this.Country_code Local_client_code Local_client_name 2015Rev 2014Rev 2013Rev
ARE 1885 client 1 1000 0 0
ARE 1885 client 1 0 2000 0
ARE 1885 client 1 0 0 1000
I ideally would want the output to look like this.
Country_code Local_client_code Local_client_name 2015Rev 2014Rev 2013Rev
ARE 1885 client 1 1000 2000 1000
Looking at the code... It looks like you'd just need to add pr.fiscal_year to the GROUP BY clause.
July 30, 2015 at 2:50 pm
I think this is what you are looking for.
SELECT
p.country_code,
p.local_client_code,
wwc.local_client_name,
sum(case when pr.fiscal_year = 2015 then pr.local_consulting_fees*er.rate + pr.local_product_fees * er.rate + pr.local_admin_fees * er.rate + pr.local_misc_fees * er.rate else 0 end) as '2015 Revenue',
sum(case when pr.fiscal_year = 2014 then pr.local_consulting_fees*er.rate + pr.local_product_fees * er.rate + pr.local_admin_fees * er.rate + pr.local_misc_fees * er.rate else 0 end) as '2014 Revenue',
sum(case when pr.fiscal_year = 2013 then pr.local_consulting_fees*er.rate + pr.local_product_fees * er.rate + pr.local_admin_fees * er.rate + pr.local_misc_fees * er.rate else 0 end) as '2013 Revenue'
FROM dbo.tblProject p
JOIN dbo.tblProject_Revenue pr ON p.project_number = pr.project_number and p.country_code = pr.country_code and p.fiscal_year = pr.fiscal_year
JOIN dbo.tblExchange_Rates er ON pr.rept_currency_code = er.currency_code AND er.fiscal_year = pr.fiscal_year AND er.rate_type = 'BUD'
JOIN dbo.tblWorldWide_Clients wwc ON p.local_client_code = wwc.local_client_code AND p.country_code = wwc.country_code
JOIN dbo.tblHayGroup_Organization ho ON p.unit_id = ho.unit_code AND ho.automated_status = 'Y' OR ho.country_code IN ('JPN','CHN')
GROUP BY p.country_code,p.local_client_code,wwc.local_client_name
The issue was that you had switched the scope of the CASE and SUM expressions. You had the SUM inside of the CASE when you needed the CASE inside of the SUM. You most likely did this, because you overlooked the fact that SUM is essentially addition and addition is associative. So that SUM(X) + SUM(Y) = SUM(X+Y)--as long as you account for null values appropriately.
Also, you most definitely DO NOT want the fiscal year in your GROUP BY if you want all of the fiscal years to appear on the same row.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply