--exec USP_UTIL_CRT_Validation '2016-06-03'
CREATE procedure [dbo].[USP_UTIL_CRT_ReAggDay]
(@checkDay datetime)
as
declare @TableName	sysname,@UnitDefinition varchar(15),@TimeUnit varchar(10),@TimeInterval varchar(10),@sql nvarchar(max),@minLastRun datetime,@Source_TableName sysname

create table #temp (CRTD_ID int,
					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)

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,
					Min_RowId int,
					Max_RowId int,
					BidOpen_Value float,
					BidClose_Value float,
					MidOpen_Value float,
					MidClose_Value float   )	


declare cTasks cursor local static for
	select CRTT_TableName, CRTT_UnitDefinition
	from CRT_tasks with (nolock)
	where CRTT_Name <> '1Minute'
	and CRTT_IsActive = 1 and CRTT_Source_TableName <> '' 
	order by CRTT_ID
open cTasks
fetch next from cTasks into @TableName, @UnitDefinition
while @@fetch_status = 0
	begin	
			truncate table #temp
			truncate table #temp1
			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 = 
		'insert into #temp(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 CRTD_Period ,CRTD_FirstQuote ,CRTD_LastQuote ,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(millisecond,-3,dateadd('+@TimeUnit+','+@TimeInterval+', (dateadd('+@TimeUnit+', datediff('+@TimeUnit+',0,CRTD_Period) / '+@TimeInterval+' * '+@TimeInterval+', 0)))) as EndIntervalDate
		from  YB_Charts.dbo.CRT_Data_1Minute (nolock)
		where CRTD_Period BETWEEN CAST(@checkDay AS DATE) AND CAST(dateadd(millisecond,-3,@checkDay+1) AS DATEtIME)'
		exec sp_executesql @SQL,N'@checkDay datetime ',@checkDay 				
		if @TimeUnit='Week'--change so the week will be from sunday to saturday
			begin
				UPDATE #temp
				SET StartIntervalDate=StartIntervalDate-1,
					EndIntervalDate=EndIntervalDate-1
			end				
		insert into #temp1
				(StartPeriod, EndPeriod, First_Quote, Last_Quote, CRTD_PairSymbol, BidMin_Value, BidMax_Value, MidMin_Value, MidMax_Value)			
		select  StartIntervalDate,
				EndIntervalDate,
				min(CRTD_FirstQuote) First_Quote, max(CRTD_LastQuote) Last_Quote, 
				A.CRTD_PairSymbol,
				min(CRTD_BidLowValue) BidMin_Value, max(CRTD_BidHighValue) BidMax_Value, 
				min(CRTD_MidLowValue) MidMin_Value, max(CRTD_MidHighValue) MidMax_Value			 
		from #temp A
		group by StartIntervalDate,EndIntervalDate,A.CRTD_PairSymbol
				
		update A
		set BidOpen_Value = B.CRTD_BidOpeningValue,
			MidOpen_Value = B.CRTD_MidOpeningValue
		FROM #temp1 A
			INNER JOIN (select StartIntervalDate,CRTD_PairSymbol,CRTD_BidOpeningValue,CRTD_MidOpeningValue,ROW_NUMBER() over (partition by CRTD_PairSymbol,StartIntervalDate order by crtd_period) as rownum
						from #temp) B ON 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 StartIntervalDate,CRTD_PairSymbol,CRTD_BidClosingValue,CRTD_MidClosingValue,ROW_NUMBER() over (partition by CRTD_PairSymbol,StartIntervalDate order by crtd_period desc) as rownum
						from #temp) B ON A.CRTD_PairSymbol = B.CRTD_PairSymbol and A.StartPeriod=B.StartIntervalDate
		where B.rownum=1

		set @SQL = 
				'delete from ' + quotename(@TableName) + 
				' where CRTD_Period BETWEEN CAST(@checkDay AS DATE) AND CAST(dateadd(millisecond,-3,@checkDay+1) AS DATEtIME)'
		exec sp_executesql @SQL,N'@checkDay datetime ',@checkDay 				
		
		set @SQL = 
				'insert into ' + quotename(@TableName) + 
				'(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 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)			
	fetch next from cTasks into @TableName, @UnitDefinition

	end
close cTasks
deallocate cTasks

drop table #temp
drop table #temp1