CREATE PROCEDURE [dbo].[CRT_Agg_All_Except1Minute]
	@i_CRTT_ID int = null
AS
/*=============================================
 Author: Ariel Meran
 Create date: 7/12/2016
 Description: Agg the records from CRT_Data_1Minute to the other CRT tables
 Debug:
 select * from CRT_LastQuote where Next_End_period<lastQuote
 and tablename='CRT_Data_5Minutes'
 and CRTD_PairSymbol='BACUSD' 
SELECT * from  CRT_Data_1Minute A (nolock)
where CRTD_PairSymbol='EURCZK'
and A.CRTD_Period>='2016-12-13 10:05:00.000'
 =============================================*/
SET NOCOUNT ON
declare @SQL Nvarchar(max),@TableName sysname,@Source_TableName sysname,@UnitDefinition varchar(15),@rowcount int,
		@TimeUnit varchar(10),@TimeInterval varchar(10)

create table #temp (CRTD_Period datetime,
					CRTD_FirstQuote datetime,
					CRTD_LastQuote  datetime,
					CRTD_PairSymbol varchar(15),
					CRTD_BidOpeningValue float ,
					CRTD_BidClosingValue float ,
					CRTD_BidLowValue float ,
					CRTD_BidHighValue float ,
					CRTD_MidOpeningValue float ,
					CRTD_MidClosingValue float ,
					CRTD_MidLowValue float ,
					CRTD_MidHighValue float,
					StartIntervalDate datetime,
					EndIntervalDate datetime,
					ExchangeId int)

create index ix_StartIntervalDate on #temp(StartIntervalDate) 

create table #temp1 (StartPeriod datetime,
					EndPeriod datetime,
					First_Quote datetime,
					Last_Quote datetime,
					CRTD_PairSymbol varchar(15),
					BidMin_Value float,
					BidMax_Value float,
					MidMin_Value float,
					MidMax_Value float,
					BidOpen_Value float,
					BidClose_Value float,
					MidOpen_Value float,
					MidClose_Value float,
					ExchangeId int)	

create table #ChangedQuote
	(ExchangeId int,
	 CRTD_PairSymbol varchar(50),
	 NEXT_CRTD_Period dateTime,
	 LastQuote dateTime)

create table #LastQuote
(ExchangeId int,
CRTD_PairSymbol varchar(20),
max_LastQuote datetime)

insert into #LastQuote
select B.ExchangeId, B.CRTD_PairSymbol,max([CRTD_LastQuote]) as max_LastQuote
from CRT_Data_1Minute B (nolock)							
where B.CRTD_Period>getdate()-7
group by B.ExchangeId, B.CRTD_PairSymbol

update A
set LastQuote=B.max_LastQuote
from CRT_LastQuote A
	inner join #LastQuote B on A.ExchangeId = B.ExchangeId AND A.CRTD_PairSymbol=B.CRTD_PairSymbol

--Insert new symbols to CRT_LastQuote
insert into CRT_LastQuote(TableName, ExchangeId, CRTD_PairSymbol,LAST_CRTD_Period,LastQuote)
SELECT distinct TableName,B.ExchangeId, B.CRTD_PairSymbol ,'2015-01-01',max_LastQuote
from  CRT_LastQuote A
	cross JOIN #LastQuote B 
where not exists(select 1 from CRT_LastQuote C where C.TableName=A.TableName and B.ExchangeId = C.ExchangeId and B.CRTD_PairSymbol=C.CRTD_PairSymbol)

declare cTasks cursor local static for
	select CRTT_TableName, CRTT_Source_TableName, CRTT_UnitDefinition
	from CRT_tasks with (nolock)
	where CRTT_Name <> '1Minute'
	and CRTT_IsActive = 1 and CRTT_Source_TableName <> '' and (CRTT_ID = @i_CRTT_ID or @i_CRTT_ID is null)
	order by CRTT_ID
