sql server - Getting Error from the following script -
i going create si each record of table, getting error stored procedure:
no column name specified column 6 of 'cte_alldates'.
please check script , let me know problem is:
and stored procedure:
create proc sp_calcualttesi begin declare @today datetime set @today = dateadd(day,datediff(day,0,current_timestamp),0) ; cte_dates ( select distinct name, pamount, rateofint, cdate, case when isnull(today, @today) < dateadd(month, 1, dateadd(month, datediff(month, 0, cdate), 0)) isnull(@today, @today) else dateadd(month, 1, dateadd(month, datediff(month, 0, cdate), 0)) end monthend, isnull(@today, @today) end_date tbl_intestcalculate), cte_alldates ( select name, pamount, rateofint, cdate, monthend, @today cte_dates union select name, pamount, rateofint, dateadd(month, number, monthend), case when dateadd(month, number + 1, monthend) < @today dateadd(month, number + 1, monthend) else @today end, @today cte_dates c cross join (select number master..spt_values type = 'p' , number between 0 , 11) dateadd(month, number, monthend) < @today ) select name, cdate, monthend, pamount, rateofint, datediff(day, cdate, monthend) no_of_days, round(pamount * rateofint * datediff(day, cdate, monthend) / 36500, 2) si cte_alldates end
a cte needs have column names specified columns. in case missing column name sixth column. cte_alldates cte should read:
cte_alldates ( select name, pamount, rateofint, cdate, monthend, [day]=@today cte_dates union select name, pamount, rateofint, dateadd(month, number, monthend), case when dateadd(month, number + 1, monthend) < @today dateadd(month, number + 1, monthend) else @today end, [day]=@today cte_dates c cross join (select number master..spt_values type = 'p' , number between 0 , 11) dateadd(month, number, monthend) < @today ) 
Comments
Post a Comment