Hi all,
I'm looking for help solving a SQL problem in Oracle that involves calculating a derived column (base_am_calc and over_200k_am_calc) based on a running total. I've been studying up on SQL Model By clause but have not been able to figure out how to implement it. Please use plain SQL and not PL/SQL.
The goal is to calculate base_am_calc and over_200k_am_calc for each row using the following logic. My main obstacle is how to maintain a running sum of base_am_calc while accounting for the conditional logic of 'P' and 'R'.
Business rules is as follows:
If ded_acceptance_id = ‘A’
then base_am_calc = (cumul_subj_gross * 0.0145) - SUM of previous base_am_calc values from the first row to 1 row before the current row.
If ded_acceptance_id = 'P':
base_am_calc = ded_am (copy the value directly)
If ded_acceptance_id = 'R':
base_am_calc = 0 (no deduction)
The challenge is that I need to reference the running total of base_am_calc and over_200k_am_calc as they're being calculated. This gets complicated because of the 'P' and 'R' cases.
There's a second piece of the calculation where if the the cumul_subj_gross is greater than 200,000, then the amount of 200,000 is multiplied by 0.009, let call this over_200k_am_calc.
The base_am_calc + over_200k_am should match the ded_am column. The ded_am column is what I'm trying to reconcile against.
Here’s an example:
For int_ctrl_no = 701, base_am_calc should be:
(864553.87 * 0.0145) - sum of the base_am_calc from row 1 to 1 row preceding the current row or SUM(141.3054, 141.3054, 189.276765, 115.11144, 23.985755, 11920.85049), which yields 4.195865.
base_am_calc = (864553.87 * 0.0145) - SUM(141.3054, 141.3054, 189.276765, 115.11144, 23.985755, 11920.85049) = 4.195865.
over_200k_am_calc = ((864553.87 - 200000) * 0.009 - SUM(0, 0, 0, 0, 0, 5978.3805) = 2.60433
base_am_calc + over_200k_am_calc: 4.195865 + 2.60433 = 6.79933, which matched the ded_am 6.8
For int_ctrl_no = 1001, the ded_acceptance_id = 'R' , no earnings to take the deduction, so base_am_calc = 0; over_200k_am_calc = 0
For int_ctrl_no = 1201 (with ded_acceptance_id = 'P'), we just assign base_am_calc = 50 directly; over_200k_am_calc = 0. This is a case where the amount is taken partially because there's not enough earnings to take in full. If it keeps going, it will self-adjust when there's enough money to full later on like in int_ctrl_no 1301.
For int_ctrl_no 1301, there's enough to take the full amount. It's self-adjusted as follows:
base_am_calc: (13986.84 * 0.0145) - SUM(141.31, 141.30, 189.28, 115.11, 23.99, 11920.85, 4.20, -11920.85, 11921.66, 0, 435.00, 50) = 965
over_200k_am_calc: ((964609.57 - 200000) * 0.009) - SUM(0.00, 0.00, 0.00, 0.00, 0.00, 5978.38, 2.60, -5,978.38, 5978.8818, 0, 270, 0) = 630
base_am_calc + over_200k_am_calc: 965 + 630 = 1595, which matches 1595 in ded_am
Here's the sample data:
WITH sample_data AS (
SELECT '2281' AS empl_id, 101 AS ctrl_no,
2024 AS cldr_yr, 'HITE' AS catg_cd, TO_DATE('1/12/2024', 'MM/DD/YYYY') AS effective_dt,
9745.2 AS subj_gross_am, 141.31 AS ded_am, 'A' AS ded_acceptance_id, 9745.2 AS cumul_subj_gross FROM dual
UNION ALL
SELECT '2281', 201, 2024, 'HITE', TO_DATE('1/30/2024','MM/DD/YYYY'), 9745.2, 141.3, 'A', 19490.4 FROM dual
UNION ALL
SELECT '2281', 301, 2024, 'HITE', TO_DATE('2/15/2024','MM/DD/YYYY'), 13053.57, 189.28, 'A', 32543.97 FROM dual
UNION ALL
SELECT '2281', 401, 2024, 'HITE', TO_DATE('2/29/2024','MM/DD/YYYY'), 7938.72, 115.11, 'A', 40482.69 FROM dual
UNION ALL
SELECT '2281', 501, 2024, 'HITE', TO_DATE('3/15/2024','MM/DD/YYYY'), 1654.19, 23.98, 'A', 42136.88 FROM dual
UNION ALL
SELECT '2281', 601, 2024, 'HITE', TO_DATE('4/3/2024','MM/DD/YYYY'), 822127.62, 17899.24, 'A', 864264.5 FROM dual
UNION ALL
SELECT '2281', 701, 2024, 'HITE', TO_DATE('4/9/2024','MM/DD/YYYY'), 289.37, 6.8, 'A', 864553.87 FROM dual
UNION ALL
SELECT '2281', 801, 2024, 'HITE', TO_DATE('4/3/2024','MM/DD/YYYY'), -822127.62, -17899.24, 'A', 42426.25 FROM dual
UNION ALL
SELECT '2281', 901, 2024, 'HITE', TO_DATE('4/10/2024','MM/DD/YYYY'), 822183.32, 17900.54, 'A', 864609.57 FROM dual
UNION ALL
SELECT '2281', 1001, 2024, 'HITE', TO_DATE('5/10/2024','MM/DD/YYYY'), 10000, 0, 'R', 874609.57 FROM dual
UNION ALL
SELECT '2281', 1101, 2024, 'HITE', TO_DATE('5/30/2024','MM/DD/YYYY'), 20000, 705, 'A', 894609.5 FROM dual
UNION ALL
SELECT '2281', 1201, 2024, 'HITE', TO_DATE('6/10/2024','MM/DD/YYYY'), 30000, 50, 'P', 924609.57 FROM dual
UNION ALL
SELECT '2281', 1301, 2024, 'HITE', TO_DATE('6/30/2024','MM/DD/YYYY'), 40000, 1595, 'A', 964609.57 FROM dual
UNION ALL
SELECT '2281', 201, 2024, 'HITR', TO_DATE('12/31/2023','MM/DD/YYYY'), 9745.2, 141.31, 'A', 9745.2 FROM dual
UNION ALL
SELECT '2281', 2281, 2024, 'HITR', TO_DATE('1/15/2024','MM/DD/YYYY'), 9745.2, 141.3, 'A', 19490.4 FROM dual
UNION ALL
SELECT '2281', 2281, 2024, 'HITR', TO_DATE('1/31/2024','MM/DD/YYYY'), 13053.57, 189.28, 'A', 32543.97 FROM dual
UNION ALL
SELECT '2281', 2281, 2024, 'HITR', TO_DATE('2/15/2024','MM/DD/YYYY'), 7938.72, 115.11, 'A', 40482.69 FROM dual
UNION ALL
SELECT '2281', 2281, 2024, 'HITR', TO_DATE('2/29/2024','MM/DD/YYYY'), 1654.19, 23.98, 'A', 42136.88 FROM dual
UNION ALL
SELECT '2281', 2281, 2024, 'HITR', TO_DATE('4/3/2024','MM/DD/YYYY'), 822127.62, 11920.86, 'A', 864264.5 FROM dual
UNION ALL
SELECT '2281', 2281, 2024, 'HITR', TO_DATE('4/9/2024','MM/DD/YYYY'), 289.37, 4.19, 'A', 864553.87 FROM dual
UNION ALL
SELECT '2281', 2281, 2024, 'HITR', TO_DATE('4/3/2024','MM/DD/YYYY'), -822127.62, -11920.86, 'A', 42426.25 FROM dual
UNION ALL
SELECT '2281', 2281, 2024, 'HITR', TO_DATE('4/10/2024','MM/DD/YYYY'), 822183.32, 11921.67, 'A', 864609.57 FROM dual
)
Thanks in advance!
NT