| Author |
 Topic  |
|
|
fan2005
Yak Posting Veteran
77 Posts |
 Posted - 07/10/2011 : 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 |
Edited by - fan2005 on 07/11/2011 00:09:23
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 07/11/2011 : 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
Time is always against us
|
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 07/11/2011 : 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
Time is always against us
|
Edited by - khtan on 07/16/2011 01:00:18 |
|
|
|
fan2005
Yak Posting Veteran
77 Posts |
Posted - 07/11/2011 : 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
|
Edited by - fan2005 on 07/12/2011 00:02:36 |
|
|
|
fan2005
Yak Posting Veteran
77 Posts |
Posted - 07/13/2011 : 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)
Singapore
16745 Posts |
Posted - 07/13/2011 : 06:29:53
|
sorry, should be CROSS JOIN
KH
Time is always against us
|
|
|
|
fan2005
Yak Posting Veteran
77 Posts |
Posted - 07/16/2011 : 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
77 Posts |
Posted - 07/16/2011 : 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)
Singapore
16745 Posts |
Posted - 07/16/2011 : 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
Time is always against us
|
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16745 Posts |
Posted - 07/16/2011 : 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
Time is always against us
|
Edited by - khtan on 07/16/2011 01:00:56 |
|
|
|
fan2005
Yak Posting Veteran
77 Posts |
Posted - 07/16/2011 : 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
Time is always against us
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
|
Edited by - fan2005 on 07/16/2011 02:03:31 |
|
|
|
fan2005
Yak Posting Veteran
77 Posts |
Posted - 07/16/2011 : 02:11:35
|
I think May be i need quicky update and insert.
please help me with this problem |
Edited by - fan2005 on 07/16/2011 02:17:34 |
|
|
| |
Topic |
|
|
|
Help with faster solution for this code
