Description
Properties
| Name | Value |
|---|---|
| ANSI Nulls ON | True |
| Quoted Identifier ON | True |
| Encrypted | False |
| Execute As | |
| Assembly |
Parameters
1| Name | Data Type | Length | Description |
|---|---|---|---|
| @yesterday | date | 3 |
SQL Script
Copy
SET QUOTED_IDENTIFIER, ANSI_NULLS ON
GO
--exec monitoreos_batch_10_new '2017-07-17'
--select * from monitoreos_resultados where tabla = 'cendeu1'
--delete from monitoreos_resultados where tabla = 'cendeu1'
--select * from monitoreos_resultados where tabla = 'cendeu' and CONVERT(date,fecha) = '2017-07-17'
CREATE OR ALTER PROCEDURE dbo.monitoreos_batch_10_NEW
@yesterday date = null
AS
BEGIN
SET NOCOUNT ON;
declare @r_id_cendeu int
declare @r_cuit varchar(20)
declare @yesterday_1 date
declare @cant0 int = 0
declare @cant1 int = 0
declare @cant2 int = 0
declare @cant3 int = 0
declare @cant4 int = 0
if (@yesterday is null) begin set @yesterday = GETDATE() - 1 end
set @yesterday_1 = @yesterday
/* Cuando se quiere desde una fecha anterior hasta hoy, agregar este set: */
if (@yesterday = '2018-03-07')
begin
set @yesterday_1 = '2018-02-01'
end
/* */
if (@yesterday = '2018-03-07') /* cuando sabemos que hay muchos registros en deudas y no queremos que pierda tiempo contando */
begin
set @cant0 = 1
set @cant1 = 2
set @cant2 = 2
set @cant3 = 2
set @cant4 = 2
end
else
begin
set @cant0 = (select COUNT(distinct cuit)
from monitoreos
where visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday))
set @cant2 = (select COUNT(distinct iden) from cendeu where CONVERT(date, modified) between @yesterday_1 and @yesterday and vigente = 1)
set @cant1 = (select COUNT(distinct iden) from cendeu where CONVERT(date, created) between @yesterday_1 and @yesterday and vigente = 1)
set @cant3 = (select COUNT(distinct iden) from cendeu_temp where CONVERT(date, modified) between @yesterday_1 and @yesterday and vigente = 1)
set @cant4 = (select COUNT(distinct iden) from cendeu_temp where CONVERT(date, created) between @yesterday_1 and @yesterday and vigente = 1)
end
--select @cant0, @cant1, @cant2, @cant3, @cant4
if (@cant1 > 0 or @cant2 > 0)
BEGIN
IF ((@cant1 + @cant2) < @cant0) -- Recorre por cendeu
BEGIN
DECLARE cec CURSOR
FOR
select iden, id_cendeu
from cendeu
where CONVERT(date, created) between @yesterday_1 and @yesterday
and vigente = 1
and CONVERT(int,situacion) > 1
union
select iden, id_cendeu
from cendeu
where CONVERT(date, modified) between @yesterday_1 and @yesterday
and vigente = 1
and CONVERT(int,situacion) > 1
OPEN cec
FETCH next from cec INTO @r_cuit, @r_id_cendeu
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
if (select COUNT(*)
from monitoreos
where cuit = @r_cuit
and visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)) > 0
begin
insert into monitoreos_resultados
(id_tabla, tabla, cuit, seccion_mantenimiento, fecha, novedad, tipo_novedad)
values (@r_id_cendeu, 'cendeu', @r_cuit, 10, @yesterday, 1, 'Bancos');
update monitoreos
set fecha_ultimo_procesamiento = @yesterday,
fecha_modificacion = @yesterday
where cuit = @r_cuit
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)
end
FETCH next from cec INTO @r_cuit, @r_id_cendeu
END
CLOSE cec
DEALLOCATE cec
END
ELSE
BEGIN
-- recorre por monitoreo
DECLARE bn CURSOR
FOR
select distinct cuit
from monitoreos
where visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
OPEN bn
Fetch next from bn INTO @r_cuit
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
DECLARE cendeuc CURSOR
FOR
select distinct(id_cendeu)
from cendeu
where iden = @r_cuit
and vigente = 1
and ((convert(date,created) between @yesterday_1 and @yesterday and created is not null) or (convert(date,modified) between @yesterday_1 and @yesterday and modified is not null))
and CONVERT(int,situacion) > 1
OPEN cendeuc
FETCH next from cendeuc INTO @r_id_cendeu
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
insert into monitoreos_resultados
(id_tabla, tabla, cuit, seccion_mantenimiento, fecha, novedad, tipo_novedad)
values (@r_id_cendeu, 'cendeu', @r_cuit, 10, @yesterday, 1, 'Bancos');
update monitoreos
set fecha_ultimo_procesamiento = @yesterday,
fecha_modificacion = @yesterday
where cuit= @r_cuit
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)
FETCH next from cendeuc INTO @r_id_cendeu
END
CLOSE cendeuc
DEALLOCATE cendeuc
FETCH next from bn INTO @r_cuit
END
CLOSE bn
DEALLOCATE bn
END
END
if (@cant3 > 0 or @cant4 > 0)
BEGIN
IF ((@cant3 + @cant4) < @cant0) -- Recorre por cendeu_temp
BEGIN
DECLARE cec2 CURSOR
FOR
select iden, id_cendeu
from cendeu_temp
where CONVERT(date, created) between @yesterday_1 and @yesterday
and vigente = 1
and CONVERT(int,situacion) > 1
union
select iden, id_cendeu
from cendeu_temp
where CONVERT(date, modified) between @yesterday_1 and @yesterday
and vigente = 1
and CONVERT(int,situacion) > 1
OPEN cec2
FETCH next from cec2 INTO @r_cuit, @r_id_cendeu
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
if (select COUNT(*)
from monitoreos
where cuit = @r_cuit
and visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)) > 0
begin
insert into monitoreos_resultados
(id_tabla, tabla, cuit, seccion_mantenimiento, fecha, novedad, tipo_novedad)
values (@r_id_cendeu, 'cendeu_temp', @r_cuit, 10, @yesterday, 1, 'Bancos');
update monitoreos
set fecha_ultimo_procesamiento = @yesterday,
fecha_modificacion = @yesterday
where cuit = @r_cuit
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)
end
FETCH next from cec2 INTO @r_cuit, @r_id_cendeu
END
CLOSE cec2
DEALLOCATE cec2
END
ELSE
BEGIN
-- recorre por monitoreo
DECLARE bn2 CURSOR
FOR
select distinct cuit
from monitoreos
where visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
OPEN bn2
Fetch next from bn2 INTO @r_cuit
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
DECLARE cendeuc2 CURSOR
FOR
select distinct(id_cendeu)
from cendeu_temp
where iden = @r_cuit
and vigente = 1
and ((convert(date,created) between @yesterday_1 and @yesterday and created is not null) or (convert(date,modified) between @yesterday_1 and @yesterday and modified is not null))
and CONVERT(int,situacion) > 1
OPEN cendeuc2
FETCH next from cendeuc2 INTO @r_id_cendeu
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
insert into monitoreos_resultados
(id_tabla, tabla, cuit, seccion_mantenimiento, fecha, novedad, tipo_novedad)
values (@r_id_cendeu, 'cendeu_temp', @r_cuit, 10, @yesterday, 1, 'Bancos');
update monitoreos
set fecha_ultimo_procesamiento = @yesterday,
fecha_modificacion = @yesterday
where cuit= @r_cuit
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)
FETCH next from cendeuc2 INTO @r_id_cendeu
END
CLOSE cendeuc2
DEALLOCATE cendeuc2
FETCH next from bn2 INTO @r_cuit
END
CLOSE bn2
DEALLOCATE bn2
END
END
END
GO
GO
--exec monitoreos_batch_10_new '2017-07-17'
--select * from monitoreos_resultados where tabla = 'cendeu1'
--delete from monitoreos_resultados where tabla = 'cendeu1'
--select * from monitoreos_resultados where tabla = 'cendeu' and CONVERT(date,fecha) = '2017-07-17'
CREATE OR ALTER PROCEDURE dbo.monitoreos_batch_10_NEW
@yesterday date = null
AS
BEGIN
SET NOCOUNT ON;
declare @r_id_cendeu int
declare @r_cuit varchar(20)
declare @yesterday_1 date
declare @cant0 int = 0
declare @cant1 int = 0
declare @cant2 int = 0
declare @cant3 int = 0
declare @cant4 int = 0
if (@yesterday is null) begin set @yesterday = GETDATE() - 1 end
set @yesterday_1 = @yesterday
/* Cuando se quiere desde una fecha anterior hasta hoy, agregar este set: */
if (@yesterday = '2018-03-07')
begin
set @yesterday_1 = '2018-02-01'
end
/* */
if (@yesterday = '2018-03-07') /* cuando sabemos que hay muchos registros en deudas y no queremos que pierda tiempo contando */
begin
set @cant0 = 1
set @cant1 = 2
set @cant2 = 2
set @cant3 = 2
set @cant4 = 2
end
else
begin
set @cant0 = (select COUNT(distinct cuit)
from monitoreos
where visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday))
set @cant2 = (select COUNT(distinct iden) from cendeu where CONVERT(date, modified) between @yesterday_1 and @yesterday and vigente = 1)
set @cant1 = (select COUNT(distinct iden) from cendeu where CONVERT(date, created) between @yesterday_1 and @yesterday and vigente = 1)
set @cant3 = (select COUNT(distinct iden) from cendeu_temp where CONVERT(date, modified) between @yesterday_1 and @yesterday and vigente = 1)
set @cant4 = (select COUNT(distinct iden) from cendeu_temp where CONVERT(date, created) between @yesterday_1 and @yesterday and vigente = 1)
end
--select @cant0, @cant1, @cant2, @cant3, @cant4
if (@cant1 > 0 or @cant2 > 0)
BEGIN
IF ((@cant1 + @cant2) < @cant0) -- Recorre por cendeu
BEGIN
DECLARE cec CURSOR
FOR
select iden, id_cendeu
from cendeu
where CONVERT(date, created) between @yesterday_1 and @yesterday
and vigente = 1
and CONVERT(int,situacion) > 1
union
select iden, id_cendeu
from cendeu
where CONVERT(date, modified) between @yesterday_1 and @yesterday
and vigente = 1
and CONVERT(int,situacion) > 1
OPEN cec
FETCH next from cec INTO @r_cuit, @r_id_cendeu
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
if (select COUNT(*)
from monitoreos
where cuit = @r_cuit
and visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)) > 0
begin
insert into monitoreos_resultados
(id_tabla, tabla, cuit, seccion_mantenimiento, fecha, novedad, tipo_novedad)
values (@r_id_cendeu, 'cendeu', @r_cuit, 10, @yesterday, 1, 'Bancos');
update monitoreos
set fecha_ultimo_procesamiento = @yesterday,
fecha_modificacion = @yesterday
where cuit = @r_cuit
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)
end
FETCH next from cec INTO @r_cuit, @r_id_cendeu
END
CLOSE cec
DEALLOCATE cec
END
ELSE
BEGIN
-- recorre por monitoreo
DECLARE bn CURSOR
FOR
select distinct cuit
from monitoreos
where visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
OPEN bn
Fetch next from bn INTO @r_cuit
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
DECLARE cendeuc CURSOR
FOR
select distinct(id_cendeu)
from cendeu
where iden = @r_cuit
and vigente = 1
and ((convert(date,created) between @yesterday_1 and @yesterday and created is not null) or (convert(date,modified) between @yesterday_1 and @yesterday and modified is not null))
and CONVERT(int,situacion) > 1
OPEN cendeuc
FETCH next from cendeuc INTO @r_id_cendeu
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
insert into monitoreos_resultados
(id_tabla, tabla, cuit, seccion_mantenimiento, fecha, novedad, tipo_novedad)
values (@r_id_cendeu, 'cendeu', @r_cuit, 10, @yesterday, 1, 'Bancos');
update monitoreos
set fecha_ultimo_procesamiento = @yesterday,
fecha_modificacion = @yesterday
where cuit= @r_cuit
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)
FETCH next from cendeuc INTO @r_id_cendeu
END
CLOSE cendeuc
DEALLOCATE cendeuc
FETCH next from bn INTO @r_cuit
END
CLOSE bn
DEALLOCATE bn
END
END
if (@cant3 > 0 or @cant4 > 0)
BEGIN
IF ((@cant3 + @cant4) < @cant0) -- Recorre por cendeu_temp
BEGIN
DECLARE cec2 CURSOR
FOR
select iden, id_cendeu
from cendeu_temp
where CONVERT(date, created) between @yesterday_1 and @yesterday
and vigente = 1
and CONVERT(int,situacion) > 1
union
select iden, id_cendeu
from cendeu_temp
where CONVERT(date, modified) between @yesterday_1 and @yesterday
and vigente = 1
and CONVERT(int,situacion) > 1
OPEN cec2
FETCH next from cec2 INTO @r_cuit, @r_id_cendeu
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
if (select COUNT(*)
from monitoreos
where cuit = @r_cuit
and visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)) > 0
begin
insert into monitoreos_resultados
(id_tabla, tabla, cuit, seccion_mantenimiento, fecha, novedad, tipo_novedad)
values (@r_id_cendeu, 'cendeu_temp', @r_cuit, 10, @yesterday, 1, 'Bancos');
update monitoreos
set fecha_ultimo_procesamiento = @yesterday,
fecha_modificacion = @yesterday
where cuit = @r_cuit
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)
end
FETCH next from cec2 INTO @r_cuit, @r_id_cendeu
END
CLOSE cec2
DEALLOCATE cec2
END
ELSE
BEGIN
-- recorre por monitoreo
DECLARE bn2 CURSOR
FOR
select distinct cuit
from monitoreos
where visible = 1
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
OPEN bn2
Fetch next from bn2 INTO @r_cuit
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
DECLARE cendeuc2 CURSOR
FOR
select distinct(id_cendeu)
from cendeu_temp
where iden = @r_cuit
and vigente = 1
and ((convert(date,created) between @yesterday_1 and @yesterday and created is not null) or (convert(date,modified) between @yesterday_1 and @yesterday and modified is not null))
and CONVERT(int,situacion) > 1
OPEN cendeuc2
FETCH next from cendeuc2 INTO @r_id_cendeu
WHILE (@@FETCH_STATUS <> -1 )
BEGIN
insert into monitoreos_resultados
(id_tabla, tabla, cuit, seccion_mantenimiento, fecha, novedad, tipo_novedad)
values (@r_id_cendeu, 'cendeu_temp', @r_cuit, 10, @yesterday, 1, 'Bancos');
update monitoreos
set fecha_ultimo_procesamiento = @yesterday,
fecha_modificacion = @yesterday
where cuit= @r_cuit
and seccion_mantenimiento = 10
and (fecha_vigencia_mantenimiento is null or fecha_vigencia_mantenimiento >= @yesterday)
and (fecha_desde is null or fecha_desde <= @yesterday)
FETCH next from cendeuc2 INTO @r_id_cendeu
END
CLOSE cendeuc2
DEALLOCATE cendeuc2
FETCH next from bn2 INTO @r_cuit
END
CLOSE bn2
DEALLOCATE bn2
END
END
END
GO
Depends On
3Used By
No items found