Para la primera consulta se decidió hacer uso de una vista. En este caso primero se intentó encontrar una forma de usar la función agregada DATEADD y restar los resultados para calcular la cantidad de depósitos realizados y los que que se hubieran realizado si el saldo no hubiera quedado negativo, esto estaba generando problemas, sin embargo investigando se descubrió la función agregada DATEDIFF que simplificó mucho más el proceso. Para aprender a usar las vistas fue de gran ayuda este video:
https://www.youtube.com/watch?v=MK_dWEcltWY
Además para este caso de puso como criterio de filtración que la fecha inicio de las cuentas objetivo por analizar fueran iguales o menores al 2018-05-31 ya que el último día del archivo de simulación es el 2018-06-30 y como lo indica la sección DOS de la tercera tarea programada los ahorros en la CO son mensuales por lo que esta sería la última fecha posible para hacer un ahorro mensual. Idealmente se incluiría una consulta que calcula este fecha con base en la última fecha del archivo de simulación.
Este es el código resultante de la primera consulta:
CREATE OR ALTER VIEW [dbo].[Vista_CuentasObjetivoQuePerdieronRetirosPorSaldoNegativo]
AS
SELECT CO.Numero_Cuenta,
CO.id as ID_de_la_Cuenta_Objetivo,
CO.Descripcion,
DATEDIFF(MONTH,Fecha_Proximo_Credito,Fecha_Final) as Cantidad_de_depósitos_realizados,
DATEDIFF(MONTH,Fecha_Inicio, Fecha_Final) as Cantidad_de_depósitos_si_se_hubieran_realizado_todos_los_depósitos,
CO.Monto_Ahorro * DATEDIFF(MONTH,Fecha_Proximo_Credito,Fecha_Final) as Monto_debitado_real,
CO.Monto_Ahorro * DATEDIFF(MONTH,Fecha_Inicio, Fecha_Final) as Monto_debitado_si_se_hubieran_realizado_todos_los_depósitos
FROM CuentaObjetivo CO
WHERE CO.Saldo = 0 and CO.Fecha_Proximo_Credito != Fecha_Final and Fecha_Inicio <= '2018-05-31'
GO
Para la segunda consulta, se decidió utilizar un stored procedure. Para realizar esta consulta se tuvo que hacer modificaciones al archivo XML de simulación (Simulación2.xml), puesto a que no sucedía ningún caso en el que una cuenta tuviera 5 multas por exceso de retiros en cajero humano. Por lo tanto en la prueba realizada solo se muestra sobre 1 cuenta la consulta para mostrar que funciona, ya que hacerlo para todas habría tomado mucho tiempo. También sucedió que con las pruebas se descubrió que puede haber más de un mes y más de un año con la mayor cantidad de retiros, por lo que se pasó de usar una variable entera para guardar este valor a usar tablas variables para guardar todos los años y meses donde la cantidad de retiros fue la mayor. Uno de los principales problemas enfrentados fue el de obtener el primer día del mes siguiente a cualquier fecha, para lograrlo se hizo uso de una función encontrada en internet: DATEADD(m,1,DATEADD(m,datediff(m,0,@Fecha_Buscar),0))
Al final lo que faltó de la consulta fue calcular el promedio de retiros por mes.
El código de la segunda consulta es el siguiente
USE [Sistema_Banco]
GO
/****** Object: StoredProcedure [dbo].[SP_CuentasMultaExcCH_Min5_UltimosNDias] Script Date: 11/14/2019 5:05:38 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:
<Austin Hakanson>
-- Create date: <11-06-2019>
-- Description:
<SP para ejecutar consulta de cuentas que tienen multas por exceso de retiros en cajero humano
-- y lo han hecho al menos 5 veces en los últimos N días>
-- =============================================
ALTER PROCEDURE [dbo].[SP_CuentasMultaExcCH_Min5_UltimosNDias]
-- Parametros
@Dias int
AS
BEGIN
SET NOCOUNT ON;
--
DESCRIPCION
-- Se consultan los id de las cuentas y el numero de apariciones de dicho id en la tabla movimento
-- para todas las cuentas que hayan tenido movimientos del tipo 'multSaldMin' en fechas posteriores
-- a la fecha de busqueda
-- Se declara una tabla variable para cargar las fechas de operacion
SET Language Spanish
DECLARE @Fechas TABLE
(
sec int primary key identity(1,1),
fecha date
)
DECLARE @UltimaFechaDeOperacion date
DECLARE @PrimeraFechaDeOperacion date
DECLARE @MesesDeOperacion int
DECLARE @DocHandle int, @XmlDocument xml
-- Se cargan los datos del XML a la variable XML
SELECT @XmlDocument = F
FROM OPENROWSET (Bulk '\\Mac\Home\Documents\Ing. Computación\IV Semestre\Bases de datos I\IIITP\Simulacion2.xml',Single_BLOB) AS Fechas(F)
-- Se crea una representacion interna del documento XML
EXEC sp_xml_preparedocument @DocHandle OUTPUT, @XmlDocument
-- Execute a SELECT statement using OPENXML rowset provider.
INSERT INTO @Fechas
SELECT fecha
FROM OPENXML (@DocHandle, '/xml/Simulacion/FechaOperacion',1) -- El uno al final indica que la lectura es <atribute-centric>
WITH (fecha date)
EXEC sp_xml_removedocument @DocHandle
SELECT @UltimaFechaDeOperacion = max(fecha) FROM @Fechas
SELECT @PrimeraFechaDeOperacion = min(fecha) FROM @Fechas
SELECT @UltimaFechaDeOperacion = DATEADD(DAY,1,@UltimaFechaDeOperacion)
-- Para que ajustar el total de meses de operacion si es el ultimo dia del mes
SELECT @MesesDeOperacion = DATEDIFF(MONTH,@PrimeraFechaDeOperacion,@UltimaFechaDeOperacion)
-- Se selecciona la ultima fecha de operacion del xml de simulacion
DECLARE @Fecha_Buscar date = DATEADD(DAY,-@Dias,@UltimaFechaDeOperacion)
-- Se insertan las cuentas con multa de exceso de retiros en cajero humano
DECLARE @Cuentas_Con_Multa_Exceso_CH_En_Fecha_Solicitada TABLE
(
idCuenta int,
Frecuencia int
)
INSERT INTO @Cuentas_Con_Multa_Exceso_CH_En_Fecha_Solicitada
SELECT [idCuenta]
,COUNT(idCuenta)
FROM [Sistema_Banco].[dbo].[Movimiento]
WHERE Tipo_Movimiento = 'multExcCH' and Fecha > @Fecha_Buscar
GROUP BY idCuenta
-- CANTIDAD PROMEDIO DE RETIROS
-- Se inserta en una tabla todos los movimientos de retiro
DECLARE @TodosLosMovimientosRetiro TABLE
( sec int identity(1,1),
idCuenta int,
Fecha date,
Tipo_Movimiento nvarchar(50)
)
INSERT INTO @TodosLosMovimientosRetiro
SELECT M.idCuenta,
M.Fecha,
M.Tipo_Movimiento
FROM Movimiento M
WHERE M.Tipo_Movimiento = 'retiroCH' or M.Tipo_Movimiento = 'retiroCA'
-- Variables para el procesamiento del promedio
DECLARE @Fecha_LimiteInferior date = @PrimeraFechaDeOperacion
DECLARE @Fecha_LimiteSuperior date = DATEADD(MONTH,1,@PrimeraFechaDeOperacion)
SET @Fecha_LimiteSuperior = DATEADD(DAY,-1,@Fecha_LimiteSuperior)
DECLARE @Promedio float
DECLARE @Total float
DECLARE @CantidadMesesSimulacion int
DECLARE @MesConMasRetiros int = 0
DECLARE @MayorCantidadRetiros int = 0
DECLARE @AnioConMasRetiros int
-- Ciclo para sacar los retiros mensuales de las cuentas con mas de 5 multas
DECLARE @Retiros_En_El_Mes int
DECLARE @Retiros_En_El_Anio int
DECLARE @MesesConMasRetiros TABLE
(
sec int identity(1,1),
mes int,
cantidad int,
nombre nvarchar(20)
)
DECLARE @AniosConMasRetiros TABLE
(
sec int identity(1,1),
anio int,
cantidad int
)
SELECT @Fecha_Buscar as Fecha_A_Buscar
-- Se definen los limites de fecha para hacer los calculos mensuales
SET @Fecha_LimiteInferior = @Fecha_Buscar
SET @Fecha_LimiteSuperior = DATEADD(m,1,DATEADD(m,datediff(m,0,@Fecha_Buscar),0)) -- Se selecciona el primer dia del siguiente mes
SET @Fecha_LimiteSuperior = DATEADD(DAY,-1,@Fecha_LimiteSuperior) -- Se acomoda para que sea el final del mes
SET @UltimaFechaDeOperacion = DATEADD(DAY,-1, @UltimaFechaDeOperacion)
WHILE @Fecha_LimiteSuperior <= @UltimaFechaDeOperacion
BEGIN
-- Se seleccionan el numero de retiros y el id de las cuentas que tienen movimientos de retiroCH o retiraCA Y que tienen al menos 5 multas por exceso de retiros
SELECT @Fecha_LimiteInferior as Fecha_LimiteInferior, @Fecha_LimiteSuperior as Fecha_LimiteSuperior, M.idCuenta, COUNT(M.idCuenta) Numero_Retiros_En_El_Mes
FROM Movimiento M
WHERE M.idCuenta IN (SELECT idCuenta FROM @Cuentas_Con_Multa_Exceso_CH_En_Fecha_Solicitada WHERE Frecuencia >= 5)
-- Se seleccionan solo las cuentas con mas de 5 multas por exceso de retiros
and M.Fecha BETWEEN @Fecha_LimiteInferior and @Fecha_LimiteSuperior -- Se seleccionan solo los
and (M.Tipo_Movimiento = 'retiroCH' or M.Tipo_Movimiento = 'retiroCA')
GROUP BY idCuenta
-- Se guarda el numero de retiros en el mes
SELECT @Retiros_En_El_Mes = COUNT(M.idCuenta)
FROM Movimiento M
WHERE M.idCuenta IN (SELECT idCuenta FROM @Cuentas_Con_Multa_Exceso_CH_En_Fecha_Solicitada WHERE Frecuencia >= 5)
-- Se seleccionan solo las cuentas con mas de 5 multas por exceso de retiros
and M.Fecha BETWEEN @Fecha_LimiteInferior and @Fecha_LimiteSuperior -- Se seleccionan solo los
and (M.Tipo_Movimiento = 'retiroCH' or M.Tipo_Movimiento = 'retiroCA')
GROUP BY idCuenta
SET @Retiros_En_El_Anio = @Retiros_En_El_Mes
IF (@Retiros_En_El_Mes >= @MayorCantidadRetiros)
BEGIN
SET @MayorCantidadRetiros = @Retiros_En_El_Mes
SET @MesConMasRetiros = DATEPART(MONTH,@Fecha_LimiteInferior)
SET @AnioConMasRetiros = DATEPART(YEAR,@Fecha_LimiteInferior)
IF (@MesConMasRetiros NOT IN (SELECT mes FROM @MesesConMasRetiros)) -- Si el mes no esta ya
BEGIN
-- Se eliminan los meses que tengan menor o igual cantidad
IF (@Retiros_En_El_Mes > (SELECT max(cantidad) FROM @MesesConMasRetiros))
BEGIN
DELETE @MesesConMasRetiros
END
-- Se inserta el nuevo mes
INSERT INTO @MesesConMasRetiros
VALUES
(
@MesConMasRetiros,
@Retiros_En_El_Mes,
DATENAME(MONTH,@Fecha_LimiteInferior)
)
END
ELSE
BEGIN
UPDATE @MesesConMasRetiros
SET cantidad = @MayorCantidadRetiros
WHERE mes = @MesConMasRetiros
END
IF (@AnioConMasRetiros NOT IN (SELECT anio FROM @AniosConMasRetiros))
BEGIN
-- Se eliminan los anios que tengan menor o igual cantidad
IF (@Retiros_En_El_Anio > (SELECT max(anio) FROM @AniosConMasRetiros))
BEGIN
DELETE @AniosConMasRetiros
END
-- Se inserta el nuevo anio
INSERT INTO @AniosConMasRetiros
VALUES
(
@AnioConMasRetiros,
@Retiros_En_El_Mes
)
END
ELSE
BEGIN
UPDATE @AniosConMasRetiros
SET cantidad = @MayorCantidadRetiros
WHERE anio = @AnioConMasRetiros
END
END
SET @Fecha_LimiteInferior = DATEADD(m,1,DATEADD(m,datediff(m,0,@Fecha_LimiteInferior),0)) -- Se selecciona el primer dia del siguiente mes
SET @Fecha_LimiteSuperior = DATEADD(MONTH,1,@Fecha_LimiteInferior) -- Se selecciona el primer dia del siguiente mes
SET @Fecha_LimiteSuperior = DATEADD(DAY,-1,@Fecha_LimiteSuperior)
----------------------------------------------------------------------------
END
SELECT @MesConMasRetiros as MesConMasRetiros, @AnioConMasRetiros as AnioConMasRetiros
SELECT * FROM @MesesConMasRetiros
SELECT * FROM @AniosConMasRetiros
END
Horas de trabajo: Primera Consulta (1:30 h) Segunda Consulta: (2h)
