﻿
CREATE procedure [dbo].[Agg_1Minute]
	@StartDate datetime,
	@PairSymbol	NVARCHAR(50),
	@High		decimal(18,8),
	@Low		decimal(18,8),
	@Open		decimal(18,8),
	@Close		decimal(18,8),
	@ExchangeId int
AS
BEGIN
	SET NOCOUNT ON;
	--Check if rate data for the date exists 
	if(exists(SELECT 1 FROM CRT_Data_LiveChart (nolock) WHERE ExchangeId = @ExchangeId AND [CRTD_PairSymbol] = @PairSymbol AND CRTD_CreationDate >= @StartDate))
	Begin
		RETURN
	End
	--Compare dates rounded to minutes
	if (not exists(SELECT 1 FROM CRT_Data_LiveChart  WHERE ExchangeId = @ExchangeId AND [CRTD_PairSymbol] = @PairSymbol 
	AND dateadd(mi, datediff(mi, 0, CRTD_CreationDate), 0) = dateadd(mi, datediff(mi, 0, @StartDate), 0)))
	Begin
		INSERT INTO [dbo].[CRT_Data_1Minute] 
			([CRTD_Period]
				,[CRTD_FirstQuote]
				,[CRTD_LastQuote]
				,[CRTD_PairSymbol]
				,[CRTD_BidOpeningValue]
				,[CRTD_BidClosingValue]
				,[CRTD_BidLowValue]
				,[CRTD_BidHighValue]
				,[CRTD_MidOpeningValue]
				,[CRTD_MidClosingValue]
				,[CRTD_MidLowValue]
				,[CRTD_MidHighValue]
				,[CRTD_DateIn]
				,[CRTD_Rows]
				,[ExchangeId] 
				)
			SELECT CRTD_CreationDate
				,0
				,@StartDate
				,CRTD_PairSymbol
				,[CRTD_BidOpeningValue]
				,[CRTD_BidClosingValue]
				,[CRTD_BidLowValue]
				,[CRTD_BidHighValue]
				,0
				,0
				,0
				,0
				,GETUTCDATE()
				,0
				,[ExchangeId]
				FROM [CRT_Data_LiveChart] (nolock)
				WHERE CRTD_ID = (SELECT TOP 1 lc.CRTD_ID from [CRT_Data_LiveChart] lc (nolock)
					WHERE lc.ExchangeId = @ExchangeId AND lc.[CRTD_PairSymbol] = @PairSymbol 
						AND dateadd(mi, datediff(mi, 0, lc.CRTD_CreationDate), 0) <= dateadd(mi, datediff(mi, 0, @StartDate), 0) 
					ORDER BY lc.CRTD_CreationDate DESC)

		INSERT INTO [dbo].[CRT_Data_LiveChart]
			([CRTD_TargetName]
			,[CRTD_CreationDate]
			,[CRTD_FirstQuote]
			,[CRTD_LastQuote]
			,[CRTD_PairSymbol]
			,[CRTD_BidOpeningValue]
			,[CRTD_BidClosingValue]
			,[CRTD_BidLowValue]
			,[CRTD_BidHighValue]
			,[CRTD_MidOpeningValue]
			,[CRTD_MidClosingValue]
			,[CRTD_MidLowValue]
			,[CRTD_MidHighValue]
			,[CRTD_DateModified]
			,[CRTD_Rows]
			,[ExchangeId]) 
		Values
			('LiveChart' 
			,@StartDate
			,0
			,@StartDate
			,@PairSymbol
			,@Open
			,@Close
			,@Low
			,@High
			,0
			,0
			,0
			,0
			,GETUTCDATE()
			,0
			,@ExchangeId
			);
	End
	Else 
	Begin
		UPDATE [dbo].[CRT_Data_LiveChart] SET 
			[CRTD_TargetName] = 'LiveChart' 
			,[CRTD_CreationDate] = @StartDate
			,[CRTD_FirstQuote] = 0
			,[CRTD_LastQuote] = @StartDate
			,[CRTD_PairSymbol] = @PairSymbol
			,[CRTD_BidOpeningValue] =@Open
			,[CRTD_BidClosingValue]= @Close
			,[CRTD_BidLowValue]=@Low
			,[CRTD_BidHighValue] = @High
			,[CRTD_MidOpeningValue] =0
			,[CRTD_MidClosingValue]=0
			,[CRTD_MidLowValue]=0
			,[CRTD_MidHighValue]=0
			,[CRTD_DateModified]=GETUTCDATE()
			,[CRTD_Rows] =0,
			[ExchangeId] = @ExchangeId
		WHERE  [CRTD_PairSymbol] =  @PairSymbol AND ExchangeId = @ExchangeId 
			AND dateadd(mi, datediff(mi, 0, CRTD_CreationDate), 0) = dateadd(mi, datediff(mi, 0, @StartDate), 0)
	END
END
GO
