I need some help or idea to set up the formular for calculating percentage of the gross margin from sales and cost of good sold. So basically this is I'm looking for (sales-cogs)/sales. This is how it looks like in the query.
SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN [detail_price] ELSE 0 END) as current_month_sales ,SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN [detail_cogs] ELSE 0 END) as current_month_cogs ,SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN ([detail_price])-([detail_cogs]) ELSE 0 END) as current_month_gm$
Do you know how to set up a gross margin percentage base on this current_month sales and cogs? This is what I tried but it is not corrected.
SUM(CASE WHEN [p21_sales_history_report_view].period = @current_month THEN ((([detail_price])-([detail_cogs]))/([detail_price])*100) ELSE 0 END) as current_month_gm_percent
CAST(ROUND((((SUM([detail_price])-SUM([detail_cogs]))/(SUM([detail_price])))*100),2)as decimal (38,2)) as 'gm%'
However I need to add particular date to retrieve the data for the month, that's why I used case when as above. If I only add this code into CASE WHEN function I have to use GROUP BY [p21_sales_history_report_view].period at the end. That would give me wrong data and I don't want to use GROUP BY with that column. If you know how to fix this, please let me know. Thank you.
Help on setting up formular
Posted - 07/12/2012 : 22:25:59
