please Help me with this thread

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

Transact-SQL (2008)

please Help me with this thread

Author

Topic

fan2005
Yak Posting Veteran

85 Posts

Posted - 2011-07-16 : 03:24:55

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

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?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources