CREATE FUNCTION GetAppropriateCRTDataRange(
	@Duration int,
	@Currency1 varchar(30),
	@Currency2 varchar(30),
	@ExchangeId int,
	@RangeSettings [DurationCRTAggrItemsList] readonly) 
	RETURNS @Result TABLE(
	Duration int,
	CRTD_Period datetime, 
	CRTD_PairSymbol nvarchar(10),
	ExchangeId int,
	Rate decimal(22, 8), 
	RateDate datetime)
AS
Begin
if (EXISTS(SELECT 1 FROM @RangeSettings s WHERE @Duration >= s.MinRange AND @Duration <= s.MaxRange AND UPPER(s.Period) = '1H'))
Begin
	INSERT INTO @Result(Duration, CRTD_Period, CRTD_PairSymbol,	ExchangeId,	Rate, RateDate) 
	SELECT 1, h.CRTD_Period, h.CRTD_PairSymbol, h.ExchangeId, h.CRTD_BidClosingValue, h.CRTD_Period 
		FROM Knoks_Prices.dbo.CRT_Data_1Hour h (nolock)
		WHERE h.CRTD_PairSymbol = @Currency1 + @Currency2 AND h.ExchangeId = @ExchangeId
End
Else
if (EXISTS(SELECT 1 FROM @RangeSettings s WHERE @Duration >= s.MinRange AND @Duration <= s.MaxRange AND UPPER(s.Period) = '2H'))
Begin
	INSERT INTO @Result(Duration, CRTD_Period, CRTD_PairSymbol,	ExchangeId,	Rate, RateDate) 
	SELECT 1, h.CRTD_Period, h.CRTD_PairSymbol, h.ExchangeId, h.CRTD_BidClosingValue, h.CRTD_Period 
		FROM Knoks_Prices.dbo.CRT_Data_2Hours h (nolock)
		WHERE h.CRTD_PairSymbol = @Currency1 + @Currency2 AND h.ExchangeId = @ExchangeId
End
Else
if (EXISTS(SELECT 1 FROM @RangeSettings s WHERE @Duration >= s.MinRange AND @Duration <= s.MaxRange AND UPPER(s.Period) = '4H'))
Begin
	INSERT INTO @Result(Duration, CRTD_Period, CRTD_PairSymbol,	ExchangeId,	Rate, RateDate) 
	SELECT 1, h.CRTD_Period, h.CRTD_PairSymbol, h.ExchangeId, h.CRTD_BidClosingValue, h.CRTD_Period 
		FROM Knoks_Prices.dbo.CRT_Data_4Hours h (nolock)
		WHERE h.CRTD_PairSymbol = @Currency1 + @Currency2 AND h.ExchangeId = @ExchangeId
End
Else
if (EXISTS(SELECT 1 FROM @RangeSettings s WHERE @Duration >= s.MinRange AND @Duration <= s.MaxRange AND UPPER(s.Period) = '8H'))
Begin
	INSERT INTO @Result(Duration, CRTD_Period, CRTD_PairSymbol,	ExchangeId,	Rate, RateDate) 
	SELECT 1, h.CRTD_Period, h.CRTD_PairSymbol, h.ExchangeId, h.CRTD_BidClosingValue, h.CRTD_Period 
		FROM Knoks_Prices..CRT_Data_8Hours h (nolock)
		WHERE h.CRTD_PairSymbol = @Currency1 + @Currency2 AND h.ExchangeId = @ExchangeId
End
Else
Begin
	INSERT INTO @Result(Duration, CRTD_Period, CRTD_PairSymbol,	ExchangeId,	Rate, RateDate) 
	SELECT 1, h.CRTD_Period, h.CRTD_PairSymbol, h.ExchangeId, h.CRTD_BidClosingValue, h.CRTD_Period 
		FROM Knoks_Prices..CRT_Data_1Day h (nolock)
		WHERE h.CRTD_PairSymbol = @Currency1 + @Currency2 AND h.ExchangeId = @ExchangeId
End
RETURN;
End
GO