July 12, 2012 at 10:45 pm
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
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.
July 12, 2012 at 11:22 pm
I'm guessing that I would need the DDL for your tables, some sample data (10 to 20 rows covering about 3 months time frame), and expected results based on the sample data.
I understand what you are trying to accomplish, but having the tables, sample data, and expected results will make it easier to figure out how to accomplish the task and check the results.
The more you can do to helps us, the better answers you will get in return.
July 13, 2012 at 6:09 am
pls try below code
declare @month int=7
declare @t1 table(empid int,month int,sal int,cont int)
insert into @t1 values(1,6,1,2)
insert into @t1 values(1,6,2,4)
insert into @t1 values(1,6,3,2)
insert into @t1 values(2,6,1,2)
insert into @t1 values(2,6,2,4)
insert into @t1 values(2,6,3,2)
insert into @t1 values(3,7,1,2)
insert into @t1 values(3,7,3,2)
insert into @t1 values(3,7,4,2)
select empid,case when month=@month then (convert(decimal(10,2),SUM(sal))/SUM(cont))*100 else 0 end from @t1
group by empid,month
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply