SQLTeam.com | Weblogs | Forums

Tsql - Assign Interest and Capital based on a Schedule

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:sweat_smile:

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

Hi

i suggest first
you write
notes or instructions or checklist or to do list
like a recipe for preparing chicken sandwich !!!

on what basis you want to calculate the
calcinterest calcoutput columns !!!

once you have the notes
translate it into t-sql !!!!

some rules , criteria will be there right

Looking at the data i see some things
but dont get rest of the things

Having it in written form !!!! then writing the sql would make it really easy
First we need to know what (how) needs to be done !!

Thanks Harishgg,
I think i know what your getting at - need to break the problem down more. Will have a rethink.
Cheers