SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2008 Forums
 Transact-SQL (2008)
 please Help me with this thread
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

fan2005
Yak Posting Veteran

84 Posts

Posted - 07/16/2011 :  03:24:55  Show Profile  Reply with Quote

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)

Singapore
17642 Posts

Posted - 07/16/2011 :  04:23:31  Show Profile  Reply with Quote
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
Time is always against us


Edited by - khtan on 07/16/2011 04:25:06
Go to Top of Page

fan2005
Yak Posting Veteran

84 Posts

Posted - 07/16/2011 :  05:43:18  Show Profile  Reply with Quote
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".


Edited by - fan2005 on 06/21/2013 08:50:43
Go to Top of Page

skylar
Starting Member

2 Posts

Posted - 07/16/2011 :  08:31:20  Show Profile  Visit skylar's Homepage  Reply with Quote
;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

Go to Top of Page

fan2005
Yak Posting Veteran

84 Posts

Posted - 07/16/2011 :  10:01:20  Show Profile  Reply with Quote
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
Go to Top of Page

khtan
In (Som, Ni, Yak)

Singapore
17642 Posts

Posted - 07/16/2011 :  10:12:36  Show Profile  Reply with Quote
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
Time is always against us

Go to Top of Page

fan2005
Yak Posting Veteran

84 Posts

Posted - 07/16/2011 :  14:29:05  Show Profile  Reply with Quote
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?


Edited by - fan2005 on 07/16/2011 14:41:59
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000