Cursor

• joemai

  Hall of Fame

  Points: 3496

  More actions

  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

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  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

  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

  February 12, 2004 at 7:51 pm

  #493922

  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

  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

  February 17, 2004 at 12:44 pm

  #494425

  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.