Cálculo de la tarifa acumulativa por niveles en SQL Server para un desencadenador DML UPDATE/INSERT.

25 Jul, 2023 Programación 0

Básicamente, utilizando SQL Server, quiero tomar el “Monto Bruto” de la tabla actual a continuación (que se deriva de una columna calculada al hacer una inserción o actualización) y luego hacer que ese “Monto Bruto” pase por la “Tabla Escalonada” para derivar el “Total A $” en la tabla de salida deseada.

Supuse que esto probablemente tendría que hacerse con un desencadenador (¿quizás una función?) ya que este cálculo ocurriría al realizar una inserción o actualización, y también porque la lógica condicional podría incorporarse en él, ya que hay diferentes tablas escalonadas con valores límite mínimos / máximos y umbrales de porcentaje para diferentes niveles.

El ejemplo a continuación es, por supuesto, acumulativo, y funciona como las tasas de impuesto por ingresos marginales, los primeros 10000 están al 90% (para el Total A), el segundo nivel calcula los 19999 al 60%, el tercer nivel los 69999 al 40%, y así sucesivamente. Hay otras regiones con diferentes escalones que son solo valores de referencia simples.

Tabla escalonada:

RegionID	TierNo	Min	Max	Total A	Total B
3	1	0	10000	0.90	0.10
3	2	10001	30000	0.60	0.40
3	3	30001	100000	0.40	0.60
3	4	100001	500000	0.40	0.60
3	5	500001	999999999999	0.20	0.80

Ejemplo de tabla actual:

TransID	RegionID	GrossAmt	Total A %	Total A $	Net Amt
100001	3	125000

Salida deseada:

TransID	RegionID	GrossAmt	Total A %	Total A $	Net Amt
100001	3	125000	0.47	59000	66000

Cualquier idea o guía sería extremadamente útil y apreciada.

davy.ai

July 25, 2023 at 6:27 am

Para lograr la salida deseada, se puede utilizar un desencadenador para realizar el cálculo y actualizar los campos relevantes en la tabla de salida. Aquí hay un ejemplo de código de desencadenador que se puede usar:

CREATE TRIGGER CalculateTotalA
ON CurrentTable
AFTER INSERT, UPDATE
AS
BEGIN
  -- Obtener el valor de GrossAmt de la fila insertada/actualizada
  DECLARE @GrossAmt INT;
  SELECT @GrossAmt = GrossAmt FROM inserted;

  -- Encontrar el nivel relevante de TieredTable basado en RegionID
  DECLARE @TierNo INT;
  SELECT @TierNo = TierNo FROM TieredTable WHERE RegionID = (SELECT RegionID FROM inserted);

  -- Calcular el porcentaje y monto Total A utilizando los valores de nivel
  DECLARE @TotalAPercent DECIMAL(8,2);
  DECLARE @TotalAAmount DECIMAL(18,2);
  IF @GrossAmt > (SELECT Max FROM TieredTable WHERE TierNo = @TierNo)
     SET @GrossAmt = (SELECT Max FROM TieredTable WHERE TierNo = @TierNo);

  SELECT @TotalAPercent = TotalA FROM TieredTable WHERE TierNo = @TierNo;
  SELECT @TotalAAmount = (@GrossAmt * @TotalAPercent) FROM inserted;

  -- Actualizar los campos de Total A % y Total A $ en la tabla de salida
  UPDATE CurrentTable SET [Total A %] = @TotalAPercent, [Total A $] = @TotalAAmount
  FROM inserted WHERE CurrentTable.TransID = inserted.TransID;
END

En este código, el desencadenador se crea en la CurrentTable y se ejecuta después de cada operación INSERT o UPDATE. Primero recupera el valor de GrossAmt de la fila insertada/actualizada y luego encuentra el nivel relevante de TieredTable basado en RegionID. Luego aplica la lógica de porcentaje escalonado para calcular el porcentaje y monto total A, asegurándose de limitar el valor de GrossAmt al máximo del nivel. Finalmente, actualiza los campos de Total A % y Total A $ en la tabla de salida para la fila relevante.

Ten en cuenta que el código asume que hay una columna de clave primaria llamada TransID en la CurrentTable para unir las filas insertadas/actualizadas con la tabla de salida. Además, el campo Net Amt en la tabla de salida no se calcula en este código, por lo que es posible que necesites agregar otro desencadenador o una columna calculada para calcularlo en función de los valores de GrossAmt y Total A $.

¡Espero que esto ayude! Avísame si tienes alguna pregunta o necesitas más orientación.

Answer

davy.ai

July 25, 2023 at 6:27 am
Para lograr la salida deseada, se puede utilizar un desencadenador para realizar el cálculo y actualizar los campos relevantes en la tabla de salida. Aquí hay un ejemplo de código de desencadenador que se puede usar:
```
CREATE TRIGGER CalculateTotalA
ON CurrentTable
AFTER INSERT, UPDATE
AS
BEGIN
  -- Obtener el valor de GrossAmt de la fila insertada/actualizada
  DECLARE @GrossAmt INT;
  SELECT @GrossAmt = GrossAmt FROM inserted;

  -- Encontrar el nivel relevante de TieredTable basado en RegionID
  DECLARE @TierNo INT;
  SELECT @TierNo = TierNo FROM TieredTable WHERE RegionID = (SELECT RegionID FROM inserted);

  -- Calcular el porcentaje y monto Total A utilizando los valores de nivel
  DECLARE @TotalAPercent DECIMAL(8,2);
  DECLARE @TotalAAmount DECIMAL(18,2);
  IF @GrossAmt > (SELECT Max FROM TieredTable WHERE TierNo = @TierNo)
     SET @GrossAmt = (SELECT Max FROM TieredTable WHERE TierNo = @TierNo);

  SELECT @TotalAPercent = TotalA FROM TieredTable WHERE TierNo = @TierNo;
  SELECT @TotalAAmount = (@GrossAmt * @TotalAPercent) FROM inserted;

  -- Actualizar los campos de Total A % y Total A $ en la tabla de salida
  UPDATE CurrentTable SET [Total A %] = @TotalAPercent, [Total A $] = @TotalAAmount
  FROM inserted WHERE CurrentTable.TransID = inserted.TransID;
END
```
En este código, el desencadenador se crea en la CurrentTable y se ejecuta después de cada operación INSERT o UPDATE. Primero recupera el valor de GrossAmt de la fila insertada/actualizada y luego encuentra el nivel relevante de TieredTable basado en RegionID. Luego aplica la lógica de porcentaje escalonado para calcular el porcentaje y monto total A, asegurándose de limitar el valor de GrossAmt al máximo del nivel. Finalmente, actualiza los campos de Total A % y Total A $ en la tabla de salida para la fila relevante.

Ten en cuenta que el código asume que hay una columna de clave primaria llamada TransID en la CurrentTable para unir las filas insertadas/actualizadas con la tabla de salida. Además, el campo Net Amt en la tabla de salida no se calcula en este código, por lo que es posible que necesites agregar otro desencadenador o una columna calculada para calcularlo en función de los valores de GrossAmt y Total A $.

¡Espero que esto ayude! Avísame si tienes alguna pregunta o necesitas más orientación.

Comments are closed.

es.davy.ai

¿Tienes una pregunta?

Cálculo de la tarifa acumulativa por niveles en SQL Server para un desencadenador DML UPDATE/INSERT.

Answer

davy.ai