|
tkotey
Yak Posting Veteran
75 Posts |
 Posted - 2008-07-08 : 05:35:47
|
| [code]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[/code]I will like to add the above statement to the one below[code]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.AccountNameFROM 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.ServiceIndexWHERE (b.BillType = 1) AND (a.Type <> 7)ORDER BY a.BillIndex, a.BillSectionIndex[/code]The balance should show at the end in the outputThanks |
|
Need help with view query