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

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

Path	csiph.com!v102.xanadu-bbs.net!xanadu-bbs.net!news.glorb.com!postnews.google.com!glegroupsg2000goo.googlegroups.com!not-for-mail
From	Dinesh <dinesht15@gmail.com>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Need help to update table based on previous rows column values
Date	Thu, 12 Jul 2012 23:27:18 -0700 (PDT)
Organization	http://groups.google.com
Lines	48
Message-ID	<ef77549d-59a5-4a3a-8778-519fb3b11277@googlegroups.com> (permalink)
References	<f06f1d6e-7af7-476e-8808-2b1524163b0f@googlegroups.com>
NNTP-Posting-Host	203.123.184.124
Mime-Version	1.0
Content-Type	text/plain; charset=ISO-8859-1
X-Trace	posting.google.com 1342162534 10070 127.0.0.1 (13 Jul 2012 06:55:34 GMT)
X-Complaints-To	groups-abuse@google.com
NNTP-Posting-Date	Fri, 13 Jul 2012 06:55:34 +0000 (UTC)
In-Reply-To	<f06f1d6e-7af7-476e-8808-2b1524163b0f@googlegroups.com>
Complaints-To	groups-abuse@google.com
Injection-Info	glegroupsg2000goo.googlegroups.com; posting-host=203.123.184.124; posting-account=c-1-ygoAAAC0kYqwlHCH29-kttFWYQF3
User-Agent	G2/1.0
Xref	csiph.com comp.databases.ms-sqlserver:1172

Show key headers only | 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