Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #1166

Need help to update table based on previous rows column values

Show all headers | View raw

Hi Experts,

I need help to etite one SQL query in 2008. I am having one table called CustomerData. This table is having 
rows as below



TransID		CustID		Qty		WrittenQty 	Date		RefilWritten		RefilLeft
1		123		84		90		01/01/2012	6.07			6.07
2		123		84		90		02/02/2012	6.07			NULL
3		123		84		90		03/01/2012	6.07			NULL
4		123		84		90		04/01/2012	6.07			NULL
5		123		84		90		05/01/2012	6.07			NULL
6		123		84		90		06/01/2012	6.07			NULL

7		222		28		112		01/01/2012	3.14			3.14
8		222		84		90		02/02/2012	3.14			NULL
9		222		84		90		03/01/2012	3.14			NULL
10		222		84		90		04/01/2012	3.14			NULL
11		222		84		90		05/01/2012	3.14			NULL
		222		84		90		06/01/2012	3.14			NULL


12		333		28		112		01/01/2012	11.0			11.0
13		333		84		90		02/02/2012	11.0			NULL
14		333		84		90		03/01/2012	11.0			NULL
15		333		84		90		04/01/2012	11.0			NULL
11		333		84		90		05/01/2012	11.0			NULL



Now I need to update above table for RefilLeft column values for each custID based on their previous refilLeft value with following formula

RefilLeft = Previous Date RefilLeft - (QTY/WrittenQty)

First month refil left will be always equals to RefilWritten.


Any help will be appricated.

Thanks

Back to comp.databases.ms-sqlserver | Previous | Next — Next in thread | Find similar

Thread

Need help to update table based on previous rows column values Dinesh <dinesht15@gmail.com> - 2012-07-09 11:21 -0700
  Re: Need help to update table based on previous rows column values Erland Sommarskog <esquel@sommarskog.se> - 2012-07-09 23:43 +0200
  Re: Need help to update table based on previous rows column values Dinesh <dinesht15@gmail.com> - 2012-07-12 23:27 -0700
    Re: Need help to update table based on previous rows column values Erland Sommarskog <esquel@sommarskog.se> - 2012-07-13 11:14 +0200

csiph-web