76 lines
2.5 KiB
Transact-SQL
76 lines
2.5 KiB
Transact-SQL
create procedure [dbo].[USP_UTIL_INIT_CRT_LastQuote]
|
|
AS
|
|
/*create table CRT_LastQuote
|
|
(TableName varchar(20),
|
|
CRTD_PairSymbol varchar(50),
|
|
LAST_CRTD_Period datetime,
|
|
NEXT_CRTD_Period datetime,
|
|
Next_End_period datetime,
|
|
LastQuote datetime)
|
|
create index ix_TableName on CRT_LastQuote(TableName)
|
|
create index ix_CRTD_PairSymbol on CRT_LastQuote(CRTD_PairSymbol)
|
|
*/
|
|
|
|
truncate table CRT_LastQuote
|
|
declare @TableName varchar(100),@sql varchar(max),@UnitDefinition varchar(15),@TimeUnit varchar(10),@TimeInterval varchar(10),
|
|
@Source_TableName varchar(100)
|
|
|
|
create table #LastQuote
|
|
(CRTD_PairSymbol varchar(20),
|
|
max_LastQuote datetime)
|
|
|
|
insert into #LastQuote
|
|
select B.CRTD_PairSymbol,max([CRTD_LastQuote]) as max_LastQuote
|
|
from CRT_Data_1Minute B (nolock)
|
|
group by B.CRTD_PairSymbol
|
|
|
|
declare cTasks cursor local static for
|
|
select CRTT_TableName,CRTT_UnitDefinition,CRTT_Source_TableName
|
|
from CRT_tasks with (nolock)
|
|
where 1=1
|
|
and CRTT_Name <> '1Minute'
|
|
and CRTT_IsActive = 1 and CRTT_Source_TableName <> ''
|
|
order by 1
|
|
open cTasks
|
|
fetch next from cTasks into @TableName, @UnitDefinition,@Source_TableName
|
|
while @@fetch_status = 0
|
|
begin
|
|
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 CRT_LastQuote(TableName,CRTD_PairSymbol,LAST_CRTD_Period)
|
|
SELECT '''+@TableName+''',CRTD_PairSymbol ,max(CRTD_Period)
|
|
from ' + quotename(@TableName) + '(nolock)
|
|
group by CRTD_PairSymbol '
|
|
exec(@SQL)
|
|
|
|
update A
|
|
set LastQuote=B.max_LastQuote
|
|
from CRT_LastQuote A
|
|
inner join #LastQuote B on A.CRTD_PairSymbol=B.CRTD_PairSymbol
|
|
where A.tableName=@TableName
|
|
|
|
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)
|
|
|
|
fetch next from cTasks into @TableName, @UnitDefinition,@Source_TableName
|
|
end
|
|
close cTasks
|
|
deallocate cTasks
|
|
drop table #LastQuote |