Please start any new threads on our new
site at http://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.

Our new SQL Server Forums are live!
Come on over! We've restricted the ability to create new threads on these forums.

UPDATE c1
SET c1.Total=c1.Total + c1.Balance + ISNULL(c2.PrevBalance,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Balance) AS PrevBalance
FROM YourTable
WHERE UserType =c1.UserType
AND UserIndex =c1.UserIndex
AND TransType = c1.TransType
AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2

I want to Insert the Balance for row 2 by adding the Total field of row2 with the Balance of row1. I don't want insert a new field. Sorry I am a newbie trying to understands the statements above. Thanks for your help.

I want to Insert the Balance for row 2 by adding the Total field of row2 with the Balance of row1. I don't want insert a new field. Sorry I am a newbie trying to understands the statements above. Thanks for your help.

what happend if you have more than two rows for same set of UserType UserIndex TransType values Total Balance row 1 90 90 row 2 23 row 3 15 row 4 20

do you want this? Total Balance row 1 90 90 row 2 23 90 row 3 15 23 row 4 20 15

In row 2 to get the balance I SUM Balance(90) of row 1 with Total(23) of row 2 to get Balance (113) of row 2. Same thing is done for the next row by adding with Balance of previous row

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
FROM YourTable
WHERE UserType =c1.UserType
AND UserIndex =c1.UserIndex
AND TransType = c1.TransType
AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
FROM YourTable
WHERE UserType =c1.UserType
AND UserIndex =c1.UserIndex
AND TransType = c1.TransType
AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2

In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
FROM YourTable
WHERE UserType =c1.UserType
AND UserIndex =c1.UserIndex
AND TransType = c1.TransType
AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2

In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1

c1 and c2 are same table. i've just used two aliases for it. just replace YourTable with your actual table name and see.

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
FROM YourTable
WHERE UserType =c1.UserType
AND UserIndex =c1.UserIndex
AND TransType = c1.TransType
AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2

In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1

c1 and c2 are same table. i've just used two aliases for it. just replace YourTable with your actual table name and see.

Oh OK. I tried the statement but all it did was to duplicate the totals into the balance e.g.

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
FROM YourTable
WHERE UserType =c1.UserType
AND UserIndex =c1.UserIndex
AND TransType = c1.TransType
AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2

In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1

c1 and c2 are same table. i've just used two aliases for it. just replace YourTable with your actual table name and see.

Oh OK. I tried the statement but all it did was to duplicate the totals into the balance e.g.

Are you sure you put correct column names inside the WHERE condition. I just put them as seen from your sample data so i'm not sure its same you want.My assumption was that your all rows in a group will have same UserType,UserIndex & TransType with only difference in BillingTransactionsIndex value. if this is not correct, you need replace the WHERE condition with your actual rule.

UPDATE c1
SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)
FROM YourTable c1
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
FROM YourTable
WHERE UserType =c1.UserType
AND UserIndex =c1.UserIndex
AND TransType = c1.TransType
AND BillingTransactionsIndex<c1.BillingTransactionsIndex) c2

In your statement c1 and c2 are two different tables. May you please give me a statement that will work on the same table or one table e.g c1

c1 and c2 are same table. i've just used two aliases for it. just replace YourTable with your actual table name and see.

Oh OK. I tried the statement but all it did was to duplicate the totals into the balance e.g.

Are you sure you put correct column names inside the WHERE condition. I just put them as seen from your sample data so i'm not sure its same you want.My assumption was that your all rows in a group will have same UserType,UserIndex & TransType with only difference in BillingTransactionsIndex value. if this is not correct, you need replace the WHERE condition with your actual rule.

Here's a new sample data. I have included date

Date Total Balance row 1 6/5/2008 9:45:24 AM 90 90 row 2 6/6/2008 9:45:24 AM 23 113 row 3 6/7/2008 9:45:24 AM 15 128 row 4 6/8/2008 9:45:24 AM 20 148

Below is the code

UPDATE BillingTransactions
SET BillingTransactions.Balance=BillingTransactions.Total + ISNULL(BillingTransactions_1.PrevBalance,0)
FROM BillingTransactions
OUTER APPLY (SELECT SUM(Balance) AS PrevBalance
FROM BillingTransactions
WHERE UserIndex =BillingTransactions.UserIndex
AND CreateDate = BillingTransactions.CreateDate
AND BillingTransactionIndex<BillingTransactions.BillingTransactionIndex) BillingTransactions_1

How can i know what are values for UserIndex,CreateDate and other fields? either post it or you rewrite the conditions as per your requirement.I assumed the WHERE conditions from your first sample data so i'm not sure that will be the one you want. Please post clearly how will your data be & what will be rules otherwise you're really making it hard for someone trying to help you as we dont have access to your tabes and dont know how will be your data.

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevTotal,0)
FROM BillingTransactions b
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
FROM BillingTransactions
WHERE UserIndex =b.UserIndex
AND Index<b.Index) c

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevTotal,0)
FROM BillingTransactions b
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
FROM BillingTransactions
WHERE UserIndex =b.UserIndex
AND Index<b.Index) c

UPDATE b
SET b.Balance=b.Total + ISNULL(c.PrevTotal,0)
FROM BillingTransactions b
OUTER APPLY (SELECT SUM(Total) AS PrevTotal
FROM BillingTransactions
WHERE UserIndex =b.UserIndex
AND Index<b.Index) c

What is the 0 for in (c.PrevTotal,0)

it looks if there are any records coming before current for same UserIndex and in cases where none exists it returns a NULL. the isnull converts it to 0 and adds it to Total. this happens only for first occurance of UserIndex value.

I am trying to reproduce the data below. My table does not have the Balance and I am trying to add it then do the calculations. I tried your statement and still gave me the same result as before

Right now my table is like this below. The credit and debit are on the same field which is Total. The balance is 0 throughout. Don't worry about the UType