Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
tantcu
Yak Posting Veteran
58 Posts |
 Posted - 2012-07-12 : 22:25:59
|
| Hello,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 |
|
|
tantcu
Yak Posting Veteran
58 Posts |
Posted - 2012-07-12 : 22:48:39
|
| Before that I used this code: 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. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-07-13 : 10:15:10
|
| it might be better if you post some sample data and explain the issue you're facing.------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|
|
Help on setting up formular