Meu procedimento é o seguinte:
set [tb1].[SLD_VAR] = case when [CalendarDate] = [tb1].[FileDate] and ([tb2].[SLD] - [tb2].[LAG_SLD])/([tb2].[SLD]+1) >= 0.15 then 1 else 0 end,
[tb1].[StartingDate_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[StartingDate] <> [tb2].[LAG_StartingDate] then 1 else 0 end,
[tb1].[ClosingDate_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[ClosingDate] <> [tb2].[LAG_ClosingDate] then 1 else 0 end,
[tb1].[CurrentStatus_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[CurrentStatus] <> [tb2].[LAG_CurrentStatus] then 1 else 0 end,
[tb1].[Rate_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[Rate] <> [tb2].[LAG_Rate] then 1 else 0 end,
[tb1].[COF_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[COF] <> [tb2].[LAG_COF] then 1 else 0 end,
[tb1].[Term_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[Term] <> [tb2].[LAG_Term] then 1 else 0 end,
[tb1].[Term_GT_VAR] = case when [CalendarDate] = [tb1].[FileDate] and [tb2].[Term_GT] <> [tb2].[LAG_Term_GT] then 1 else 0 end
from [DataTable] as [tb1]
left join (
select [Filedate]
,cast([Deal] as inT) as [Deal]
,[SLD]
,[CurrentStatus]
,[StartingDate]
,[ClosingDate]
,[Rate]
,[COF]
,[Term],
DATEDIFF(dd,[ApplicationDate],[ClosingDate]) as [Term_GT],
LAG([StartingDate],1) over (partition BY [Deal] order by [FileDate]) as [LAG_StartingDate],
LAG([ClosingDate],1) over (partition BY [Deal] order by [FileDate]) as [LAG_ClosingDate],
LAG([CurrentStatus],1) over (partition BY [Deal] order by [FileDate]) as [LAG_CurrentStatus],
LAG([Rate],1) over (partition BY [Deal] order by [FileDate]) as [LAG_Rate],
LAG([COF],1) over (partition BY [Deal] order by [FileDate]) as [LAG_COF],
LAG([SLD],1) over (partition BY [Deal] order by [FileDate]) as [LAG_SLD],
LAG([Term],1) over (partition BY [Deal] order by [FileDate]) as [LAG_Term],
LAG(DATEDIFF(dd,[ApplicationDate],[ClosingDate]),1) over (partition BY [Deal] order by [FileDate]) as [LAG_Term_GT]
from [DataTable]
where [FileDate] >= DATEADD( day, -14, @Date)
) as [tb2]
on [tb1].[Filedate] = [tb2].[Filedate]
and [tb1].[Deal] = [tb2].[Deal]
where [tb1].[CalendarDate] >= @DateProd
end
Tendo CalendarDatee Dealjuntos como PrimaryKey, criei alguns índices mas ainda demorou bastante para executar e preciso executar esse procedimento semanalmente. Por favor me ajude com alguma estratégia para otimizar isso.