56 lines
2.5 KiB
Transact-SQL
56 lines
2.5 KiB
Transact-SQL
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 |