SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[sp_report_1]
	@date_from date,
	@date_to date,
	@good_group_name nvarchar(MAX)
AS
BEGIN
		
	declare @date_from_int int
	declare @date_to_int int
	
	set @date_from_int = (select top 1 did from dbo.dim_date where d = @date_from )
	set @date_to_int = (select top 1 did from dbo.dim_date where d = @date_to )
	
	
	SELECT d.d as [Дата]
		, s.store_name as [Аптека]
		, g.group_name as [Группа товара]
		, g.good_name as [Номенклатура]
		, SUM(f.quantity) as [Продажи шт.]
		, SUM(f.sale_grs) as [Продажи руб., с НДС]
		, sum(f.cost_grs) as [Закупка руб., с НДС]
		, (sum(f.cost_net)/NULLIF(SUM(f.quantity),0)) as [Средняя цена закупки руб., без НДС]
		, (SUM(f.sale_net)-SUM(f.cost_net)) as [Маржа руб. без НДС]
		, (SUM(f.sale_net)-SUM(f.cost_net))/(NULLIF(SUM(f.cost_net),0))*100 as [Наценка % без НДС]
FROM [dbo].[fct_cheque] as f
		LEFT JOIN (SELECT DISTINCT cash_register_id FROM [dbo].[dim_cash_register]) as cr
		on f.cash_register_id=cr.cash_register_id
		LEFT JOIN [dbo].[dim_date] as d
		ON d.did=f.date_id
		LEFT JOIN (SELECT DISTINCT [good_id],[good_name],[group_id],[group_name]
		FROM [dbo].[dim_goods]) as g
		on f.good_id=g.good_id
		LEFT JOIN [dbo].[dim_stores] as s
		on s.store_id=f.store_id
	where date_id between @date_from_int and @date_to_int
		and g.group_name = @good_group_name
	GROUP BY d.d
		, s.store_name 
		, g.group_name
		, g.good_name
		, f.sale_net
		, f.cost_net
		
		
END