open cTasks
fetch next from cTasks into @TableName, @Source_TableName, @UnitDefinition
while @@fetch_status = 0
	begin/*1*/	
		set @TimeUnit=substring(@UnitDefinition,1,1)
		set @TimeInterval=substring(@UnitDefinition,2,4)
		SELECT @TimeUnit=CASE @TimeUnit when 'N' then 'minute'
										when 'H' then 'hour'
										when 'D' then 'day'
										when 'W' then 'Week'
										when 'M' then 'Month'		
							end															

		Set @SQL =
		'update CRT_LastQuote
		 set NEXT_CRTD_Period=dateadd('+@TimeUnit+','+@TimeInterval+', (dateadd('+@TimeUnit+', datediff('+@TimeUnit+',0,LAST_CRTD_Period) / '+@TimeInterval+' * '+@TimeInterval+', 0)))
		 where TableName='''+@TableName+''''
		 exec(@SQL)

		Set @SQL =
		'update CRT_LastQuote
		 set Next_End_period=dateadd('+@TimeUnit+','+@TimeInterval+', (dateadd('+@TimeUnit+', datediff('+@TimeUnit+',0,NEXT_CRTD_Period) / '+@TimeInterval+' * '+@TimeInterval+', 0)))
		 where TableName='''+@TableName+''''
		 exec(@SQL)

        truncate table #ChangedQuote
        insert into #ChangedQuote(ExchangeId, CRTD_PairSymbol,NEXT_CRTD_Period,LastQuote)
		select ExchangeId, CRTD_PairSymbol,NEXT_CRTD_Period,LastQuote
		from CRT_LastQuote
		where TableName=@TableName
		and Next_End_period<LastQuote

		if @@rowcount>0-- there is no need to do anything if there is no quotes after the next time interval at the destination table
		  begin/*2*/
			truncate table #temp
			truncate table #temp1
			
			set @SQL = 
			'insert into #temp(ExchangeId, CRTD_Period,CRTD_FirstQuote,CRTD_LastQuote,CRTD_PairSymbol,CRTD_BidOpeningValue,CRTD_BidClosingValue,CRTD_BidLowValue,CRTD_BidHighValue,CRTD_MidOpeningValue,
							   CRTD_MidClosingValue,CRTD_MidLowValue,CRTD_MidHighValue,StartIntervalDate,EndIntervalDate)
			SELECT A.ExchangeId, CRTD_Period ,CRTD_FirstQuote ,CRTD_LastQuote ,A.CRTD_PairSymbol ,CRTD_BidOpeningValue ,CRTD_BidClosingValue  
				  ,CRTD_BidLowValue ,CRTD_BidHighValue ,CRTD_MidOpeningValue ,CRTD_MidClosingValue ,CRTD_MidLowValue ,CRTD_MidHighValue,
				  dateadd('+@TimeUnit+', datediff('+@TimeUnit+',0,CRTD_Period) /'+ @TimeInterval+' * '+@TimeInterval+', 0) as StartIntervalDate,
				  dateadd('+@TimeUnit+','+@TimeInterval+', (dateadd('+@TimeUnit+', datediff('+@TimeUnit+',0,CRTD_Period) / '+@TimeInterval+' * '+@TimeInterval+', 0))) as EndIntervalDate
			from  ' + quotename(@Source_TableName) + ' A with (nolock,forceseek)
				inner join #ChangedQuote B on A.CRTD_PairSymbol=B.CRTD_PairSymbol AND A.ExchangeId=B.ExchangeId 
			where A.CRTD_Period>=B.NEXT_CRTD_Period'
				
			if @TimeUnit='Week'--reduce 1 day so the week will be from sunday to saturday
				begin
					set @SQL=replace(@SQL,'as StartIntervalDate','-1 as StartIntervalDate')
					set @SQL=replace(@SQL,'as EndIntervalDate','-1 as EndIntervalDate')
				end

			exec sp_executesql @SQL
			
			--delete records which already exists
			set @SQL = 'delete from A 
						from #temp A 
							inner join '+ quotename(@TableName) + 'B on A.StartIntervalDate=B.CRTD_Period and A.ExchangeId = B.ExchangeId and A.CRTD_PairSymbol=B.CRTD_PairSymbol
						where exists(select 1 from '+ quotename(@TableName) + ' as t where t.ExchangeId = A.ExchangeId and A.CRTD_PairSymbol=t.CRTD_PairSymbol)'
			exec(@sql)
									
			insert into #temp1
					(ExchangeId, StartPeriod, EndPeriod, First_Quote, Last_Quote, CRTD_PairSymbol, BidMin_Value, BidMax_Value, MidMin_Value, MidMax_Value)			
			select  ExchangeId,
					StartIntervalDate,
					EndIntervalDate,
					min(CRTD_FirstQuote) First_Quote, max(CRTD_LastQuote) Last_Quote, 
					CRTD_PairSymbol,
					min(CRTD_BidLowValue) BidMin_Value, max(CRTD_BidHighValue) BidMax_Value, 
					min(CRTD_MidLowValue) MidMin_Value, max(CRTD_MidHighValue) MidMax_Value			 
			from #temp
			group by ExchangeId, StartIntervalDate,EndIntervalDate,CRTD_PairSymbol
				
			update A
			set BidOpen_Value = B.CRTD_BidOpeningValue,
				MidOpen_Value = B.CRTD_MidOpeningValue
			FROM #temp1 A
				INNER JOIN (select ExchangeId, StartIntervalDate,CRTD_PairSymbol,CRTD_BidOpeningValue,CRTD_MidOpeningValue,ROW_NUMBER() over (partition by ExchangeId, CRTD_PairSymbol,StartIntervalDate order by crtd_period) as rownum
							from #temp) B ON A.ExchangeId = B.ExchangeId and A.CRTD_PairSymbol = B.CRTD_PairSymbol and A.StartPeriod=B.StartIntervalDate
			where B.rownum=1

			update A
			set BidClose_Value = B.CRTD_BidClosingValue,
				MidClose_Value = B.CRTD_MidClosingValue
			FROM #temp1 A
				INNER JOIN (select ExchangeId, StartIntervalDate,CRTD_PairSymbol,CRTD_BidClosingValue,CRTD_MidClosingValue,ROW_NUMBER() over (partition by ExchangeId, CRTD_PairSymbol,StartIntervalDate order by crtd_period desc) as rownum
							from #temp) B ON A.ExchangeId = B.ExchangeId and A.CRTD_PairSymbol = B.CRTD_PairSymbol and A.StartPeriod=B.StartIntervalDate
			where B.rownum=1
--delete records where there are no quotes until the top interval range. For example: if a time interval is between 10:00 to 10:15, it should verify there are quotes after 10:15		
			--delete from A
			--from #temp1 A
			--	inner join #ChangedQuote B on A.ExchangeId = B.ExchangeId and A.CRTD_PairSymbol=B.CRTD_PairSymbol 
			--where A.EndPeriod>B.LastQuote
			set @sql = 'delete from A
			from #temp1 A
				inner join #ChangedQuote B on A.ExchangeId = B.ExchangeId and A.CRTD_PairSymbol=B.CRTD_PairSymbol 
			where A.EndPeriod>B.LastQuote
				and exists(select 1 from '+ quotename(@TableName) + ' as t where t.ExchangeId = A.ExchangeId and A.CRTD_PairSymbol=t.CRTD_PairSymbol)'
			
			exec(@sql)


			begin try
				begin tran		
				Set @SQL =
				'update A
				 set LAST_CRTD_Period=B.StartPeriod,
					 NEXT_CRTD_Period=B.EndPeriod
				 from CRT_LastQuote A
					inner join (select ExchangeId, CRTD_PairSymbol, max(StartPeriod) as StartPeriod, max(EndPeriod) as EndPeriod 
							    from #temp1 
								group by ExchangeId, CRTD_PairSymbol) as B on A.ExchangeId=B.ExchangeId and A.CRTD_PairSymbol=B.CRTD_PairSymbol 
				 where TableName='''+@TableName+''''
				 exec(@SQL)

				Set @SQL =
				'update CRT_LastQuote
				 set Next_End_period=dateadd('+@TimeUnit+','+@TimeInterval+', (dateadd('+@TimeUnit+', datediff('+@TimeUnit+',0,NEXT_CRTD_Period) / '+@TimeInterval+' * '+@TimeInterval+', 0)))
				 where TableName='''+@TableName+''''
				 exec(@SQL)

				set @SQL = 
				'insert into ' + quotename(@TableName) + 
				'(ExchangeId, CRTD_Period, CRTD_FirstQuote, CRTD_LastQuote, CRTD_PairSymbol,
					CRTD_BidOpeningValue, CRTD_BidClosingValue, CRTD_BidLowValue,CRTD_BidHighValue,
					CRTD_MidOpeningValue, CRTD_MidClosingValue, CRTD_MidLowValue,CRTD_MidHighValue)
				select ExchangeId, StartPeriod, First_Quote, Last_Quote, A.CRTD_PairSymbol, BidOpen_Value, BidClose_Value, BidMin_Value, BidMax_Value,
						MidOpen_Value, MidClose_Value, MidMin_Value, MidMax_Value
				from #temp1 A'					
				exec(@SQL)			

				commit tran
			end try
			begin catch
				rollback;
				THROW;
			end catch
			--exec CRT_UpdateTasks_LastRun @CRTT_ID			
		end/*2*/
	Else print 'no new data for '+@TableName
	fetch next from cTasks into  @TableName, @Source_TableName, @UnitDefinition
end/*1*/
close cTasks
deallocate cTasks

drop table #temp
drop table #temp1
drop table #ChangedQuote