SQL Server: desglosar las cantidades en precios fijos
Digamos que tenemos una tabla que contiene diferentes pagos (session_payments) para una sesión que tiene diferentes actividades (activity_prices). Ten en cuenta que puede haber mucho más que 2 pagos para cada sesión.
session_id | payment_method | amount |
---|---|---|
1000 | 3 | 50 |
1000 | 6 | 200 |
session_id | activity | price |
---|---|---|
1000 | 1 | 100 |
1000 | 2 | 150 |
Necesito crear registros de pago para cada actividad.
En este caso:
session_id | activity | payment_method | amount |
---|---|---|---|
1000 | 1 | 3 | 50 |
1000 | 1 | 6 | 50 |
1000 | 2 | 6 | 150 |
Intenté con un enfoque de cursor, pero solo funciona bien cuando el pago es mayor que el precio de la actividad.
Creo que necesita algún tipo de acción recursiva que el cursor no puede proporcionar.
Aquí está el entorno SQL simplificado:
CREATE TABLE session_payments
(session_id VARCHAR(128) NULL,
payment_method INT NULL,
amount NUMERIC(10,2) NULL)
GO
CREATE TABLE activity_prices
(session_id VARCHAR(128) NULL,
activity NUMERIC(10,0) NULL,
price NUMERIC(10,2) NULL)
GO
INSERT session_payments
SELECT ‘1000’,3,50
INSERT session_payments
SELECT ‘1000’,6,200
INSERT activity_prices
SELECT ‘1000’,1,100
INSERT activity_prices
SELECT ‘1000’,2,150
GO
davy.ai
Para crear registros de pago para cada actividad, puede utilizar un JOIN CRUZADO para combinar las dos tablas y luego filtrar los registros donde el monto del pago sea menor que el precio de la actividad. Aquí hay un ejemplo de consulta:
Esta consulta inserta nuevos registros de pago en una tabla llamada
pagos
, con los valoressession_id
,actividad
,metodo_pago
ymonto
. La declaraciónCASE
asegura que cada pago cumpla con el precio de la actividad, ya sea utilizando el precio completo de la actividad (si el monto del pago es mayor o igual al precio) o el monto de pago restante (si el pago es menor que el precio).La cláusula
WHERE
filtra los pagos de valor cero y los pagos que no pueden cubrir ningún precio de actividad (es decir, aquellos para los cuales no hay ninguna actividad con un precio por debajo del monto del pago). Esto asegura que solo se inserten registros de pago válidos.No se requieren cursores ni recursividad para este enfoque.