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

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

Path	csiph.com!usenet.pasdenom.info!gegeweb.org!eternal-september.org!feeder.eternal-september.org!mx04.eternal-september.org!.POSTED!not-for-mail
From	Erland Sommarskog <esquel@sommarskog.se>
Newsgroups	comp.databases.ms-sqlserver
Subject	Re: Need help to update table based on previous rows column values
Date	Mon, 09 Jul 2012 23:43:22 +0200
Organization	Erland Sommarskog
Lines	31
Message-ID	<XnsA08BF152D5411Yazorman@127.0.0.1> (permalink)
References	<f06f1d6e-7af7-476e-8808-2b1524163b0f@googlegroups.com>
Mime-Version	1.0
Content-Type	text/plain; charset=windows-1252
Content-Transfer-Encoding	8bit
Injection-Info	mx04.eternal-september.org; posting-host="nBFDv6s1VJQDuF1w6hpX2A"; logging-data="24354"; mail-complaints-to="abuse@eternal-september.org"; posting-account="U2FsdGVkX1+Pvj1mAlQ8I0dGPwwuq776"
User-Agent	Xnews/2006.08.24 Mime-proxy/2.1.c.0 (Win32)
Cancel-Lock	sha1:T5Ze7lW0hSJPXqzxbuebDmTkQgM=
Xref	csiph.com comp.databases.ms-sqlserver:1168

Show key headers only | View raw

Dinesh (dinesht15@gmail.com) writes:
> 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.
> 

This will give you the correct result eventually:

UPDATE tbl
SET    RefillLeft = (SELECT MIN(b.RefilLeft) - SUM(b.Qty/b.WrittenQty)
                     FROM   tbl b
                     WHERE  a.CustID = b.CustID
                       AND  b.Date < a.Date)
FROM  tbl a

However, performance will not be fantastic as it grows with the square of 
number rows per customer.

In SQL 2012 there are much more effecient ways to do this.


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

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