Without using CTE, Please provide a recursive method

Question

Post reply

Without using CTE, Please provide a recursive method

Varun R

Ten Centuries

Points: 1171
More actions
March 3, 2011 at 1:07 am

#231967

Hi friends,
I had a table which contain customerID,Invoice,TotalPayment
CID InvoiceAmount Total_pay Result
A 100 5000 4900
A 50 5000 4850
A 50 5000 4800
A 100 5000 4700
.
.
B
In this Result amount s obtained by TotalPay-Invoiceamount,out of TotalPay is changed after each row is
completed.can any 1 provide me a correct solution for this,Dont use CTE because limit exceeds.Please provide solution asap
Regards
Varun r

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 1

Sound like a rolling balance problem,

use a cursor or take a look at a 'quirky update' solution.

Clear Sky SQL
My Blog[/url]

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 2

dba.varun.in (3/3/2011)
I had a table which contain customerID,Invoice,TotalPayment
...
In this Result amount s obtained by TotalPay-Invoiceamount,out of TotalPay is changed after each row is
completed.

If I understand it correctly, Result = TotalPay - InvoiceAmount.

Is this on a row per row basis, or is Result a single value for each CID?

Can you explain your example a bit more?

dba.varun.in (3/3/2011)
Please provide solution asap

You do realize that this is a forum with unpaid volunteers? Since it is all written text down here, it is easily misinterpreted.

You can easily talk like that to a consultant which you pay, but not to me 🙂

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Varun R Ten Centuries Points: 1171 More actions · Answer 3

yes...what you said is correct.Please look the data,you will understand.

REgards

Varun R

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

dba.varun.in (3/3/2011)
yes...what you said is correct.Please look the data,you will understand.
REgards
Varun R

Allright, I took a long hard stare at the example, and I finally noticed the running total.

Here's the article that Dave mentioned:

http://www.sqlservercentral.com/articles/T-SQL/68467/

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

parthi-1705 SSCrazy Eights Points: 9277 More actions · Answer 5

dba.varun.in (3/3/2011)
Hi friends,
I had a table which contain customerID,Invoice,TotalPayment
CID InvoiceAmount Total_pay Result
A 100 5000 4900
A 50 5000 4850
A 50 5000 4800
A 100 5000 4700
.
.
B
In this Result amount s obtained by TotalPay-Invoiceamount,out of TotalPay is changed after each row is
completed.can any 1 provide me a correct solution for this,Dont use CTE because limit exceeds.Please provide solution asap
Regards
Varun r

hi

is this u are trying to get,this may be complex it is just a idea for you to get work on it.if you get better solution on this dont forget to post back your query.

declare @Temp table (CID varchar(5), InvoiceAmount money, Total_pay money )--,result money)

declare @Temp1 table (Rid int,CID varchar(5), InvoiceAmount money, Total_pay money ,result money,Newrid int)

insert into @Temp

values

('A', 100, 5000 ),--,4900),

('A', 50, 5000 ),--,4850),

('A',50, 5000 ),--,4800),

('A', 100, 5000 ),--,4700),

('B', 100, 15000),--, 14900),

('B', 50, 15000),--, 14850),

('B',50, 15000),--, 14800),

('B', 100, 15000)--, 14700)

/* The above is the Table where the datas are stored as like @Temp table*/

;With cte ( CID , InvoiceAmount , Total_pay ,Rid)

as (

Select CID , InvoiceAmount , Total_pay ,ROW_NUMBER() over(PARTITION by CID order by cid) as rid from @Temp

)

insert into @Temp1

Select Rid ,CID , InvoiceAmount , Total_pay ,0,ROW_NUMBER() over(PARTITION by RID order by CID) as Newrid

from cte

/*inserting Rid and Newrid */

DECLARE @Cid varchar(5)

DECLARE @Counter INT

set @Counter=1

While @Counter<=(Select COUNT(*) from @Temp1)

begin

Select @cid=cid from @Temp1 where Newrid=@Counter

update @Temp1

set result =Total_pay-

(Select SUM(InvoiceAmount) from @Temp1 as a where a.Rid<=b.Rid and CID=@cid)

