Runnning Balance

• Francis Lledo-195398

  Grasshopper

  Points: 11

  More actions

  February 17, 2005 at 2:19 am

  #163354

  has anyone been able to create a script that calculates a running balance?

  attached are data samples desired results are in red

  pls help

   

  TEMP_ID	INV_NUM	PAY_NUM	PAY_DATE	PAY_AMT	INV_PMT	RESULT
  1	17542	306322	8/6/99 0:00	375000.00	111375.00	263625.00
  2	17542	307517	11/24/99 0:00	375000.00	21656.25	241968.75
  3	17542	307739	12/21/99 0:00	375000.00	21656.25	220312.50
  4	17542	309649	6/9/00 0:00	375000.00	21656.25	198656.25
  5	17542	309784	6/23/00 0:00	375000.00	21656.25	177000.00
  6	17542	310775	9/4/00 0:00	375000.00	43312.50	133687.50
  7	17542	310996	9/22/00 0:00	375000.00	21656.25	112031.25
  8	17542	311330	10/18/00 0:00	375000.00	21656.25	90375.00
  9	17542	311959	12/11/00 0:00	375000.00	21656.25	68718.75
  10	17542	312408	1/26/01 0:00	375000.00	21656.25	47062.50
  11	17542	312853	3/8/01 0:00	375000.00	21656.25	25406.25
  12	17542	313014	3/26/01 0:00	375000.00	21656.25	3750.00
  13	17542	313338	4/27/01 0:00	375000.00	3750.00	0.00
  14	17543	306322	8/6/99 0:00	375000.00	111375.00	263625.00
  15	17543	307517	11/24/99 0:00	375000.00	21656.25	241968.75
  16	17543	307739	12/21/99 0:00	375000.00	21656.25	220312.50
  17	17543	309649	6/9/00 0:00	375000.00	21656.25	198656.25
  18	17543	309784	6/23/00 0:00	375000.00	21656.25	177000.00
  19	17543	310775	9/4/00 0:00	375000.00	43312.50	133687.50
  20	17543	310996	9/22/00 0:00	375000.00	21656.25	112031.25
  21	17543	311330	10/18/00 0:00	375000.00	21656.25	90375.00
  22	17543	311959	12/11/00 0:00	375000.00	21656.25	68718.75
  23	17543	312408	1/26/01 0:00	375000.00	21656.25	47062.50
  24	17543	312853	3/8/01 0:00	375000.00	21656.25	25406.25
  25	17543	313014	3/26/01 0:00	375000.00	21656.25	3750.00
  26	17543	313338	4/27/01 0:00	375000.00	3750.00	0.00

   

• Dinesh Asanka

  SSChampion

  Points: 11058

  More actions

  February 17, 2005 at 2:55 am

  #542746

  select id,amount, amount +(select  sum(amount)

                 from ac_audit as b

                 where b.id<a.id)

  from ac_audit as a order by id

  match your fields name to this

  
  
  
  

  My Blog: http://dineshasanka.spaces.live.com/

• Dinesh Asanka

  SSChampion

  Points: 11058

  More actions

  February 17, 2005 at 2:57 am

  #542748

  select id,amount, (select amount + sum(amount)

                 from ac_audit as b

                 where b.id<a.id)

  from ac_audit as a order by id

  
  
  
  

  My Blog: http://dineshasanka.spaces.live.com/

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 17, 2005 at 2:58 am

  #542750

  This might serve as a start.

  SET NOCOUNT ON

  IF OBJECT_ID('lfdsum_t') IS NOT NULL

       DROP TABLE lfdsum_t

  GO

  CREATE TABLE lfdsum_t

  (

   id int identity

   , pay_amt decimal(8,2)

   , inv_pmt decimal(8,2)

  )

  INSERT INTO lfdsum_t values (375000,111375);

  INSERT INTO lfdsum_t values (375000,21656.25);

  INSERT INTO lfdsum_t values (375000,21656.25);

  INSERT INTO lfdsum_t values (375000,21656.25);

  INSERT INTO lfdsum_t values (375000,21656.25);

  INSERT INTO lfdsum_t values (375000,21656.25);

  SELECT

   a.id, a.pay_amt-

       (SELECT

            SUM(b.inv_pmt)

       FROM

            lfdsum_t b

  WHERE

            a.id > b.id) lfd_Sum

   

  FROM

       lfdsum_t a

  DROP TABLE lfdsum_t

  SET NOCOUNT OFF

  id          lfd_Sum                                 

  ----------- ----------------------------------------

  1           NULL

  2           263625.00

  3           241968.75

  4           220312.50

  5           198656.25

  6           177000.00

  Aargh, being a bit brain-dead today. Now, I just can't figure out right now how to get rid of this 45&!@!"§ NULL

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 17, 2005 at 2:58 am

  #542749

  Dinesh, what did you do here?

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 17, 2005 at 3:02 am

  #542752

  Stupid me!!!

  SET NOCOUNT ON

  IF OBJECT_ID('lfdsum_t') IS NOT NULL

       DROP TABLE lfdsum_t

  GO

  CREATE TABLE lfdsum_t

  (

   id int identity

   , pay_amt decimal(8,2)

   , inv_pmt decimal(8,2)

  )

  INSERT INTO lfdsum_t values (375000,111375);

  INSERT INTO lfdsum_t values (375000,21656.25);

  INSERT INTO lfdsum_t values (375000,21656.25);

  INSERT INTO lfdsum_t values (375000,21656.25);

  INSERT INTO lfdsum_t values (375000,21656.25);

  INSERT INTO lfdsum_t values (375000,21656.25);

  SELECT

   a.id, a.pay_amt-

       (SELECT

            SUM(b.inv_pmt)

       FROM

            lfdsum_t b

  WHERE

            a.id >= b.id) lfd_Sum

   

  FROM

       lfdsum_t a

  DROP TABLE lfdsum_t

  SET NOCOUNT OFF

  id          lfd_Sum                                 

  ----------- ----------------------------------------

  1           263625.00

  2           241968.75

  3           220312.50

  4           198656.25

  5           177000.00

  6           155343.75

  should do the trick.

  Now I get some more java to wake up

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]

• Dinesh Asanka

  SSChampion

  Points: 11058

  More actions

  February 17, 2005 at 3:08 am

  #542753

  I don't know. I just press the quote button

  
  
  
  

  My Blog: http://dineshasanka.spaces.live.com/

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  February 17, 2005 at 3:10 am

  #542754

  Test?

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]