--exec USP_UTIL_CRT_Validation '2016-12-12'
CREATE procedure [dbo].[USP_UTIL_CRT_Validation]
(@checkDay datetime,@fixMismatch bit=0)
as
declare @TableName	sysname,@UnitDefinition varchar(15),@TimeUnit varchar(10),@TimeInterval varchar(10),@sql nvarchar(max),@minLastRun datetime,@mismatchfound bit

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   )	

create table #MismatchRecords 
(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)

SELECT CRTD_PairSymbol ,max([CRTD_LastQuote]) as max_LastQuote
into   #LastQuote
from  [Knoks_Price].[dbo].[CRT_Data_1Minute](nolock)
where CRTD_Period>getdate()-7
group by CRTD_PairSymbol 


select  CRTT_TableName, CRTT_UnitDefinition
into #TablesToCheck
from CRT_tasks with (nolock)
where CRTT_Name <> '1Minute'
and CRTT_IsActive = 1 and CRTT_Source_TableName <> ''
order by crtt_id


if datepart(weekday,@checkDay)<>1
	delete from #TablesToCheck where CRTT_TableName='CRT_Data_1Week'

if datepart(day,@checkDay)<>1
	delete from #TablesToCheck where CRTT_TableName='CRT_Data_1Month'

declare cTasks cursor local static for
	select  CRTT_TableName, CRTT_UnitDefinition
	from #TablesToCheck
--where CRTT_TableName = 'CRT_Data_1Month'
open cTasks
fetch next from cTasks into @TableName, @UnitDefinition
while @@fetch_status = 0
	begin	
			set @mismatchfound=0
			truncate table #temp
			truncate table #temp1
			truncate table #MismatchRecords 
			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)'
		
		if @TimeUnit in ('minute','hour')
			set @SQL = @SQL +' where CRTD_Period BETWEEN CAST(@checkDay AS DATE) AND dateadd(ms,-3,@checkDay+1)'
		else if @TimeUnit ='Week'
			begin
				set @SQL = @SQL +' where dateadd('+@TimeUnit+', datediff('+@TimeUnit+',0,CRTD_Period) /'+ @TimeInterval+' * '+@TimeInterval+', 0)-1 =CAST(@checkDay AS DATE)
				and CRTD_Period BETWEEN CAST(@checkDay AS DATE) and CAST(@checkDay+6 AS DATE)'
				set @SQL=replace(@SQL,'as StartIntervalDate','-1 as StartIntervalDate')
				set @SQL=replace(@SQL,'as EndIntervalDate','-1 as EndIntervalDate')
			end
		else		
			set @SQL = @SQL +' where dateadd('+@TimeUnit+', datediff('+@TimeUnit+',0,CRTD_Period) /'+ @TimeInterval+' * '+@TimeInterval+', 0) =CAST(@checkDay AS DATE)'
