Help in Getting Closing Balance from Opening balance

• GuruGPrasad

  SSCommitted

  Points: 1768

  More actions

  April 21, 2012 at 12:59 am

  #255227

  Hi all,

  create table Opening([Date] date,item_id int,location_id int,qty int)

  Insert into Opening

  Select '14-Mar-2012',75,36,1

  Union

  select '14-Mar-2012',75,43,1

  Union

  select '29-Mar-2012',75,43,3

  Union

  select '30-Mar-2012',75,43,7

  Union

  select '02-Apr-2012',75,43,8

  Union

  select '03-Apr-2012',75,43,10

  Union

  select '04-Apr-2012',75,43,12

  Union

  select '14-Apr-2012',75,43,11

  Union

  select '04-Apr-2012',75,80,4

  Union

  select '04-Apr-2012',75,102,2

  Union

  select '04-Apr-2012',76,8,5001

  Union

  select '20-Apr-2012',76,8,4996

  Union

  select '04-Apr-2012',76,35,3170

  Union

  select '03-Apr-2012',76,37,25

  Union

  select '14-Mar-2012',76,39,5000

  Union

  select '02-Apr-2012',76,39,5001

  Union

  select '15-Mar-2012',76,43,6

  Union

  select '31-Mar-2012',76,43,139

  Union

  select '02-Apr-2012',76,43,140

  Union

  select '03-Apr-2012',76,43,147

  Union

  select '04-Apr-2012',76,43,144

  Union

  select '05-Apr-2012',76,43,345

  Union

  select '10-Apr-2012',76,43,355

  Union

  select '14-Mar-2012',76,102,25

  Union

  select '28-Mar-2012',76,102,25

  Union

  select '30-Mar-2012',76,102,50

  Union

  select '02-Apr-2012',76,102,51

  Union

  select '03-Apr-2012',76,102,176

  Union

  select '04-Apr-2012',76,102,8

  Union

  select '03-Apr-2012',76,1Apr,5

  Union

  select '04-Apr-2012',76,1Apr,10

  Union

  select '14-Mar-2012',102,8,1

  Union

  select '02-Apr-2012',107,43,141

  Union

  select '09-Apr-2012',115,43,351

  Union

  select '20-Apr-2012',124,102,6508

  Union

  select '21-Apr-2012',124,102,7408

  Union

  select '22-Apr-2012',124,102,8658

  select * from Opening order by item_id ,location_id,Date

  I need to get the closing balance from the above table.

  Sample out I had attached.

  Next day Opening will be the current date closing balance.

  Date Item Location Opening Closing

  14-03-2012 75 36 11

  14-03-2012 75 43 13

  29-03-2012 75 43 37

  Thanks In advance

  Regards

  Guru

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  April 21, 2012 at 2:40 am

  #1477020

  Here is what you are trying to accomplish:

  create table Opening([Date] date,item_id int,location_id int,qty int)

  Insert into Opening

  Select '14-Mar-2012', 75 ,36 ,1

  Union

  select '14-Mar-2012', 75, 43, 1

  Union

  select '29-Mar-2012' ,75, 43, 3

  Union

  select '30-Mar-2012', 75, 43 ,7

  Union

  select '02-Apr-2012', 75, 43, 8

  Union

  select '03-Apr-2012' ,75 ,43 ,10

  Union

  select '04-Apr-2012' ,75, 43 ,12

  Union

  select '14-Apr-2012' ,75, 43, 11

  Union

  select '04-Apr-2012' ,75, 80, 4

  Union

  select '04-Apr-2012' ,75, 102, 2

  Union

  select '04-Apr-2012' ,76 ,8 ,5001

  Union

  select '20-Apr-2012' ,76 ,8, 4996

  Union

  select '04-Apr-2012', 76, 35 ,3170

  Union

  select '03-Apr-2012' ,76 ,37 ,25

  Union

  select '14-Mar-2012' ,76 ,39 ,5000

  Union

  select '02-Apr-2012' ,76 ,39 ,5001

  Union

  select '15-Mar-2012' ,76 ,43 ,6

  Union

  select '31-Mar-2012' ,76, 43, 139

  Union

  select '02-Apr-2012' ,76 ,43, 140

  Union

  select '03-Apr-2012' ,76 ,43, 147

  Union

  select '04-Apr-2012' ,76, 43 ,144

  Union

  select '05-Apr-2012' ,76, 43, 345

  Union

  select '10-Apr-2012' ,76, 43, 355

  Union

  select '14-Mar-2012' ,76, 102, 25

  Union

  select '28-Mar-2012' ,76 ,102, 25

  Union

  select '30-Mar-2012' ,76 ,102, 50

  Union

  select '02-Apr-2012' ,76, 102, 51

  Union

  select '03-Apr-2012' ,76, 102 ,176

  Union

  select '04-Apr-2012' ,76, 102 ,8

  Union

  select '03-Apr-2012' ,76, 1Apr ,5

  Union

  select '04-Apr-2012' ,76, 1Apr ,10

  Union

  select '14-Mar-2012' ,102, 8 ,1

  Union

  select '02-Apr-2012' ,107, 43 ,141

  Union

  select '09-Apr-2012' ,115, 43 ,351

  Union

  select '20-Apr-2012' ,124, 102 ,6508

  Union

  select '21-Apr-2012' ,124, 102 ,7408

  Union

  select '22-Apr-2012' ,124, 102 ,8658;

  with BaseData as (

  select

  ROW_NUMBER() over (partition by item_id, location_id order by Date) RowNum,

  Date,

  item_id,

  location_id,

  qty

  from

  Opening

  )

  select

  bd1.Date as Date,

  bd1.item_id,

  bd1.location_id,

  bd1.qty as Opening,

  ISNULL(bd2.qty,bd1.qty) as Closing

  from

  BaseData bd1

  left outer join BaseData bd2

  on (bd1.item_id = bd2.item_id

  and bd1.location_id = bd2.location_id

  and bd1.RowNum = bd2.RowNum - 1)

  ;

  drop table Opening;

• vinu512

  SSCoach

  Points: 15729

  More actions

  April 23, 2012 at 1:04 am

  #1477315

  very nice one Lynn.

  Couldn't have done it better.

  Vinu Vijayan

  For better and faster solutions please check..."How to post data/code on a forum to get the best help" - Jeff Moden[/url] 😉