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.
| Author |
Topic |
|
fan2005
Yak Posting Veteran
85 Posts |
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-07-16 : 04:23:31
|
are you using SQL 2000 or 2008 ?
If you are using SQL 2005 or later, you can use recursive CTE to do it easily. For 2000, you got to use while loop
KH
[spoiler]Time is always against us[/spoiler]
|
 |
|
|
fan2005
Yak Posting Veteran
85 Posts |
Posted - 2011-07-16 : 05:43:18
|
quote:
Originally posted by fan2005
Can any one help me with this thread
if there is any solution in sql 2008.
(CTE , ...)
http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=162806
---------------------------------------------------------
I can use Both
I know sql server 2008 is more powerful so i decided to use 2008
;WITH CTE AS
(
SELECT deposit,PTDOPNDAT,TDXPRDAT,TDDURATN,TDINTDAY, PTDOPNDAT as EndDate
FROM x
where TFDPINTFRQ=1
UNION ALL
SELECT A.deposit, A.PTDOPNDAT,A.TDXPRDAT, A.TDDURATN,A.TDINTDAY
, cast (dbo.[PersianDateAdd] ( A.TdINTDAY, A.EndDate, 'mm' ) as nvarchar(10))
FROM CTE A
WHERE A.EndDate < A.TDXPRDAT
)
SELECT deposit, PTDOPNDAT,TDXPRDAT, TDDURATN,TDINTDAY, EndDate
FROM CTE
ORDER BY 1
I tryed to write one but I get error .i'm new to cte
Types don't match between the anchor and the recursive part in column "EndDate" of recursive query "CTE".
|
|
|
|
skylar
Starting Member
2 Posts |
Posted - 2011-07-16 : 08:31:20
|
;WITH CTE AS
(
SELECT deposit,PTDOPNDAT,PTDXPRDAT,TDDURATN,TFDPINTFRQ,TBTEMPRATE,TDOPNAMT,TDINTDAY,1 as tedad,
case when TFDPINTFRQ=1 --??????
then
CAST( dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'mm' ) as nvarchar(10))
else case when
TFDPINTFRQ=12
then
CAST( dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'yy' ) as nvarchar(10))
end end as EndDate,
case when TFDPINTFRQ=1 --??????
then
cast (TDOPNAMT*TBTEMPRATE*
dbo.ShamsiDateDiff(PTDOPNDAT,CAST( dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'mm' ) as nvarchar(10)) )/36500 as decimal(38,0))
else case when TFDPINTFRQ=12 --??????
then
cast (TDOPNAMT*TBTEMPRATE*
dbo.ShamsiDateDiff(PTDOPNDAT,CAST( dbo.[PersianDateAdd] ( TdINTDAY, PTDOPNDAT, 'yy' ) as nvarchar(10)) )/36500 as decimal(38,0))
end
end as interest
FROM salavizadeh.x
UNION ALL
SELECT
A.deposit, A.PTDOPNDAT,A.PTDXPRDAT, A.TDDURATN,A.TFDPINTFRQ,A.TBTEMPRATE,A.TDOPNAMT,A.TDINTDAY ,A.tedad+1,
case when TFDPINTFRQ=1 then CAST( dbo.[PersianDateAdd] ( 0, A.EndDate, 'mm' ) as nvarchar(10))
else case when TFDPINTFRQ=12 then CAST( dbo.[PersianDateAdd] ( 0, A.EndDate, 'yy' ) as nvarchar(10)) end end
,
case when TFDPINTFRQ=1 then
cast ((A.TDOPNAMT*A.TBTEMPRATE*dbo.ShamsiDateDiff(A.EndDate ,dbo.[PersianDateAdd] ( 0, A.EndDate, 'mm' ))/36500 )as decimal(38,0))
else case when TFDPINTFRQ=12 then
cast ((A.TDOPNAMT*A.TBTEMPRATE*dbo.ShamsiDateDiff(A.EndDate ,dbo.[PersianDateAdd] ( 0, A.EndDate, 'yy' ))/36500 )as decimal(38,0))
end
end
FROM CTE A
WHERE tedad < (TDDURATN/TFDPINTFRQ)-1
Union All
SELECT
A.deposit, A.PTDOPNDAT,A.PTDXPRDAT, A.TDDURATN,A.TFDPINTFRQ,A.TBTEMPRATE,A.TDOPNAMT,A.TDINTDAY ,A.tedad+1,
cast (PTDXPRDAT as nvarchar(10))
,
cast (
(A.TDOPNAMT*A.TBTEMPRATE*dbo.ShamsiDateDiff(A.EndDate ,cast (PTDXPRDAT as nvarchar(10)))/36500+A.TDOPNAMT) as decimal(38,0))
FROM CTE A
WHERE tedad = (TDDURATN/TFDPINTFRQ)-1
)
SELECT
deposit, PTDOPNDAT,PTDXPRDAT, TDDURATN,TFDPINTFRQ,TBTEMPRATE,TDOPNAMT
,TDINTDAY,tedad, EndDate ,interest
FROM CTE
ORDER BY 1
|
|
|
|
fan2005
Yak Posting Veteran
85 Posts |
Posted - 2011-07-16 : 10:01:20
|
Its shoking I see my post with someonel else's profile. how it can happen.
Skylar how did you post something i've written right now
|
|
|
|
khtan
In (Som, Ni, Yak)
17689 Posts |
Posted - 2011-07-16 : 10:12:36
|
quote:
Originally posted by fan2005
Its shoking I see my post with someonel else's profile. how it can happen.
Skylar how did you post something i've written right now
You mean that post "Posted - 07/16/2011 : 08:31:20" is by you instead of skylar ? ?
KH
[spoiler]Time is always against us[/spoiler]
|
|
|
|
fan2005
Yak Posting Veteran
85 Posts |
Posted - 2011-07-16 : 14:29:05
|
Yes.exactly
at that time sqlteam.com site started to be crazy
the page wouldn't be loaded properly
and after that there was a message related to " log "
I don't remember what was message.
I thought our network was a problem.
but.. It's strange
those ??? in the code is Persian character that it is not showed here.
how can I change it
whom I can talk to about this problem?
|
|
|
|
|
|
|
|
|
please Help me with this thread