Help with faster solution for this code

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 2000 Forums

SQL Server Development (2000)

Help with faster solution for this code

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-07-10 : 23:53:45

Hi,
He is a piece of code to calculate interests of deposits.
I have about 1000000 deposits which depends on what the TDDURATN and TFDPINTFRQ are, I have to calculate the next time it hast to get interest and calculate the interest , and insert it into another table.
for example:
for
deposit "A"
TDDURATN =60
TFDPINTFRQ =1
rate =15.5
I have to calculate interests for sixty months and insert them into other table ,which is very slow for 1000000 records.
Any idea to solve it.
Thanks.


declare @TDDURATN int
declare @TFDPINTFRQ int
declare @TdINTDAY int
DECLARE @PTDOPNDAT            varchar(10)
DECLARE @pTDXPRDAT           varchar(10)

declare @TBTEMPRATE           float
declare @tdxtrint           float 
DECLARE @TDOPNAMT          decimal(20,0)
DECLARE @date                 nvarchar(10)
DECLARE @pdate                 nvarchar(10)

DECLARE @interest             decimal(20,0)
DECLARE @sql                  varchar(8000)
DECLARE @i                  int 
DECLARE @row                 int 
DECLARE @totalrow                 bigint 

set @totalrow =  (select max (cast(ID as bigint)) from  salavizadeh.myrisk_info  )
set @row=1
while @row<=200000
begin --1
	set @TDDURATN=(select cast(TDDURATN as int) from  salavizadeh.myrisk_info    where  cast (id as bigint ) = @row)
	set @TFDPINTFRQ=(select cast(TFDPINTFRQ as int) from  salavizadeh.myrisk_info    where  cast (id as bigint ) = @row)
	if( @TDDURATN<> @TFDPINTFRQ ) --1m
		begin
			set @pdate = (select PTDOPNDAT   from  salavizadeh.myrisk_info    where  cast (id as bigint ) = @row )
			set @pTDXPRDAT = (select pTDXPRDAT   from  salavizadeh.myrisk_info    where  cast (id as bigint ) = @row )
			set @date = (select TDOPNDAT   from  salavizadeh.myrisk_info    where  cast (id as bigint ) = @row )
			set @TdINTDAY = (select cast(TdINTDAY as int)   from  salavizadeh.myrisk_info    where  cast (id as bigint ) = @row )
			set @TBTEMPRATE=(select cast(TBTEMPRATE as float) from  salavizadeh.myrisk_info    where  cast (id as bigint ) = @row)
			set @tdxtrint=(select case when tdxtrint <>'' then  cast(tdxtrint as float)  
									   else  0 end
					       from  salavizadeh.myrisk_info    where  cast (id as bigint ) = @row)
			set @TDOPNAMT=(select cast(TDOPNAMT as decimal(20,0)) from  salavizadeh.myrisk_info    where  cast (id as bigint ) = @row)
			set @i=1
			while @i<=@TDDURATN 
				 begin				 	 
				 	 
					if @i=1  --first  date
				 	 begin
				 	 		 	  
				 	   set @interest= (select case when @TFDPINTFRQ=1 then 
				 			@TDOPNAMT*(@TBTEMPRATE)*
				 			(dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( @TdINTDAY,@pdate,'mm')))/36500
				 							else case when 	 @TFDPINTFRQ=12 then 
				 							@TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( @TdINTDAY,@pdate,'yy')))/36500
				 							    end
											end )
						if  @TFDPINTFRQ=1						
						begin  set @pdate= dbo.[PersianDateAdd]( @TdINTDAY,@pdate,'mm')
						  print (@pdate)--(cast (@i as nvarchar(10)))
						  --(cast ( @pdate as nvarchar(10)) + ' ' +@PTDOPNDAT)
						  end
						else 
						  begin set @pdate= dbo.PersianDateAdd( @TdINTDAY,@pdate,'yy')
						  --print(cast ( @pdate as nvarchar(10)) + ' ' +@PTDOPNDAT)
						  end
					  	set @sql='insert into RiskCashFlow900331 ( tedad, deposits,opndat,exprdat,opnamnt,rate,frq,durant,intday,calcdate,interest) select ' + cast (@i as nvarchar(5)) + 					  	
						',deposit ,PTDOPNDAT,pTDXPRDAT,TDOPNAMT, cast (TBTEMPRATE as float),TFDPINTFRQ,TDDURATN, '+cast(@TdINTDAY as nvarchar(5))+','''+
						@pdate+''','+cast( @interest as nvarchar(255))+' from myrisk_info     where  cast (id as bigint ) =  ' +cast(@row as nvarchar(255))						
					  --  print (@sql)
					    exec( @sql)					    
				 	 end
				 	 else if @i<@TDDURATN
				 	 begin				 	 
				 		set @interest= (select case when @TFDPINTFRQ=1 then 
				 		@TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( 0,@pdate,'mm')))/36500
				 							else case when 	 @TFDPINTFRQ=12 
				 							then @TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( 0,@pdate,'yy')))/36500
				 							    end
											end )
						if  @TFDPINTFRQ=1
						 set @pdate= dbo.PersianDateAdd( 0,@pdate,'mm')						 
						else 
						 set @pdate= dbo.PersianDateAdd( 0,@pdate,'yy')
					set @sql='insert into RiskCashFlow900331 ( tedad, deposits,opndat,exprdat,opnamnt,rate,frq,durant,intday,calcdate,interest) select ' + cast (@i as nvarchar(5)) + 
					', deposit,PTDOPNDAT,pTDXPRDAT,TDOPNAMT , TBTEMPRATE ,TFDPINTFRQ,TDDURATN,'+cast(@TdINTDAY as nvarchar(5))+','''+
					@pdate+''','+cast( @interest as nvarchar(255))+' from myrisk_info     where  cast (id as bigint ) =  ' +cast(@row as nvarchar(255))						
				--print (@sql)
					exec (@sql)
					end
				
				else if @i=@TDDURATN
				 	 begin
				 	 
				 		set @interest= (select case when @TFDPINTFRQ=1 then 
				 		(@TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,@pTDXPRDAT))/36500)+cast(@TDOPNAMT as decimal(38,0))
				 							else case when 	 @TFDPINTFRQ=12 
				 							then 
				 		 (@TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,@pTDXPRDAT))/36500)+cast(@TDOPNAMT as decimal(38,0))
				 							    end
											end )						
					set @sql='insert into RiskCashFlow900331 ( tedad, deposits,opndat,exprdat,opnamnt,rate,frq,durant,intday,calcdate,interest) select ' + cast (@i as nvarchar(5)) + 
					', deposit,PTDOPNDAT,pTDXPRDAT,TDOPNAMT , TBTEMPRATE ,TFDPINTFRQ,TDDURATN,'+cast(@TdINTDAY as nvarchar(5))+','''+@pTDXPRDAT+''','+cast( @interest as nvarchar(255))+' from myrisk_info     where  cast (id as bigint ) =  ' +cast(@row as nvarchar(255))						
				print (@sql)
				 exec (@sql)
					end
					
								set @i=@i+@TFDPINTFRQ
								
								    
				end
		
end
set @row=@row+1
end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-11 : 01:19:10

you need to re-write your query in set-based instead of while-loop. Also i don't see any need to use dynamic SQL here at all.

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-11 : 01:43:57

This should give you a head start. All the above codes can be replaced by the query above. Single INSERT statement with couple of CASE WHEN to cater for difference in calculation of interest.

I have no idea and didn't attempt to understand your business logic. This is merely a COPY and PASTE job with some FIND & REPLACE in between.

The only thing i have added is the CROSS JOIN to the NUMBERS table to replace the while loop of @i. NUBMERS is just a table with numbers. You can create it like


CREATE TABLE NUMBERS
(
   NUM int
)

and then use a while loop to fill it with NUM from 0 to a large number that sufficient for your @i


insert into RiskCashFlow900331 ( tedad, deposits, opndat, exprdat, opnamnt, rate, frq, durant, intday, calcdate, interest) 
select	TDDURATN, deposit, PTDOPNDAT, pTDXPRDAT, TDOPNAMT, cast(TBTEMPRATE as float), TFDPINTFRQ, TDDURATN, @TdINTDAY, 
	pdate	= case 	when 	TFDPINTFRQ = 1
			then 	dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'mm' )
			else 	dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'yy' )
			end,
	interest= case  when 	n.NUM = 0
			then
				case 	when 	TFDPINTFRQ = 1 
					then 
						TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdatePTDOPNDAT,dbo.PersianDateAdd( TdINTDAY, PTDOPNDAT, 'mm' ) / 36500
					else 
						case	when TFDPINTFRQ = 12 
							then TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdatePTDOPNDAT,dbo.PersianDateAdd( TdINTDAY, PTDOPNDAT, 'yy' ) / 36500
							end
					end
			when 	n.NUM < TDDURATN - 1
			then
				case 	when 	TFDPINTFRQ = 1 
					then
						TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdatePTDOPNDAT,dbo.PersianDateAdd( 0, PTDOPNDAT, 'mm' ) / 36500
					else 
						case 	when TFDPINTFRQ = 12
							then TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdatePTDOPNDAT,dbo.PersianDateAdd( 0, PTDOPNDAT, 'yy') / 36500
							end
					end
			else
				 case 	when 	TFDPINTFRQ = 1 
					then
						(TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(PTDOPNDAT, pTDXPRDAT ) / 36500 ) + TDOPNAMT
					else 
						case 	when TFDPINTFRQ = 12
							then (TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(PTDOPNDAT, pTDXPRDAT) / 36500) + TDOPNAMT
							end
					end
			end
from 	myrisk_info 
	cross apply NUMBERS n
where	TDDURATN	<> TFDPINTFRQ
and	n.NUM between 0 and TDDURATN - 1
and	n.NUM % TFDPINTFRQ = 0

KH
[spoiler]Time is always against us[/spoiler]

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-07-11 : 23:58:36

Hi,
I tryed to use the code
but I get error near Apply
I've never used Cross aply statement
and I dont know what the error may be
I'm checking lines before but I still havent understand what the problem is
I use sql server 2000
Is cross apply supported by Sql server 2000


insert into RiskCashFlow900331 (  deposits, opndat, exprdat, opnamnt, rate, frq, durant, intday, calcdate, interest) 
select	 deposit, PTDOPNDAT, pTDXPRDAT, TDOPNAMT, cast(TBTEMPRATE as float), TFDPINTFRQ, TDDURATN, TdINTDAY, 
	@pdate	= 2
	,
	
	@interest=1
	end
from 	myrisk_info_2 
	cross apply NUMBERS n
where	TDDURATN	<> TFDPINTFRQ
and	n.NUM between 0 and TDDURATN - 1
and	n.NUM % TFDPINTFRQ = 0

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-07-13 : 02:23:22

in this solution i need to calculate @pdate according to privous
@pdate so the interest would be calcuted .

thanks for tour help

this is My original table


deposit	ID	ABRNCHCOD	TBDPTYPE	CFCIFNO	TDSERIAL	TBRATEDATE	TBTEMPRATE	TFDPINTFRQ	PTDRNWDAT	PTDXPRDAT	PTDOPNDAT	TDRNWDAT	TDXPRDAT	TDOPNDAT	TDOPNAMT	TDDURATN	TDSTTFRQ	TDXTRINT	TDINTDAT	TDINTDAY
384-904-813921-1	4	384	904	813921	1	1387/02/07	17.50	1	1387/05/01	1388/05/01	1387/05/01	7/22/2008	7/23/2009 2:14:10 PM	7/22/2008	33000000.00	12				1
392-900-1763-3	6	392	900	1763	3	1386/09/01	17.25	1	1386/10/01	1387/10/01	1386/10/01	12/22/2007	12/21/2008 10:44:34 AM	12/22/2007	20000000.00	12				1

and this would be a proper result


row	tedad	deposits	opndat	exprdat	opnamnt	rate	frq	durant	intday	calcdate	interest
1	1	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1387/06/01	490479
2	2	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1387/07/01	490479
3	3	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1387/08/01	474658
4	4	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1387/09/01	474658
5	5	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1387/10/01	474658
6	6	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1387/11/01	474658
7	7	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1387/12/01	474658
8	8	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1388/01/01	474658
9	9	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1388/02/01	490479
10	10	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1388/03/01	490479
11	11	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1388/04/01	490479
12	12	384-904-813921-1	1387/05/01	1388/05/01	33000000	17.5	1	12	1	1388/05/01	33490479
49021	1	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1383/08/01	204932
49022	2	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1383/09/01	180822
49023	3	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1383/10/01	180822
49024	4	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1383/11/01	180822
49025	5	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1383/12/01	180822
49026	6	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/01/01	180822
49027	7	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/02/01	186849
49028	8	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/03/01	186849
49029	9	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/04/01	186849
49030	10	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/05/01	186849
49031	11	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/06/01	186849
49032	12	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/07/01	186849
49033	13	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/08/01	180822
49034	14	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/09/01	180822
49035	15	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/10/01	180822
49036	16	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/11/01	180822
49037	17	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1384/12/01	180822
49038	18	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/01/01	174795
49039	19	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/02/01	186849
49040	20	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/03/01	186849
49041	21	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/04/01	186849
49042	22	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/05/01	186849
49043	23	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/06/01	186849
49044	24	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/07/01	186849
49045	25	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/08/01	180822
49046	26	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/09/01	180822
49047	27	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/10/01	180822
49048	28	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/11/01	180822
49049	29	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1385/12/01	180822
49050	30	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/01/01	174795
49051	31	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/02/01	186849
49052	32	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/03/01	186849
49053	33	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/04/01	186849
49054	34	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/05/01	186849
49055	35	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/06/01	186849
49056	36	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/07/01	186849
49057	37	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/08/01	180822
49058	38	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/09/01	180822
49059	39	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/10/01	180822
49060	40	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/11/01	180822
49061	41	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1386/12/01	180822
49062	42	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/01/01	174795
49063	43	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/02/01	186849
49064	44	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/03/01	186849
49065	45	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/04/01	186849
49066	46	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/05/01	186849
49067	47	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/06/01	186849
49068	48	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/07/01	186849
49069	49	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/08/01	180822
49070	50	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/09/01	180822
49071	51	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/10/01	180822
49072	52	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/11/01	180822
49073	53	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1387/12/01	180822
49074	54	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1388/01/01	180822
49075	55	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1388/02/01	186849
49076	56	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1388/03/01	186849
49077	57	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1388/04/01	186849
49078	58	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1388/05/01	186849
49079	59	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1388/06/01	186849
49080	60	365-986-811534-7	1383/06/28	1388/06/28	10000000	22	1	60	1	1388/06/28	10162740

according to original code each @pdate depends on the previous row in case it belongs to its deposit.
so the interest depends on @pdate.
thanks for help
-----------------------------------



CREATE TABLE [salavizadeh].[myrisk_info_final](
	[id] [bigint] IDENTITY(1,1) NOT NULL,
	[deposit] [varchar](1023) NULL,
	[TBRATEDATE] [varchar](255) NULL,
	[TBTEMPRATE] [float] NULL,
	[TFDPINTFRQ] [int] NULL,
	[PTDRNWDAT] [varchar](255) NULL,
	[PTDXPRDAT] [varchar](255) NULL,
	[PTDOPNDAT] [varchar](255) NULL,
	[TDRNWDAT] [varchar](255) NULL,
	[TDXPRDAT] [varchar](255) NULL,
	[TDOPNDAT] [varchar](255) NULL,
	[TDOPNAMT] [decimal](38, 0) NULL,
	[TDDURATN] [int] NULL,
	[TDINTDAY] [int] NULL
) ON [PRIMARY]


CREATE NONCLUSTERED INDEX [indexid] ON [salavizadeh].[myrisk_info_final] 
(
	[ID] ASC
)

----------------------------------------

create table salavizadeh.RiskCashFlow900331_final 
(
row bigint identity(1,1),
tedad int,
deposits [nvarchar](500) NULL,
opndat [nvarchar](12) NULL,
exprdat [nvarchar](12) NULL,
opnamnt decimal(38,0) NULL,
rate float NULL,
frq int NULL,
durant int NULL,
intday int null,
calcdate [nvarchar](10) NULL,
interest decimal(38,0) NULL
)

--------------------------------


declare @TDDURATN int
declare @TFDPINTFRQ int
declare @TdINTDAY int
DECLARE @PTDOPNDAT            varchar(10)
DECLARE @pTDXPRDAT           varchar(10)

declare @TBTEMPRATE           float
declare @tdxtrint           float 
DECLARE @TDOPNAMT          decimal(20,0)
DECLARE @date                 nvarchar(10)
DECLARE @pdate                 nvarchar(10)

DECLARE @interest             decimal(20,0)
DECLARE @sql                  varchar(8000)
DECLARE @i                  int 
DECLARE @row                 int 
DECLARE @totalrow                 bigint 

set @totalrow =  (select max (ID ) from  salavizadeh.myrisk_info_final    )
set @row=1
while @row<=200000
begin --1
	set @TDDURATN=(select TDDURATN   from  salavizadeh.myrisk_info_final      where  id   = @row)
	set @TFDPINTFRQ=(select TFDPINTFRQ   from  salavizadeh.myrisk_info_final      where  id   = @row)
	if( @TDDURATN<> @TFDPINTFRQ ) --1m
		begin
			set @pdate = (select PTDOPNDAT   from  salavizadeh.myrisk_info_final      where  id = @row )
			set @pTDXPRDAT = (select pTDXPRDAT   from  salavizadeh.myrisk_info_final      where  id = @row )
			set @date = (select TDOPNDAT   from  salavizadeh.myrisk_info_final      where  id = @row )
			set @TdINTDAY = (select TdINTDAY     from  salavizadeh.myrisk_info_final      where  id = @row )
			set @TBTEMPRATE=(select TBTEMPRATE   from  salavizadeh.myrisk_info_final      where  id = @row)
			--set @tdxtrint=(select case when tdxtrint <>'' then  tdxtrint    
			--						   else  0 end
			--		       from  salavizadeh.myrisk_info_final      where  id = @row)
			set @TDOPNAMT=(select TDOPNAMT  from  salavizadeh.myrisk_info_final      where  id = @row)
			set @i=1
			while @i<=@TDDURATN 
				 begin				 	 
				 	 
					if @i=1  --first  date
				 	 begin
				 	 		 	  
				 	   set @interest= (select case when @TFDPINTFRQ=1 then 
				 			@TDOPNAMT*(@TBTEMPRATE)*
				 			(dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( @TdINTDAY,@pdate,'mm')))/36500
				 							else case when 	 @TFDPINTFRQ=12 then 
				 							@TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( @TdINTDAY,@pdate,'yy')))/36500
				 							    end
											end )
						if  @TFDPINTFRQ=1						
						begin  set @pdate= dbo.[PersianDateAdd]( @TdINTDAY,@pdate,'mm')
						  print (@pdate)--(cast (@i as nvarchar(10)))
						  --(cast ( @pdate as nvarchar(10)) + ' ' +@PTDOPNDAT)
						  end
						else 
						  begin set @pdate= dbo.PersianDateAdd( @TdINTDAY,@pdate,'yy')
						  --print(cast ( @pdate as nvarchar(10)) + ' ' +@PTDOPNDAT)
						  end
				set @sql='insert into salavizadeh.RiskCashFlow900331_final 
( tedad, deposits,opndat,exprdat,opnamnt,rate,frq,durant,intday,calcdate,interest) '+
'
select '+cast(@i as nvarchar(3))+' deposit,PTDOPNDAT,pTDXPRDAT,TDOPNAMT,TBTEMPRATE,TFDPINTFRQ,TDDURATN,TdINTDAY,
'''+@pdate+''','+cast(@interest as nvarchar (50))+'
from salavizadeh.myrisk_info_final      where  cast (id as nvarchar(10)= '+ cast (@row as nvarchar(10))

					   print( @sql)					    
				 	 end
				 	 else if @i<@TDDURATN
				 	 begin				 	 
				 		set @interest= (select case when @TFDPINTFRQ=1 then 
				 		@TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( 0,@pdate,'mm')))/36500
				 							else case when 	 @TFDPINTFRQ=12 
				 							then @TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( 0,@pdate,'yy')))/36500
				 							    end
											end )
						if  @TFDPINTFRQ=1
						 set @pdate= dbo.PersianDateAdd( 0,@pdate,'mm')						 
						else 
						 set @pdate= dbo.PersianDateAdd( 0,@pdate,'yy')
					set @sql
='insert into salavizadeh.RiskCashFlow900331_final 
( tedad, deposits,opndat,exprdat,opnamnt,rate,frq,durant,intday,calcdate,interest) '+
'
select '+cast(@i as nvarchar(3))+' deposit,PTDOPNDAT,pTDXPRDAT,TDOPNAMT,TBTEMPRATE,TFDPINTFRQ,TDDURATN,TdINTDAY,
'''+@pdate+''','+cast(@interest as nvarchar (50))+'
from salavizadeh.myrisk_info_final      where  cast (id as nvarchar(10)= '+ cast (@row as nvarchar(10))

					--exec (@sql)
					end
				
				else if @i=@TDDURATN
				 	 begin
				 	 
				 		set @interest= (select case when @TFDPINTFRQ=1 then 
				 		(@TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,@pTDXPRDAT))/36500)+@TDOPNAMT  
				 							else case when 	 @TFDPINTFRQ=12 
				 							then 
				 		 (@TDOPNAMT*(@TBTEMPRATE)*(dbo.ShamsiDateDiff(@pdate,@pTDXPRDAT))/36500)+@TDOPNAMT 
				 							    end
											end )						
					set @sql
='insert into salavizadeh.RiskCashFlow900331_final 
( tedad, deposits,opndat,exprdat,opnamnt,rate,frq,durant,intday,calcdate,interest) '+
'
select '+cast(@i as nvarchar(3))+' deposit,PTDOPNDAT,pTDXPRDAT,TDOPNAMT,TBTEMPRATE,TFDPINTFRQ,TDDURATN,TdINTDAY,
'''+@pdate+''','+cast(@interest as nvarchar (50))+'
from salavizadeh.myrisk_info_final      where  cast (id as nvarchar(10)= '+ cast (@row as nvarchar(10))
						
				print (@sql)
				 exec (@sql)
					end
					
								set @i=@i+@TFDPINTFRQ
								
								    
				end
		
end
set @row=@row+1
end

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-13 : 06:29:53

sorry, should be CROSS JOIN

KH
[spoiler]Time is always against us[/spoiler]

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-07-16 : 00:21:23

Hi,
This is what I tryed , But I get following error.

Msg 141, Level 15, State 1, Line 44
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


declare @pdate nvarchar(10)
declare @interest decimal(38,0)

insert into salavizadeh.RiskCashFlow900331_final_  ( tedad, deposits, opndat, exprdat, opnamnt, rate, frq, durant, intday, calcdate, interest) 
select	TDDURATN, deposit, PTDOPNDAT, pTDXPRDAT, TDOPNAMT, cast(TBTEMPRATE as float), TFDPINTFRQ, TDDURATN, TdINTDAY, 
	@pdate	= case 	when 	TFDPINTFRQ = 1
			then 	dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'mm' )
			else 	dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'yy' )
			end,
	@interest= case  when 	n.NUM = 0
			then
				case 	when 	TFDPINTFRQ = 1 
					then 
						TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( TdINTDAY, PTDOPNDAT, 'mm' )) / 36500
					else 
						case	when TFDPINTFRQ = 12 
							then TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( TdINTDAY, PTDOPNDAT, 'yy' )) / 36500
							end
					end
			when 	n.NUM < TDDURATN - 1
			then
				case 	when 	TFDPINTFRQ = 1 
					then
						TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( 0, PTDOPNDAT, 'mm' )) / 36500
					else 
						case 	when TFDPINTFRQ = 12
							then TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( 0, PTDOPNDAT, 'yy')) / 36500
							end
					end
			else
				 case 	when 	TFDPINTFRQ = 1 
					then
						(TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(PTDOPNDAT, pTDXPRDAT ) / 36500 ) + TDOPNAMT
					else 
						case 	when TFDPINTFRQ = 12
							then (TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(PTDOPNDAT, pTDXPRDAT) / 36500) + TDOPNAMT
							end
					end
			end
from 	[myrisk_info_final] 
	cross join NUMBERS n
where	TDDURATN	<> TFDPINTFRQ
and	n.NUM between 0 and TDDURATN - 1
and	n.NUM % TFDPINTFRQ = 0

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-07-16 : 00:26:02

And thease are UDFs:



create function [dbo].[nextMonth] (@tarikh nvarchar(10))
RETURNS nvarchar(10)
as
begin
declare @year nvarchar(4)
declare @month nvarchar(2)
declare @day nvarchar(2)
declare @yearInt bigint
declare @monthInt bigint
declare @dayInt bigint
--------------
set @year=substring (@tarikh,1,4)
set @month=substring (@tarikh,6,2)
set @day=substring (@tarikh,9,2)
--------------
set @yearInt=cast(@year as bigint)
set @dayInt=cast(@day as bigint)
set @monthInt=cast(@month as bigint)+1
--------------

if (@monthInt=13)
	begin
		set @monthInt=1
		set @yearInt=@yearInt+1
		--if (@dayInt between 29 and 30)
		--	set @dayInt=31
					

	end
if ((@monthInt between 7 and 11) and (@dayInt>30))
    begin
		set @dayInt=30
	end
if ((@monthInt =12) and (@dayInt >29))
    begin
		set @dayInt=29
	end

set @year=cast(@yearInt as nvarchar(4))
set @month=cast(@monthInt as nvarchar(2))
if @monthInt<10  set @month='0'+cast(@monthInt as nvarchar(2))
set @day=cast(@dayInt as nvarchar(2))
if @dayInt<10  set @day='0'+cast(@dayInt as nvarchar(2))


	
return @year+'/'+ @month+'/'+ @day


end


--select  [dbo].[FormatdateMiladi]('10/14/2008 12:11:47 PM')



Create FUNCTION [dbo].[ShamsiDateDiff] (@PDate1 nvarchar(12),@PDate2 nvarchar(12))
RETURNS bigint
AS
BEGIN
DECLARE @ISLeap bit

DECLARE @EYear as Integer
DECLARE @EMonth as Integer
DECLARE @EDay as Integer
DECLARE @EAllDays as int

set @EYear= cast((substring(@PDate2 ,0,5))as int)
set @EMonth= cast((substring(@PDate2 ,6,2))as int)
set @EDay= cast((substring(@PDate2 ,9,2))as int)

DECLARE @SAllDays as int
DECLARE @SYear as Integer
DECLARE @SMonth as Integer
DECLARE @SDay as Integer
set @SYear= cast((substring(@PDate1 ,0,5))as int)
set @SMonth= cast((substring(@PDate1 ,6,2))as int)
set @SDay= cast((substring(@PDate1 ,9,2))as int)


select @EAllDays = case when  @EMonth <=6 
      then  (@EMonth-1)*31+@EDay
      else case when @EMonth >6  and @EMonth<=12
       then 6*31+ (@EMonth-7)*30+@EDay   
    end
    end,
      @SAllDays = case when  @SMonth <=6 
      then  (@SMonth-1)*31+@SDay
      else case when @SMonth >6  and @SMonth<=12
       then 6*31+ (@SMonth-7)*30+@SDay
    end
    end

declare @diff int
set @diff =@EAllDays-@SAllDays
declare @i int set @i =0           
 while @SYear+@i<@EYear
  begin
     select @diff= case 
                     when   dbo.IsLeap(@SYear+@i)=1
                      then @diff+366
                     else @diff+365
                     end
  set @i=@i+1                   
  end


Return @diff
end



Create FUNCTION [dbo].[PersianDateAdd]
(
	-- Add the parameters for the function here
	@INTDAY int, @date nvarchar(10),@interval nvarchar(2)--persian
)
RETURNS  nvarchar(10)
AS
BEGIN
	-- Declare the return variable here
declare @newdate nvarchar(10)
declare @indate nvarchar (10)
declare @mindate smalldatetime

declare @yy nvarchar (4)
declare @mm nvarchar (2)
declare @dd nvarchar (2)

declare @yy2 nvarchar (4)
declare @mm2 nvarchar (2)
declare @dd2 nvarchar (2)

set @yy=parsename(replace (   @date,'/','.'),3)
set @mm =parsename(replace (   @date,'/','.'),2)
set @dd=parsename(replace (   @date,'/','.'),1)
 
if @interval='mm'
begin--*************
  set @indate = dbo.nextmonth(@date)
  
  	set @yy2=parsename(	replace (@indate,'/','.'),3)	
		set @mm2 =parsename(	replace (@indate,'/','.'),2)	
			
  	   
		
  if(@INTDAY<>0)
	begin
	  
	set @dd2=case when @INTDAY <10 then '0'+cast (@INTDAY as nvarchar(2))
				else cast (@INTDAY as nvarchar(2))
				end
				
		
	
		set @indate=@yy2+'/'+@mm2+'/'+@dd2
		
        if   dbo.shamsidatediff(@date ,@indate	)<30
		  begin 
		  set @indate = dbo.nextmonth(@indate)
		  set @yy2=parsename(	replace (@indate,'/','.'),3)	
		  set @mm2 =parsename(	replace (@indate,'/','.'),2)	
			set @indate=@yy2+'/'+@mm2+'/'+@dd2
		  
		  end
		
		  
		
		
			
	end

	else if @INTDAY=0
	begin	
			
		set @dd2= parsename(replace (   @date,'/','.'),1)		
		if  cast (@mm as int )=12 and cast (@dd  as int )=29
		set @dd2 ='31'
		
	
	end
	set @indate=@yy2+'/'+@mm2+'/'+@dd2	
	select @newdate=@indate
	

end--***************
else if @interval='yy'
begin
set @yy2=cast ((cast(@yy as int)+1) as nvarchar(4))
set @mm2=@mm

  if(@INTDAY<>0)
begin
	
	set @dd2=case when @INTDAY <10 then '0'+cast (@INTDAY as nvarchar(2))
				else cast (@INTDAY as nvarchar(2))
				end 
	set @indate=@yy2+'/'+@mm+'/'+@dd2
	if @dd2	<@dd
	begin set @indate= dbo.nextmonth(@indate)
		set @yy2=parsename(	replace (@indate,'/','.'),3)	
		set @mm2 =parsename(	replace (@indate,'/','.'),2)	
		
	end	
		
	set @indate= @yy2+'/'+ @mm2+'/'+@dd2
	

end

	else
	begin 	
	set @dd2= parsename(replace (   @date,'/','.'),1)		
	end
	
end

 if cast( @mm2 as int )<12 and cast (@mm2 as int )>=7   and @dd2 ='31' 
 set @dd2='30'
 if cast (@mm2 as int )=12  and (@dd2 ='31' or @dd2='30'  )and dbo.isleap(@yy2+'/'+@mm2+'/'+@dd2)=0
 set @dd2='29'
 
 set @indate=@yy2+'/'+@mm2+'/'+@dd2	
 select @newdate=@indate	  

RETURN @newdate
END

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-16 : 00:53:36

quote:

Originally posted by fan2005

Hi,
This is what I tryed , But I get following error.

Msg 141, Level 15, State 1, Line 44
A SELECT statement that assigns a value to a variable must not be combined with data-retrieval operations.


declare @pdate nvarchar(10)
declare @interest decimal(38,0)

insert into salavizadeh.RiskCashFlow900331_final_  ( tedad, deposits, opndat, exprdat, opnamnt, rate, frq, durant, intday, calcdate, interest) 
select	TDDURATN, deposit, PTDOPNDAT, pTDXPRDAT, TDOPNAMT, cast(TBTEMPRATE as float), TFDPINTFRQ, TDDURATN, TdINTDAY, 
	@pdate	= case 	when 	TFDPINTFRQ = 1
			then 	dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'mm' )
			else 	dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'yy' )
			end,
	@interest= case  when 	n.NUM = 0
			then
				case 	when 	TFDPINTFRQ = 1 
					then 
						TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( TdINTDAY, PTDOPNDAT, 'mm' )) / 36500
					else 
						case	when TFDPINTFRQ = 12 
							then TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( TdINTDAY, PTDOPNDAT, 'yy' )) / 36500
							end
					end
			when 	n.NUM < TDDURATN - 1
			then
				case 	when 	TFDPINTFRQ = 1 
					then
						TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( 0, PTDOPNDAT, 'mm' )) / 36500
					else 
						case 	when TFDPINTFRQ = 12
							then TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdate,dbo.PersianDateAdd( 0, PTDOPNDAT, 'yy')) / 36500
							end
					end
			else
				 case 	when 	TFDPINTFRQ = 1 
					then
						(TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(PTDOPNDAT, pTDXPRDAT ) / 36500 ) + TDOPNAMT
					else 
						case 	when TFDPINTFRQ = 12
							then (TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(PTDOPNDAT, pTDXPRDAT) / 36500) + TDOPNAMT
							end
					end
			end
from 	[myrisk_info_final] 
	cross join NUMBERS n
where	TDDURATN	<> TFDPINTFRQ
and	n.NUM between 0 and TDDURATN - 1
and	n.NUM % TFDPINTFRQ = 0

remove all the @ from the query

KH
[spoiler]Time is always against us[/spoiler]

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-07-16 : 00:57:39

you should also replace @pdate with the actual column name from the table


case	when TFDPINTFRQ = 12 
	then TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(@pdate PTDOPNDAT,dbo.PersianDateAdd( TdINTDAY, PTDOPNDAT, 'yy' )) / 36500
	end

KH
[spoiler]Time is always against us[/spoiler]

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-07-16 : 01:55:12

quote:
Originally posted by khtan

you should also replace @pdate with the actual column name from the table

case when TFDPINTFRQ = 12
then TDOPNAMT * TBTEMPRATE * dbo.ShamsiDateDiff(~~@pdate~~ PTDOPNDAT,dbo.PersianDateAdd( TdINTDAY, PTDOPNDAT, 'yy' )) / 36500
end

KH

[spoiler]Time is always against us[/spoiler]

Dear khtan
How can I use PTDOPNDAT instead of @pdate when it's related to @pdate previous value
it should add one month to original PTDOPNDAT in every row inserted ,for each deposits.
I get this result

row	tedad	deposits	opndat	exprdat	opnamnt	rate	frq	durant	intday	calcdate	interest
1	36	172-946-750872-3	1384/11/05	1387/11/05	3000000	18.2	1	36	1	1385/01/01	82274
2	36	172-946-750872-3	1384/11/05	1387/11/05	3000000	18.2	1	36	1	1385/01/01	44877

which is not what i expected
calcdate is all the same
It sould be first '1385/01/01'
then '1385/02/01'
and so on
thank you

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-07-16 : 02:11:35

I think May be i need quicky update and insert.
please help me with this problem

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources