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

quote:

---

Originally posted by visakh16

SELECT b.Total + ISNULL(c.PrevBalance,0) AS Balance,otherfields
FROM BillingTransactions2 b
OUTER APPLY (SELECT SUM(Total) AS PrevBalance
             FROM BillingTransactions2
             WHERE UserIndex =b.UserIndex
             AND BillingTransactionIndex<b.BillingTransactionIndex) c

---

SELECT DISTINCT TOP (100) PERCENT
                          (SELECT     COUNT(*) AS Expr1
                            FROM          dbo.BillsSections
                            WHERE      (BillIndex = a.BillIndex) AND (Type <> 7) AND (BillSectionIndex > a.BillSectionIndex)) AS IsLast,
                          (SELECT     SUM(Charge) AS Expr1
                            FROM          dbo.BillsSections AS BillsSections_2
                            WHERE      (BillIndex = a.BillIndex)) AS Sums,
                          (SELECT     SUM(Charge) AS Expr1
                            FROM          dbo.BillsSections AS BillsSections_1
                            WHERE      (BillIndex = a.BillIndex) AND (Type = 7)) AS Tax, a.BillIndex, a.BillSectionIndex, a.Type, a.Charge, a.Info1, a.Info2, a.Info3, a.Remark, 
                      b.BillStartDate, b.BillEndDate, b.CreateDate AS BillCreateDate, c.UserID, c.GroupName, c.UserPaymentBalance, d.UserIndex, d.FirstName, 
                      d.MiddleName, d.LastName, d.Company, d.Address1, d.Address2, d.City, d.State, d.Country, d.Zip, d.PhoneHome, d.PhoneWork, d.PhoneFax, d.Email, 
                      d.CreateDate, d.LastModify, d.NewUser, d.LastCharge, d.TotalCharge, d.CustomInfo4, d.Comments, e.ServiceName, 
                      dbo.AccountTypes.AccountName
FROM         dbo.AccountTypes INNER JOIN
                      dbo.Users AS c ON dbo.AccountTypes.AccountIndex = c.AccountIndex RIGHT OUTER JOIN
                      dbo.Bills AS b RIGHT OUTER JOIN
                      dbo.BillsSections AS a ON b.BillIndex = a.BillIndex ON c.UserIndex = b.UserIndex LEFT OUTER JOIN
                      dbo.UserDetails AS d ON c.UserIndex = d.UserIndex LEFT OUTER JOIN
                      dbo.Services AS e ON a.Info1 = e.ServiceIndex
WHERE     (b.BillType = 1) AND (a.Type <> 7)
ORDER BY a.BillIndex, a.BillSectionIndex

quote:

---

Originally posted by visakh16

how do you want to format your result set?

---

quote:

---

Originally posted by visakh16

How can you relate the current query to the one i gave? do you have a field in Biling Transactions which can be linked on current query?

---

quote:

---

Originally posted by tkotey

New errors below

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'INNER'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near 'tmp'.

---

quote:

---

Originally posted by visakh16

quote:

---

Originally posted by tkotey

New errors below

Msg 156, Level 15, State 1, Line 14
Incorrect syntax near the keyword 'INNER'.
Msg 102, Level 15, State 1, Line 26
Incorrect syntax near 'tmp'.

---

i've missed a space b/w field name and from . Are you sure you've included it?

---

quote:

---

Originally posted by tkotey

Yes it works. Thanks visakh16. Your code did the trick. Do you know any place on the net or books I can buy to increase my SQL skills?

---