from @Temp1 as b where CID=@cid

set @Counter=@Counter+1

end

Select CID,InvoiceAmount,Total_pay,result from @Temp1

--- Select CID,InvoiceAmount,Total_pay,result from @Temp1 order by CID

Thanks
Parthi

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 6

dba.varun.in (3/3/2011)
Dont use CTE because limit exceeds.

The limit of a CTE will not be exceeded if you set MAXRECURSION to zero. Read about it in Books Online.

Please provide solution asap

Heh... just a friendly tip... I know you said "please" but the word "asap" is a bit of a four letter word around here. I suggest you avoid it in the future. That and the word "urgent" seem to set people off. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Varun R Ten Centuries Points: 1171 More actions · Answer 7

Hi Guys,

I solved the situation spend 1 whole day,Hope this may help u guys,

select A.ID,A.Invoice_Amount,a.Payment_Amount,A.CID,ISNULL(A.Payment_Amount -

(select SUM(B.Invoice_Amount)+A.Invoice_Amount

from TestDetails B

where B.ID < A.ID

and b.cid = A.cid

),A.Payment_Amount - A.Invoice_Amount) AS [SUCESS]

from TestDetails A

Regards

Varun R

Dave Ballantyne SSC-Dedicated Points: 33667 More actions · Answer 8

Just be sure to ensure that performance is within SLA boundaries with a production size of data.

Clear Sky SQL
My Blog[/url]

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 9

dba.varun.in (3/4/2011)
Hi Guys,
I solved the situation spend 1 whole day,Hope this may help u guys,
select A.ID,A.Invoice_Amount,a.Payment_Amount,A.CID,ISNULL(A.Payment_Amount -
(select SUM(B.Invoice_Amount)+A.Invoice_Amount
from TestDetails B
where B.ID < A.ID
and b.cid = A.cid
),A.Payment_Amount - A.Invoice_Amount) AS [SUCESS]
from TestDetails A
Regards
Varun R

Oh my... no.

I'd recommend against the use of that little bit of hidden RBAR. It's actually worse than a cursor or recursive CTE. Please see the article at the following link for why I say that. Then, reconsider the use of a cursor or the "Quirky Update".

[font="Arial Black"]Hidden RBAR: Triangular Joins[/font][/url]

Yeah... I know. The next thing you'll say is that you're never going to use it against very many rows. I know a whole lot of people who have seriously regretted using that justification for using hidden RBAR in their code. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 10

hi varun, can u tell us any specific reason why you don't want to use CTE?if the other approach the comparatively expensive.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Bhuvnesh SSC Guru Points: 59351 More actions · Answer 11

hi varun, can u tell us any specific reason why you don't want to use CTE?if the other approach the comparatively expensive.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 12

Bhuvnesh (3/8/2011)
hi varun, can u tell us any specific reason why you don't want to use CTE?if the other approach the comparatively expensive.

He already did...

Dont use CTE because limit exceeds

I figure the "limit" is the default of 100 recursions and simply needed to be intoduced to the MAXRECURSION setting.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Varun R Ten Centuries Points: 1171 More actions · Answer 13

Hi jeff and Bhuvanesh,

I was little bit busy for past few days.I found; u said not to use this query because it will raise issues when data is huge.I want to rethink about this.Here it is giving better performance than while loop.i will try it with cursor and CTE and get back to u.Your article is a nice one(jeff).We can have a close look into it.I will spend my time for investigating it.I hope all these guys will be with me.

Regards

Varun R

Jeff Moden SSC Guru Points: 1004363 More actions · Answer 14

dba.varun.in (3/10/2011)
Hi jeff and Bhuvanesh,
I was little bit busy for past few days.I found; u said not to use this query because it will raise issues when data is huge.I want to rethink about this.Here it is giving better performance than while loop.i will try it with cursor and CTE and get back to u.Your article is a nice one(jeff).We can have a close look into it.I will spend my time for investigating it.I hope all these guys will be with me.
Regards
Varun R

How are you measuring the "performance"? If it's just by duration, you've not measured all the performance factors especially if the "groups" get larger.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)