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

Re: Need help to update table based on previous rows column values

From	Dinesh <dinesht15@gmail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Need help to update table based on previous rows column values
Date	2012-07-12 23:27 -0700
Organization	http://groups.google.com
Message-ID	<ef77549d-59a5-4a3a-8778-519fb3b11277@googlegroups.com> (permalink)
References	<f06f1d6e-7af7-476e-8808-2b1524163b0f@googlegroups.com>

Show all headers | View raw

On Monday, July 9, 2012 11:51:48 PM UTC+5:30, Dinesh wrote:
> 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

HI,

Thanks for the reply. It is working but having one issue. It is setting first row value for each CUstID as NULL which should not happen. First row (Lowest date) for each customer should have RefilWritten.

Thanks

Back to comp.databases.ms-sqlserver | Previous | Next — Previous in thread | 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