SIMPLE INTEREST - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SIMPLE INTEREST

Author

Topic

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-03-28 : 12:22:13

I have a table below to compute simple interest on


NAME	TYPE	TRAN_NUM STATUS	START_DATE END_DATE  AMOUNT	RATE
AA	CALLD	240883	  STRT	1/7/2011	      332535.78	 0.3
BB	CALLD	249334	  MTRD	3/8/2011   3/16/2011  0	         12.2
CC	CALLD	248263	  COMP	3/8/2011   3/16/2011  1500000	 2.2
DD	CALLD	248263	  COMP	1/23/2011  2/12/2011  50000	 0.54

Note:
AA it has no END_DATE.
DD it cut across multiple 2 months.

I want to generate simple interest for the above table by month.
Using START_DATE and current date, AA will have three entries for and have simple interest for each month.
so also DD will have two entries START_DATE and END_DATE. etc

Below is my expected result.



NAME	TYPE	TRAN_NUM  STATUS  START_DATE	END_DATE     AMOUNT	RATE  NO_OF_DAYS   SI 
AA	CALLD   240883    STRT     1/7/2011	1/31/2011    332535.78	0.3	25	 68.33 
AA	CALLD   240883    STRT     2/1/2011	2/28/2011    332535.78	0.3	28	 76.53 
AA	CALLD	240883    STRT     3/1/2011	3/28/2011    332535.78	0.3	28	 76.53 
BB	CALLD   249334    MTRD     3/8/2011	3/16/2011    0          12.2	9	 -   
CC	CALLD   248263    COMP     3/8/2011	3/16/2011    1500000	2.2	9	 813.70 
DD	CALLD   248263    COMP     1/23/2011	1/31/2011    50000	0.54	9	 6.66 
DD	CALLD   248263    COMP     2/1/2011	2/12/2011    50000	0.54	12	 8.88

NO_OF_DAYS = DATEDIFF(D,END_DATE,START_DATE)
SI = AMOUNT * RATE * NO_OF_DAYS /36500

Many thanks.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-28 : 15:34:05

I'm not balancing to your results, and if I were clever this could have been done with one recursive cte
but here its goes


DECLARE @Table Table(name char(2),type char(5),tran_num int,status char(4),start_date datetime,end_date datetime
                     ,amount numeric(10,2),rate numeric(4,2)
                    )

INSERT INTO @Table
SELECT 'AA','CALLD',240883,'STRT','1/7/2011',null         ,332535.78,0.3 UNION ALL
SELECT 'BB','CALLD',249334,'MTRD','3/8/2011','3/16/2011'  ,0        ,12.2 UNION ALL
SELECT 'CC','CALLD',248263,'COMP','3/8/2011','3/16/2011'  ,1500000  ,2.2 UNION ALL
SELECT 'DD','CALLD',248263,'COMP','1/23/2011','2/12/2011' ,50000    ,0.54

DECLARE @today datetime
SET @today = dateadd(day,datediff(day,0,current_timestamp),0)
; with cte_dates
as
(
 select distinct t.name,t.type,tran_num,t.status,amount,rate
 ,start_date 
 ,case when isnull(end_date,@today) < dateadd(month,1,dateadd(month,datediff(month,0,start_date),0))
       then isnull(end_date,@today)
       else dateadd(month,1,dateadd(month,datediff(month,0,start_date),0))
  end as MonthEnd
 ,isnull(end_date,@today) as End_date
FROM @table t
)
,cte_Alldates
as
(
select    name,type,tran_num,status,amount,rate
         , start_Date
         ,monthEnd
         ,end_date
from cte_dates
 union 
select   name,type,tran_num,status,amount,rate
         , dateadd(month,number,monthEnd)
       
         ,case when dateadd(month,number+1,monthEnd) < end_date
               then dateadd(month,number+1,monthEnd) 
               else end_date
          end
         ,end_date
from cte_dates c
cross join (select number from master..spt_values where type = 'p' and number between 0 and 11) a 
 where  dateadd(month,number,monthEnd) <   end_date
)


 
select  name,type,tran_num,status
 ,start_date
 ,monthEnd 
 ,amount
 ,rate
,datediff(day,start_date,monthEnd) as No_Of_Days
 ,round(AMOUNT*RATE*datediff(day,start_date,monthEnd)/36500,2) as SI
 from cte_Alldates

Jim

Everyday I learn something that somebody else already knew

abacusdotcom
Posting Yak Master

133 Posts

Posted - 2011-03-28 : 16:11:17

Jim my friend, you are really a God sent. Thanks very much

I sign for fame not for shame but all the same, I sign my name.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-03-28 : 18:03:14

You're welcome! Here's the recursive cte
DECLARE @Table Table(name char(2),type char(5),tran_num int,status char(4),start_date datetime,end_date datetime
,amount numeric(10,2),rate numeric(4,2)
)

INSERT INTO @Table
SELECT 'AA','CALLD',240883,'STRT','1/7/2011',null ,332535.78,0.3 UNION ALL
SELECT 'BB','CALLD',249334,'MTRD','3/8/2011','3/16/2011' ,0 ,12.2 UNION ALL
SELECT 'CC','CALLD',248263,'COMP','3/8/2011','3/16/2011' ,1500000 ,2.2 UNION ALL
SELECT 'DD','CALLD',248263,'COMP','1/23/2011','2/12/2011' ,50000 ,0.54

DECLARE @today datetime
SET @today = dateadd(day,datediff(day,0,current_timestamp),0)

;with cte1 as
(
select distinct t.name,t.type,tran_num,t.status,amount,rate
,start_date
,case when isnull(end_date,@today) < dateadd(month,1,dateadd(month,datediff(month,0,start_date),0))
then isnull(end_date,@today)
else dateadd(month,1,dateadd(month,datediff(month,0,start_date),0))
end as MonthEnd
,isnull(end_date,@today) as End_date
, 0 as NextMonth
FROM @table t
UNION ALL
select t.name,t.type,tran_num,t.status,amount,rate
,dateadd(month,nextmonth,t.MonthEnd) as start_date
,case when dateadd(month,nextmonth + 1,t.monthEnd) < end_date
then dateadd(month,nextmonth + 1,t.monthEnd)
else end_date
end as monthEnd
,end_date
,nextMonth + 1
FROM cte1 t
where t.start_date < end_date

)
select name,type,tran_num,status,start_date,monthEnd,amount,rate
,datediff(day,start_date,monthEnd) as No_Of_Days
,round(AMOUNT*RATE*datediff(day,start_date,monthEnd)/36500,2) as SI from cte1
where start_date < end_date

order by name,start_date

Jim

Everyday I learn something that somebody else already knew

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources