
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
-- =========================================================================================
-- Author:		Laba Catalin
-- Create date: 20/12/2011
-- Description:	Returns the products variables at a specific date. To obtain all fields you must join with table 'pr_products_variables' on prdv_product_code, prdv_variable_code and prdv_date_setup
-- =========================================================================================
CREATE function [dbo].[f_pr_products_variables]
(
	@family_code ais_code_short = '%',
	@product_code ais_code_short = '%',
	@date_setup date 
) 
returns @variables table 
						(
							variable_id int,
							product_code ais_code_short,
							variable_code ais_code_short,
							date_setup date 
						)
as 
begin

	set @date_setup = isnull(@date_setup, getdate())

	insert into @variables(
			product_code,		variable_code,		date_setup,						variable_id	)
			
	select  prdv_product_code,	prdv_variable_code, prdv_variable_date_setup,		prdv_variable_id		
	from pr_products_variables
		inner join (
			select	product_code = prdv_product_code,	
					variable_code = prdv_variable_code, 
					variable_date_setup = max(prdv_variable_date_setup)
			from pr_products_variables
			
			where		(@family_code = '%' or prdv_family_code = @family_code)
					and (@product_code = '%' or prdv_product_code = @product_code)
					and	prdv_variable_date_setup <= @date_setup
			group by prdv_product_code, prdv_variable_code) as t on		prdv_product_code = product_code
																	and prdv_variable_code = variable_code
																	and prdv_variable_date_setup = variable_date_setup
	
	return;
end




GO
