234 lines
11 KiB
Transact-SQL
234 lines
11 KiB
Transact-SQL
--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 |