Cursor

Question

Post reply

Cursor

joemai

Hall of Fame

Points: 3496

February 12, 2004 at 2:48 pm

#107075

I have a temp table contains data like this:

Customer SalesAmount AvailableAmount TotalAvailable A $50 $0 $60 A $10 $0 $60 B $100 $0 $150 C $20 $0 $50 How can I code my cursor so that I can have a result like this: Customer SalesAmount AvailableAmount TotalAvailable A $50 $10 $60 A $15 $-5 $60 B $100 $50 $150 C $20 $30 $50 I'm stuck. Please give me a hand. Thanks a million Josh Hua

Viewing 6 posts - 1 through 5 (of 5 total)

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

Allen Cui-55137 SSC Guru Points: 51650 More actions · Answer 1

Allen Cui-55137

SSC Guru

Points: 51650

February 12, 2004 at 5:52 pm

#493916

Is availableamount = totalavailable - salesamount?

if yes, then

select customer, salesamount, totalavailable - salesamount as availableamount, totalavailable from temptable

joemai Hall of Fame Points: 3496 More actions · Answer 2

joemai

Hall of Fame

Points: 3496

February 12, 2004 at 6:10 pm

#493917

Yes,

However, if there is the same customer, I have to take the availableamount - SalesAmount as my first 2 rows for CustomerName A.

Thanks a lot

Josh

inugroho SSC Enthusiast Points: 145 More actions · Answer 3

I utilize primary key to avoid using cursor assuming you have such primary key. Here it is:

declare @db table(
    Customer char(1),
    SalesAmount numeric,
    TotalAvailable numeric, 
    id int identity)
insert @db(customer,salesamount,totalavailable) values ('A',50,60)
insert @db(customer,salesamount,totalavailable) values ('A',15,60)
insert @db(customer,salesamount,totalavailable) values ('B',100,150)
insert @db(customer,salesamount,totalavailable) values ('C',20,50)

SELECT Customer,SalesAmount,
    TotalAvailable-isnull((select sum(SalesAmount)
         from @db z where z.Customer=a.Customer and z.id<a.id),0)-SalesAmount as AvailableAmount,
    TotalAvailable 
FROM @db a
order by customer,id

Hope this helps!

Thanx

YOGIG SSC Enthusiast Points: 174 More actions · Answer 4

YOGIG

SSC Enthusiast

Points: 174

February 16, 2004 at 4:11 pm

#494282

I will recommend you use a DTS will dynamic Querry option. That will be fast than cursor

Thanks

joemai Hall of Fame Points: 3496 More actions · Answer 5

Thanks a lot for all of your help. I can use Inugroho's way to work on since I don't need to use cursor. However, it does not work the way I want it. If there is the same customer, I have to take the Available credit - SalesAmount (not TotalAvailableCredit-SalesAmount).

CustomerName SalesAmount AvailableAmount TotalAvailable

B $10 $5 $15

A $10 $50 $60

A $15 $35 $120

A $35 $0 $150

Inugroho's way is to take TotalAvailable - SalesAmount.

I'm still stuck. Please give me another hand.