December 8, 2014 at 2:15 pm
If I have millions of records with the format .
Customer | transaction start date | transaction end date | balance
Customer x | 02022014 | 07022014 | 65555
The interest rates for company g is
Rate | rate start date | rate end date
5%. 02022014 04022014
4%. 05022014. 07022015
This is yearly rate so I need to work out how many days divide by 365 in each period and then take the rate to work out interest charge.
So is there a set based way of working out interest for the millions of records ?
December 8, 2014 at 4:14 pm
There may be a stealthier way to do this without the need for a tally table, but one doesn't come to mind at present. Here's how I'd attack it:
Create a table of all realistically possible date (i.e. a tally table) ahead of time. I then loaded up two temp tables which presume to be of the structure you described. By joining the start and end dates of both the customer table and the interest rate table, you basically apply the correct rate on every given date to the table.
I haven't performed any aggregations on this data set because I'm not sure I understand exactly what you're trying to do, but you can group this result by datepart(year, DateOfInterest) to get a calendar year, then use aggregates to get at what you need.
-- Setup tabes and fake data)
if object_id('tempdb.dbo.#cal') is not null drop table #cal
create table #cal
(
DateInt int unique nonclustered,
DateDt date primary key clustered
)
if object_id('tempdb.dbo.#stmt') is not null drop table #stmt
create table #stmt
(
CustomerID int,
StartDate date,
EndDate date,
Balance decimal(38,5)
)
if object_id('tempdb.dbo.#ir') is not null drop table #ir
create table #ir
(
InterestRate decimal(9,5),
StartDate date,
EndDate date
)
;with n as (select top 1000000 num = row_number() over (order by (select null)) from sys.objects a, sys.objects b, sys.objects c, sys.objects d)
insert into #cal
select
DateInt = num,
DateDt = cast(num as datetime)
from n
insert into #stmt
select 1, '2014-02-02', '2014-07-02', 65555
insert into #ir
select 5, '2014-02-02', '2014-05-04' union all
select 4,' 2014-05-04', '2015-07-02'
--The real work
select
CustomerID = a.CustomerID,
DateOfInterest = ac.DateDt,
Balance = a.Balance,
InterestRate = ir.InterestRate
from #stmt a
inner join #cal ac
on ac.DateDt between a.StartDate and a.EndDate
inner join #ir ir
on ac.DateDt between ir.StartDate and ir.EndDate
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply