Knocks/BackEnd/Knoks.PriceDB/Stored Procedures/USP_CRT_Agg_1Minute.sql

CREATE procedure [dbo].[USP_CRT_Agg_1Minute]

	@StartDate datetime = NULL,
	@1MinFromStartDate Bit = 0
	
AS
SET NOCOUNT ON

declare @EndDate datetime,
		@RowsAffected int,
		@RowsToProcess int

SET	@RowsAffected = 0

SET @EndDate = dateadd(millisecond, -datepart(millisecond, getdate()) -datepart(second, getdate())*1000 - 3, getdate())
IF @StartDate is NULL
	Begin
		select @StartDate = Max([CRTD_Period]) from [dbo].[CRT_Data_1Minute]
		select @StartDate = isnull(@StartDate, DateAdd(minute,-1,Cast(convert(char(16), getdate(),121) as datetime))) 
	End

ELSE 
	Begin
		set @StartDate = Cast(convert(char(16), @StartDate,121) as datetime)
		IF @1MinFromStartDate = 1 
		Begin
			Set @EndDate = DateAdd(minute, 1 , @StartDate)
			set @EndDate = dateadd(millisecond, -datepart(millisecond, @EndDate) -datepart(second, @EndDate)*1000 - 3, @EndDate)
		End
	End

--select @StartDate StartDate , @EndDate EndDate
if @StartDate >= @EndDate 
	Begin
		print 'Start Date is not greater than End Date'
		return

	End
IF Exists (select 1 from CRT_Data_1Minute where [CRTD_Period] > @StartDate)
	Begin
		select 'Period Already Exists' as [Reason]
		return
	End
select top 0 
	 cast([Id] as bigint) AS [Id],
       [PairSymbol],
      [BidReceivedDate],
      [Bid],
	  [Mid]
into #QuotesHist
from YB_Quotes.dbo.[SpotRatesHistory] with (nolock)

IF cast(@StartDate as date) = cast(@EndDate as date)
	Begin
		insert 	into #QuotesHist
		select [Id],[PairSymbol],[BidReceivedDate],[Bid],[Mid]
		from YB_Quotes.dbo.[SpotRatesHistory] with (nolock)
		where 1=1
		and cast([BidReceivedDate] as date) = cast(@StartDate as date)
		and [BidReceivedDate] between @StartDate and  @EndDate
	End
Else 
	Begin
		insert 	into #QuotesHist
		select [Id],[PairSymbol],[BidReceivedDate],[Bid],[Mid]
		from YB_Quotes.dbo.[SpotRatesHistory] with (nolock)
		where 1=1
		and [BidReceivedDate] between @StartDate and  @EndDate	
	End

select
       [PairSymbol],
       convert(varchar(16),[BidReceivedDate],121) as [minute],
       min([Bid]) as  Bid_Low ,  --- Low
	   max([Bid]) as  Bid_High, --- High
	   min([Mid]) as  Mid_Low ,  --- Low
	   max([Mid]) as  Mid_High, --- High
	   Count([Id]) as Cnt
into #c2
from #QuotesHist with (nolock)
where 1=1
and [BidReceivedDate] between @StartDate and  @EndDate
--and [PairSymbol] = 'EURUSD'
group by convert(varchar(16), [BidReceivedDate] ,121) ,[PairSymbol]

select
       [PairSymbol],
	   convert(varchar(16),[BidReceivedDate] ,121) as [minute],
	   [BidReceivedDate] AS [DateQuote],
	    
       [Bid] AS Bid_Val,
	   [Mid] AS Mid_Val,
	   ROW_NUMBER() over (partition by [PairSymbol],convert(varchar(16),[BidReceivedDate] ,121) order by [BidReceivedDate] ) as Bid_start,
       ROW_NUMBER() over (partition by [PairSymbol],convert(varchar(16),[BidReceivedDate] ,121) order by [BidReceivedDate] desc) as Bid_end
into #c3
from #QuotesHist with (nolock)
where 1=1
and [BidReceivedDate] between @StartDate and  @EndDate
--and [PairSymbol] = 'EURUSD'

select
       CM.[CRTD_PairSymbol] AS PairSymbol,
	   CM.CRTD_Period AS [minute],
	   CM.[CRTD_BidClosingValue] ,
	   CM.[CRTD_MidClosingValue] 	
into #c4	    
from YB_Charts.[dbo].[CRT_Data_1Minute] CM with (nolock)
	Inner Join #c2 as c2 ON CM.[CRTD_PairSymbol] = c2.[PairSymbol] collate SQL_Latin1_General_CP1_CI_AS and  CM.CRTD_Period =   DateAdd(minute, -1, c2.[minute])
where 1=1

create index ix_c2 on #c2(PairSymbol,[minute])
create index ix_c3 on #c3(PairSymbol,[minute])
create index ix_c3_Bid_start on #c3(Bid_start)
create index ix_c3_Bid_end on #c3(Bid_end)
create index ix_c4 on #c4(PairSymbol,[minute])

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])
select  distinct c2.[minute] [CRTD_Period], 
		c3.[DateQuote] [CRTD_FirstQuote],
		c3_Bid.[DateQuote] [CRTD_LastQuote],
		c2.PairSymbol,
		IsNull(c4.[CRTD_BidClosingValue],c3.Bid_Val)    as Bid_OpenValue,  
		IsNull(c3_Bid.Bid_Val,0)   as Bid_CloseValue, 
		Bid_Low, 
		Bid_High,
		IsNull(c4.[CRTD_MidClosingValue],c3.Mid_Val)     as Mid_OpenValue, 
		IsNull(c3_Bid.Mid_Val,0)  as Mid_CloseValue, 
		Mid_Low,
		Mid_High,
		GetDate() As Date_Inserted,
		c2.Cnt
from   #c2 as c2
       Inner join #c3 as c3 on c2.[PairSymbol] = c3.[PairSymbol] and c2.[minute] = c3.[minute] and Bid_start =1
       Inner join #c3 as c3_Bid on c2.[PairSymbol] = c3_Bid.[PairSymbol] and c2.[minute] = c3_Bid.[minute] and c3_Bid.Bid_end =1
	   left join #c4 as c4 on c2.[PairSymbol] = c4.[PairSymbol]  collate SQL_Latin1_General_CP1_CI_AS and c2.[minute] = DateAdd(minute,1,c4.[minute]) 
Where 1=1
and Not Exists (select 1 from [CRT_Data_1Minute] XX with (nolock) where XX.[CRTD_PairSymbol] = c2.PairSymbol collate SQL_Latin1_General_CP1_CI_AS 
	            and XX.[CRTD_Period] = c2.[minute])
order by 1,4

select @RowsAffected = @@RowCount
 
update CRT_Tasks
set CRTT_LastRun = @EndDate,
	CRTT_LastRowCount = @RowsAffected,
	CRTT_LastModifiedDate = GetDate()
where CRTT_TableName = 'CRT_Data_1Minute'
			
--select @RowsToProcess RowsToProcess, @RowsAffected RowsAffected
--select * from CRT_Tasks where CRTT_ID = 1
print Cast (@RowsAffected as varchar(10)) + ' Rows Processed'


--- select top 100 * from [dbo].[CRT_Data_1Minute] order by 1 desc
--- select * from [CRT_Tasks]

drop table #QuotesHist
drop table #c2
drop table #c3
drop table #c4