9
Answers

Getting Error from the following script

Hey guys,
 
 
i am going to create SI for each record of table ,getting error from STORED PROCEDURE.
  No column name was specified for column 6 of 'cte_Alldates'.
please check the bellow script and let me know,where is the problem
 
Table script :
    
 and the STORE PROCEDURE :
 
create proc sp_calcualtteSI
as
begin
DECLARE @today datetime
SET @today = dateadd(day,datediff(day,0,current_timestamp),0)
; with cte_dates
as
(
select distinct name,Pamount,Rateofint
,cdate
,case when isnull(@today,@today) < dateadd(month,1,dateadd(month,datediff(month,0,cdate),0))
then isnull(@today,@today)
else dateadd(month,1,dateadd(month,datediff(month,0,cdate),0))
end as MonthEnd
,isnull(@today,@today) as End_date
FROM tbl_intestcalculate
) ,
cte_Alldates
as
(
select name,Pamount,Rateofint
, cdate
,monthEnd
,@today
from cte_dates
union
select name,Pamount,Rateofint
, dateadd(month,number,monthEnd)
,case when dateadd(month,number+1,monthEnd) < @today
then dateadd(month,number+1,monthEnd)
else @today
end
,@today
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) < @today
)
select name
,cdate
,monthEnd
,Pamount
,Rateofint
,datediff(day,cdate,monthEnd) as No_Of_Days
,round(Pamount*Rateofint*datediff(day,cdate,monthEnd)/36500,2) as SI
from cte_Alldates
end
 
 

Answers (9)