Hi All,
Requirement
Im looking to create a script that can assign Interest and Capital earned on Actual payments. The interest and capital must be assigned using information from a ‘schedule’ table (@schedule)
The idea is Actual payments (@actual) can reference the (@schedule) and using some kind of bucketing system assign the Interest & Capital to each payment.
Table @output shows how the ‘calculations’ should look. Obviously I’ve had to hardcode these.
Other Info
Sometimes payments can be refunded (i.e. negative).
For DealID 3; you can see the Actual payments ‘straddle’ buckets – this is where it gets more complicated as the Interest capital come from 2 sets of buckets.
The Output
Ideally a TSQL script that can run against @Actual and @schedule and produce the values seen in @output (i.e the 2 columns ‘calcInterest’ and ‘calcCapital’)
Disclaimer
If this is a bit much to ask a forum to help ‘solve’ – please can you advise the best way to get bespoke t-sql like this written?
Any advice would be very welcomed.
--===========================================
--t1 - Schedule (tells us Interest and capital split)
--===========================================
DECLARE @schedule TABLE (
DealID INT,
BillDate DATE,
Amount money,
Interest money,
Capital money
)
INSERT INTO @schedule
SELECT 1,'2019-09-26', '6590.0', '4652.80', '1937.20' union
SELECT 1,'2019-10-26', '6590.0', '4458.93', '2131.07' union
SELECT 1,'2019-11-21', '6590.0', '4265.07', '2324.93' union
SELECT 1,'2019-11-26', '93606.80', '0', '93606.80' union
SELECT 2,'2019-02-26', '5000', '2000', '3000' union
SELECT 2,'2019-03-26', '5000', '1500', '3500' union
SELECT 2,'2019-04-26', '4000', '1000', '3000' union
SELECT 2,'2019-05-26', '1000', '200', '800' union
SELECT 3,'2019-01-22', '5000', '2000', '3000' union
SELECT 3,'2019-02-24', '5000', '1500', '3500'
--SELECT 1,'2018-12-22 00:00:00.000',
--===========================================
--t2 - Actual
--===========================================
DECLARE @actual TABLE (
DealID INT,
RecievedDate DATE,
Amount money
)
INSERT INTO @actual
SELECT 1,'2019-09-26', '6590.0' union
SELECT 1,'2019-10-26', '6590.0' union
SELECT 1,'2019-10-26', '6590.0' union
SELECT 1,'2019-11-21', '1590.0' union
SELECT 1,'2019-11-22', '5000.0' union
SELECT 1,'2019-11-26', '93606.80' union
SELECT 2,'2019-02-26', '5000' union
SELECT 2,'2019-03-26', '3000' union
SELECT 2,'2019-03-26', '2000' union
SELECT 2,'2019-04-26', '4000' union
SELECT 2,'2019-05-26', '1000' union
SELECT 3,'2019-01-22', '3000' union
SELECT 3,'2019-01-22', '3000' union
SELECT 3,'2019-01-22', '3000' union
SELECT 3,'2019-01-22', '1000'
--=================================================================
--t3 - Calculate the Interest Capital Split based on buckets above
--================================================================
DECLARE @output TABLE (
DealID INT,
RecievedDate DATE,
Amount money,
calcInterest money,
calcCapital money
)
INSERT INTO @output
SELECT 1,'2019-09-26', '6590.0', '4652.80', '1937.20' union
SELECT 1,'2019-10-26', '6590.0', '4458.93', '2131.07' union
SELECT 1,'2019-11-21', '6590.0', '4265.07', '2324.93' union
SELECT 1,'2019-11-21', '1590.0', '1029.05', '560.95' union
SELECT 1,'2019-11-22', '5000.0', '3236.02', '1763.98' union
SELECT 1,'2019-11-26', '93606.80', '0', '93606.80' union
SELECT 2,'2019-02-26', '5000', '2000', '3000' union
SELECT 2,'2019-03-26', '3000', '900', '2100' union
SELECT 2,'2019-03-26', '2000', '600', '1400' union
SELECT 2,'2019-04-26', '4000', '1000', '3000' union
SELECT 2,'2019-05-26', '1000', '200', '800' union
--most complex as buckets span Interest/Capital pots
SELECT 3,'2019-01-22', '3000', '1200', '1800' union
SELECT 3,'2019-01-22', '3000','1100', '1900' union
SELECT 3,'2019-01-22', '3000', '900', '2100' union
SELECT 3,'2019-01-22', '1000' , '300', '700'
select * from @schedule
select * from @actual
select * from @output