with out using the cursor/while

• arun.sas

  SSChampion

  Points: 11831

  More actions

  February 9, 2009 at 11:48 pm

  #72675

  [font="Verdana"]Hi,

  i am writing the sp for sales report

  i have doubts

  create table #temp

  (

  slno int,

  sale_order Nchar(15),

  order_value numeric(13,8),

  budget Nchar(15),

  budget_amt numeric(13,8),

  budget_balance numeric(13,8)

  )

  insert into #temp values

  (1,'ESA-01/08',150.00,'CAP-A/08',1500.00,0.00)

  go

  insert into #temp values

  (2,'ESA-02/08',300.00,'CAP-A/08',1500.00,0.00)

  go

  insert into #temp values

  (3,'ESA-05/08',450.00,'CAP-B/08',1500.00,0.00)

  go

  insert into #temp values

  (4,'ESA-06/08',500.00,'CAP-B/08',1500.00,0.00)

  go

  insert into #temp values

  (5,'ESA-07/08',600.00,'CAP-B/08',1500.00,0.00)

  go

  insert into #temp values

  (6,'ESA-10/08',50.00,'CAP-C/08',1500.00,0.00)

  go

  insert into #temp values

  (7,'ESA-11/08',150.00,'CAP-C/08',1500.00,0.00)

  go

  insert into #temp values

  (8,'ESA-12/08',250.00,'CAP-C/08',1500.00,0.00)

  select * from #temp

  RESULT shoud be like

  slno sale_orderorder_value budget budget_amtbudget_balance

  1ESA-01/08 150CAP-A/08 15000

  2ESA-02/08 300CAP-A/08 15000

  3ESA-05/08 450CAP-B/08 15000

  4ESA-06/08 500CAP-B/08 15000

  5ESA-07/08 600CAP-B/08 15000

  6ESA-10/08 50CAP-C/08 15000

  7ESA-11/08 150CAP-C/08 15000

  8ESA-12/08 250CAP-C/08 15000

  i need the statement below with out using the cursor/while

  slno sale_orderorder_valuebudgetbudget_amtbudget_balance

  1 ESA-01/08 150CAP-A/08 150001350

  2 ESA-02/08 300CAP-A/08 150001050

  3 ESA-05/08 450CAP-B/08 150001050

  4 ESA-06/08 500CAP-B/08 15000550

  5 ESA-07/08 600CAP-B/08 15000-50

  6 ESA-10/08 50CAP-C/08 150001450

  7 ESA-11/08 150CAP-C/08 150001300

  8 ESA-12/08 250CAP-C/08 150001050

  but i am using the updates like

  update #temp

  set budget_balance = case when slno = 1 then budget_amt-order_value

  when slno <> 1 and budget in (select budget from #temp where slno = slno - 1)then (select budget_balance from #temp where slno = slno - 1) - order_value

  when slno <> 1 and budget not in (select budget from #temp where slno = slno - 1)then budget_amt - order_value

  end

  but it's not work

  help how to do without while/cursor to get result with simple

  ARUN SAS[/font]

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  February 10, 2009 at 3:31 am

  #941056

  There are many articles on this forum that solves the running total problems....

  Here is one of the article by Jeff Moden on Solving the "Running Total" & "Ordinal Rank" Problems in SS 2k/2k5[/url], though this article is under the process of re-write (as Jeff found some corrections to be made to 2K5 solutions), it has a SQL 2K solution for it.

  --Ramesh
  

  ---

• arun.sas

  SSChampion

  Points: 11831

  More actions

  February 10, 2009 at 3:51 am

  #941066

  thanks Ramesh

  But this articles only for the order by and its related,

  i need the tips to slove this issue

  ARUN SAS

• Ramesh Saive

  SSC-Insane

  Points: 24275

  More actions

  February 10, 2009 at 5:16 am

  #941102

  I don't think you have read the entire article, it clearly has an example of running totals with pre-available scripts. Please spare some time in reading the article & understanding the logic behind it. From that, you try to modify the query to suit your requirements and if in case you have problems in it, then we are here to help you on that.

  --Ramesh
  

  ---

• arun.sas

  SSChampion

  Points: 11831

  More actions

  February 10, 2009 at 8:15 pm

  #941775

  Hi,

  Thanks Ramesh,

  I already understood this concept,

  I too wrote this update statement, see the earily,

  All I need is how I solve this without using while.

  ARUN S.A.S

• Sergiy

  SSC Guru

  Points: 110208

  More actions

  February 10, 2009 at 8:54 pm

  #941782

  The soliution is in the article.

  And it does not use any loop.

  _____________
  Code for TallyGenerator

• arun.sas

  SSChampion

  Points: 11831

  More actions

  February 11, 2009 at 11:03 pm

  #942622

  Thanks Ramesh,

  I got the point

  ARUN SAS

• J-440512

  SSCertifiable

  Points: 6285

  More actions

  February 13, 2009 at 9:14 am

  #943713

  Arun,

  You did a great job by including the table creation script and insert statements to populate it.

  I have seen too many times a question asked without making the effort. You stand out amongst these.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 13, 2009 at 9:44 am

  #943750

  Here is a solution, based on the article everyone mentioned and is also listed below in my signature block.

  create clustered index IX_RunningBal on #temp (

  budget asc,

  slno asc

  )

  select * from #temp

  declare @bbal numeric(13,8),

  @bud Nchar(15);

  set @bud = '';

  update #temp set

  @bbal = [budget_balance] = case when @bud <> [budget] then [budget_amt] else @bbal end - [order_value],

  @bud = [budget]

  from

  #temp with (index(IX_RunningBal));

  select * from #temp

• arun.sas

  SSChampion

  Points: 11831

  More actions

  February 13, 2009 at 8:14 pm

  #944017

  Thanks J

  ARUN SAS

• arun.sas

  SSChampion

  Points: 11831

  More actions

  February 13, 2009 at 8:18 pm

  #944018

  Thanks Lynn Pettis

  but if i create the #temp with SLNO identity,then the clustered index realy needed?

  ARUN SAS

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  February 13, 2009 at 8:49 pm

  #944020

  arun.sas (2/13/2009)

  ---

  Thanks Lynn Pettis

  but if i create the #temp with SLNO identity,then the clustered index realy needed?

  ARUN SAS

  Absolutely. And, since you can't use an index by name in the from clause that is created in the same script, you need to modify Lynn's code as follows...

  update #temp set

  @bbal = [budget_balance] = case when @bud <> [budget] then [budget_amt] else @bbal end - [order_value],

  @bud = [budget]

  from

  #temp with (index(0));

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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 13, 2009 at 9:05 pm

  #944023

  Jeff Moden (2/13/2009)

  ---

  arun.sas (2/13/2009)

  ---

  Thanks Lynn Pettis

  but if i create the #temp with SLNO identity,then the clustered index realy needed?

  ARUN SAS

  Absolutely. And, since you can't use an index by name in the from clause that is created in the same script, you need to modify Lynn's code as follows...

  update #temp set

  @bbal = [budget_balance] = case when @bud <> [budget] then [budget_amt] else @bbal end - [order_value],

  @bud = [budget]

  from

  #temp with (index(0));

  I would not have known that, and not that I don't believe Jeff, I am going to have to test it out for myself. When I wrote the code earlier, I did it in pieces which may explain why it worked for me at the time.

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  February 13, 2009 at 9:09 pm

  #944024

  Jeff, I'm not sure if you are right or wrong still. I ran the following and everything ran fine. Can you suggest any other tests I may want to try?

  create table #temp

  (

  slno int,

  sale_order Nchar(15),

  order_value numeric(13,8),

  budget Nchar(15),

  budget_amt numeric(13,8),

  budget_balance numeric(13,8)

  );

  insert into #temp values

  (1,'ESA-01/08',150.00,'CAP-A/08',1500.00,0.00);

  insert into #temp values

  (2,'ESA-02/08',300.00,'CAP-A/08',1500.00,0.00);

  insert into #temp values

  (3,'ESA-05/08',450.00,'CAP-B/08',1500.00,0.00) ;

  insert into #temp values

  (4,'ESA-06/08',500.00,'CAP-B/08',1500.00,0.00);

  insert into #temp values

  (5,'ESA-07/08',600.00,'CAP-B/08',1500.00,0.00);

  insert into #temp values

  (6,'ESA-10/08',50.00,'CAP-C/08',1500.00,0.00);

  insert into #temp values

  (7,'ESA-11/08',150.00,'CAP-C/08',1500.00,0.00);

  insert into #temp values

  (8,'ESA-12/08',250.00,'CAP-C/08',1500.00,0.00);

  create clustered index IX_RunningBal on #temp (

  budget asc,

  slno asc

  );

  select * from #temp;

  declare @bbal numeric(13,8),

  @bud Nchar(15);

  set @bud = '';

  update #temp set

  @bbal = [budget_balance] = case when @bud <> [budget] then [budget_amt] else @bbal end - [order_value],

  @bud = [budget]

  from

  #temp with (index(IX_RunningBal));

  select * from #temp;

  drop table #temp;

• Jeff Moden

  SSC Guru

  Points: 1004405

  More actions

  February 14, 2009 at 12:15 pm

  #944136

  Lynn Pettis (2/13/2009)

  ---

  Jeff, I'm not sure if you are right or wrong still. I ran the following and everything ran fine. Can you suggest any other tests I may want to try?

  My bad, Lynn... the problem I mentioned only exists in versions prior to 2k5. I still do a lot of testing in 2k to make sure the solutions I provide still work there and here's the error you'll get if you run it in 2k...

  [font="Courier New"]Server: Msg 308, Level 16, State 1, Line 39

  Index 'IX_RunningBal' on table '#temp' (specified in the FROM clause) does not exist.[/font]

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