--print @sql
		exec sp_executesql @SQL,N'@checkDay datetime ',@checkDay 				
		
		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
			inner join #LastQuote B on A.CRTD_PairSymbol=B.CRTD_PairSymbol 
		where A.EndIntervalDate<=B.max_LastQuote
		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='insert into #MismatchRecords 
			(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, CRTD_PairSymbol, BidOpen_Value, BidClose_Value, BidMin_Value, BidMax_Value,
		  	   MidOpen_Value, MidClose_Value, MidMin_Value, MidMax_Value
		from #temp1 
		except
		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
		from '+@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 

		if @@rowcount>0
			begin
				select 'There is mismatch in table '+@TableName+' at '+Convert(varchar(10),@checkDay,120)+'. Here are the mismatched records:'
				set @sql='select * from
						  (select ''Calculated'' as Source,CRTD_Period,CRTD_FirstQuote,CRTD_LastQuote,CRTD_PairSymbol,CRTD_BidOpeningValue,CRTD_BidClosingValue,CRTD_BidLowValue,CRTD_BidHighValue,
								   CRTD_MidOpeningValue,CRTD_MidClosingValue,CRTD_MidLowValue,CRTD_MidHighValue
						  from #MismatchRecords 
						  union all
						  select ''Saved'' as Source,CRTD_Period,CRTD_FirstQuote,CRTD_LastQuote,CRTD_PairSymbol,CRTD_BidOpeningValue,CRTD_BidClosingValue,CRTD_BidLowValue,CRTD_BidHighValue,
								   CRTD_MidOpeningValue,CRTD_MidClosingValue,CRTD_MidLowValue,CRTD_MidHighValue
						  from '+@TableName+' A
						  where CRTD_Period BETWEEN CAST(@checkDay AS DATE) AND CAST(dateadd(millisecond,-3,@checkDay+1) AS DATEtIME)
						  and exists(select 1 from #MismatchRecords B where A.CRTD_Period=B.CRTD_Period and A.CRTD_PairSymbol=B.CRTD_PairSymbol))s
						  order by CRTD_Period,CRTD_PairSymbol,Source'
				exec sp_executesql @SQL,N'@checkDay datetime ',@checkDay 
				set @mismatchfound=1
			end
		else
			begin
				set @sql='insert into #MismatchRecords 
					(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 CRTD_Period,CRTD_FirstQuote,CRTD_LastQuote,CRTD_PairSymbol,CRTD_BidOpeningValue,CRTD_BidClosingValue,CRTD_BidLowValue,CRTD_BidHighValue,
					 CRTD_MidOpeningValue,CRTD_MidClosingValue,CRTD_MidLowValue,CRTD_MidHighValue
				from '+@TableName+' 
				where CRTD_Period BETWEEN CAST(@checkDay AS DATE) AND CAST(dateadd(millisecond,-3,@checkDay+1) AS DATEtIME)
				except
				select StartPeriod, First_Quote, Last_Quote, CRTD_PairSymbol, BidOpen_Value, BidClose_Value, BidMin_Value, BidMax_Value,
		  			   MidOpen_Value, MidClose_Value, MidMin_Value, MidMax_Value
				from #temp1'
				exec sp_executesql @SQL,N'@checkDay datetime ',@checkDay 
				if @@rowcount>0
					begin
						select 'There is mismatch in table '+@TableName+' at '+Convert(varchar(10),@checkDay,120)+'. Here are the mismatched records:'
						select * from
							  (select 'Saved' as Source,CRTD_Period,CRTD_FirstQuote,CRTD_LastQuote,CRTD_PairSymbol,CRTD_BidOpeningValue,CRTD_BidClosingValue,CRTD_BidLowValue,CRTD_BidHighValue,
										CRTD_MidOpeningValue,CRTD_MidClosingValue,CRTD_MidLowValue,CRTD_MidHighValue
							  from #MismatchRecords 
							  union all
							  select 'Calculated' as Source,StartPeriod, First_Quote, Last_Quote, CRTD_PairSymbol, BidMin_Value, BidMax_Value, MidMin_Value, MidMax_Value,
									  MidOpen_Value, MidClose_Value,BidOpen_Value, BidClose_Value
							  from #temp1 A
							  where exists(select 1 from #MismatchRecords B where A.StartPeriod=B.CRTD_Period and A.CRTD_PairSymbol=B.CRTD_PairSymbol))s
							  order by CRTD_Period,CRTD_PairSymbol,Source				
							  set @mismatchfound=1
					end 		
				else 
					select 'There are no mismatches in '+@TableName
			end
			if @mismatchfound=1 and @fixMismatch=1
				begin
					set @sql='delete from A
								from '+@TableName+' A
									inner join #MismatchRecords B on A.CRTD_Period=b.CRTD_Period and A.CRTD_PairSymbol=B.CRTD_PairSymbol'
					exec(@sql)
					set @sql='insert into '+@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								
									inner join #MismatchRecords B on A.StartPeriod=b.CRTD_Period and A.CRTD_PairSymbol=B.CRTD_PairSymbol'
					exec(@sql)
				end
		fetch next from cTasks into @TableName, @UnitDefinition

	end
close cTasks
deallocate cTasks

drop table #temp
drop table #temp1
drop table #MismatchRecords
drop table #LastQuote
drop table #TablesToCheck