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

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

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

Show all headers | 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