Count in Past Time Intervals

Question

Good morning !

I have a view with contracts, and in it there is a column for Dtiniciovigencia (DATE) and a column Dtfimvigencia (DATE). I needed to count the number of active contracts for each of the last 13 months. I had thought about the query below, and it even worked, but the execution time was high (8min) and for only one of the thirteen months I would like to pull:

DECLARE @mm13 DATE
SET @mm13=DATEADD(MONTH,DATEDIFF(MONTH,0,GETDATE()),-1)
SELECT
 COUNT(NmContrato)
 FROM Corporativo.VwComercial
 where
DtFimVigencia>@mm13
and
DtInicioVigencia<@mm13

Answer 1

The problem is a bit complex.

Evaluate the following suggestion:

-- código #1
declare @Inicio_leitura date, @Inicio_mes_atual date;
set @Inicio_mes_atual= dateadd (month, datediff (month, 0, cast (sysdatetime() as date)), 0);
set @Inicio_leitura= dateadd (month, -13, @Inicio_mes_atual);

with 
Calendario as (
SELECT @Inicio_leitura as Data
union all
SELECT dateadd (month, +1, Data)
  from Calendario 
  where dateadd (month, +1, Data) < @Inicio_mes_atual
),
Contrato_Periodo as (
SELECT vC.NmContrato, 
       dateadd (month, datediff (month, 0, vC.DtInicioVigencia), 0) as MesAno_inicio,
       dateadd (month, datediff (month, 0, vC.DtFimVigencia), 0) as MesAno_fim
  from Corporativo.vwComercial as vC 
  where vC.DtInicioVigencia < @Inicio_mes_atual
        and vC.DtFimVigencia >= @Inicio_leitura
        --and vC.DtInicioVigencia < vC.DtFimVigencia
)
SELECT year (C.Data) as Ano, month (C.Data) as Mes, 
       count (CP.NmContrato) as Ativos 
  from Contrato_Periodo as CP
       cross join Calendario as C
  where C.Data between CP.MesAno_inicio and CP.MesAno_fim
  group by year (C.Data), month (C.Data);

I have not tested; may contain error(s).

In CTE Calendario the last 13 months are generated, excluding the current month.

In CTE Contrato_Periodo contracts that were in force for at least one of the previous 13 months are selected.

At the end, through Cartesian product are generated n lines per contract, one for each month of the issue period in which the contract was active. Thereafter, contracts by month/year are accounted for.