Author |
Topic |
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 04:31:32
|
I would like to SUM two fileds from different rows from the same table but I don't know how to do that.E.g.BillingTransactionsIndex CreateDate UserType UserIndex TransType Reference Total Balance2 6/5/2008 15:02 1 51 1 150 -288.2 -288.25 6/8/2008 11:55 1 51 1 157 -1.58674 -289.787In the table above I want SUM fields Total and Balance for the first row and the the next row SUM 2nd row Total with 1st row BalancePlease helpThanks |
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 04:40:10
|
[code]UPDATE c1SET c1.Total=c1.Total + c1.Balance + ISNULL(c2.PrevBalance,0)FROM YourTable c1OUTER 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[/code] |
|
|
ganeshkumar08
Posting Yak Master
187 Posts |
Posted - 2008-06-25 : 04:44:40
|
HelloIf you have fixed number of known columns, which you want to sum use below query.select Base.col1 + Child.col1 as 'MySum', Base.col2FromTable1 BaseInner Join Table1 Child On Base.col=Child.col |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 04:51:06
|
quote: Originally posted by ganeshkumar08 HelloIf you have fixed number of known columns, which you want to sum use below query.select Base.col1 + Child.col1 as 'MySum', Base.col2FromTable1 BaseInner Join Table2 Child On Base.col=Child.col
Didnt understand how you think this works? What are table1 & table2 here? |
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 04:59:54
|
Total Balancerow 1 90 90row 2 23I 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. |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 05:12:52
|
quote: Originally posted by tkotey Total Balancerow 1 90 90row 2 23I 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 Balancerow 1 90 90row 2 23row 3 15row 4 20do you want this? Total Balancerow 1 90 90row 2 23 90row 3 15 23row 4 20 15or this Total Balancerow 1 90 90row 2 23 90row 3 15 113row 4 20 128 |
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 05:32:16
|
I want thisTotal Balancerow 1 90 90row 2 23 113row 3 15 128row 4 20 148In 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 05:41:41
|
thayts exactly what this does:-UPDATE c1SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)FROM YourTable c1OUTER 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 |
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 05:55:29
|
quote: Originally posted by visakh16 thayts exactly what this does:-UPDATE c1SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)FROM YourTable c1OUTER 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 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 05:57:51
|
quote: Originally posted by tkotey
quote: Originally posted by visakh16 thayts exactly what this does:-UPDATE c1SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)FROM YourTable c1OUTER 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. |
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 06:10:41
|
quote: Originally posted by visakh16
quote: Originally posted by tkotey
quote: Originally posted by visakh16 thayts exactly what this does:-UPDATE c1SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)FROM YourTable c1OUTER 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 balancee.g.The result was:Total Balancerow 1 90 90row 2 23 23row 3 15 15row 4 20 20Instead of:Total Balancerow 1 90 90row 2 23 113row 3 15 128row 4 20 148 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 06:14:52
|
quote: Originally posted by tkotey
quote: Originally posted by visakh16
quote: Originally posted by tkotey
quote: Originally posted by visakh16 thayts exactly what this does:-UPDATE c1SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)FROM YourTable c1OUTER 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 balancee.g.The result was:Total Balancerow 1 90 90row 2 23 23row 3 15 15row 4 20 20Instead of:Total Balancerow 1 90 90row 2 23 113row 3 15 128row 4 20 148
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. |
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 06:23:51
|
quote: Originally posted by visakh16
quote: Originally posted by tkotey
quote: Originally posted by visakh16
quote: Originally posted by tkotey
quote: Originally posted by visakh16 thayts exactly what this does:-UPDATE c1SET c1.Balance=c1.Total + ISNULL(c2.PrevTotal,0)FROM YourTable c1OUTER 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 balancee.g.The result was:Total Balancerow 1 90 90row 2 23 23row 3 15 15row 4 20 20Instead of:Total Balancerow 1 90 90row 2 23 113row 3 15 128row 4 20 148
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 Balancerow 1 6/5/2008 9:45:24 AM 90 90row 2 6/6/2008 9:45:24 AM 23 113row 3 6/7/2008 9:45:24 AM 15 128row 4 6/8/2008 9:45:24 AM 20 148Below is the codeUPDATE BillingTransactionsSET BillingTransactions.Balance=BillingTransactions.Total + ISNULL(BillingTransactions_1.PrevBalance,0)FROM BillingTransactionsOUTER APPLY (SELECT SUM(Balance) AS PrevBalance FROM BillingTransactions WHERE UserIndex =BillingTransactions.UserIndex AND CreateDate = BillingTransactions.CreateDate AND BillingTransactionIndex<BillingTransactions.BillingTransactionIndex) BillingTransactions_1 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 06:34:02
|
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. |
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 07:30:58
|
This is the current look of the databaseIndex UserIndex CreateDate Total Balance1 23 6/5/2008 15:02 -199.98 02 23 6/7/2008 15:02 25.95 03 8 6/8/2008 15:02 -56.70 04 23 6/11/2008 13:00 -23.80 05 8 6/11/2008 16:00 10.00 0 And I want the calculation to be done in view. Basically calculate all the Totals for the same UserIndex between a StartDate and a EndDate. Expected results below.Index UserIndex CreateDate Total Balance1 23 6/5/2008 15:02 -199.98 -199.982 23 6/7/2008 15:02 25.95 -174.033 8 6/8/2008 15:02 -56.70 -56.704 23 6/11/2008 13:00 -23.80 197.835 8 6/11/2008 16:00 10.00 -46.70 Thank you so much for help |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 07:46:43
|
[code]UPDATE bSET b.Balance=b.Total + ISNULL(c.PrevTotal,0)FROM BillingTransactions bOUTER APPLY (SELECT SUM(Total) AS PrevTotal FROM BillingTransactions WHERE UserIndex =b.UserIndex AND Index<b.Index) c[/code] |
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 08:00:20
|
quote: Originally posted by visakh16
UPDATE bSET b.Balance=b.Total + ISNULL(c.PrevTotal,0)FROM BillingTransactions bOUTER 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) |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 08:13:24
|
quote: Originally posted by tkotey
quote: Originally posted by visakh16
UPDATE bSET b.Balance=b.Total + ISNULL(c.PrevTotal,0)FROM BillingTransactions bOUTER 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. |
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 08:16:44
|
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 beforeData for UserIndex 53Record Date Credit (BWP) Debit (BWP) BalanceInvoice # 151 06/05/2008 288.20 BWP -288.21Payment # 2 06/10/2008 1,000.00 BWP 711.79Invoice # 158 06/10/2008 13.20 BWP 698.59Invoice # 160 06/12/2008 148.44 BWP 550.15Invoice # 165 06/16/2008 304.60 BWP 245.55Invoice # 181 06/17/2008 12,040.14 BWP -11,794.58Payment # 4 06/17/2008 300.00 BWP -11,494.58Invoice # 185 06/18/2008 39,688.18 BWP -51,182.76Invoice # 188 06/19/2008 112,987.20 BWP -164,169.96Invoice # 191 06/20/2008 14,679.63 BWP -178,849.59Payment # 1 06/20/2008 60.00 BWP -178,789.59Invoice # 194 06/21/2008 55,195.62 BWP -233,985.22Invoice # 197 06/22/2008 9,790.16 BWP -243,775.38Invoice # 201 06/23/2008 254.10 BWP -244,029.47Payment # 2 06/23/2008 78.00 BWP -243,951.47Payment # 4 06/23/2008 100.00 BWP -243,851.47Payment # 5 06/23/2008 30.00 BWP -243,821.47Payment # 6 06/23/2008 40.00 BWP -243,781.47Payment # 7 06/23/2008 57.00 BWP -243,724.47Payment # 8 06/23/2008 45.00 BWP -243,679.47 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 UTypeIndex CreateDate UType UserIndex TransType Total Balance3 2008-06-05 15:02:49.000 1 53 1 151 -288.2 06 2008-06-10 08:59:10.000 1 53 2 2 1000 07 2008-06-10 08:59:23.000 1 53 1 158 -13.2 09 2008-06-12 11:42:46.000 1 53 1 160 -148.439793 011 2008-06-16 03:00:00.000 1 53 1 165 -304.598546 013 2008-06-17 03:00:00.000 1 53 1 181 -12040.136172 018 2008-06-18 03:00:00.000 1 53 1 185 -39688.180278 021 2008-06-19 03:00:00.000 1 53 1 188 -112987.200625 024 2008-06-20 03:00:00.000 1 53 1 191 -14679.635 026 2008-06-20 18:20:12.000 1 53 2 1 60 028 2008-06-21 03:00:00.000 1 53 1 194 -55195.615937 031 2008-06-22 03:00:00.000 1 53 1 197 -9790.15872 034 2008-06-23 03:00:00.000 1 53 1 201 -254.1 036 2008-06-23 08:11:15.000 1 53 2 2 78 039 2008-06-23 10:08:18.000 1 53 2 5 30 040 2008-06-23 10:49:11.000 1 53 2 6 40 041 2008-06-23 10:54:45.000 1 53 2 7 57 042 2008-06-23 11:22:12.000 1 53 2 8 45 043 2008-06-23 11:23:55.000 1 53 2 9 50 046 2008-06-24 03:00:00.000 1 53 1 205 -2519.267803 049 2008-06-25 03:00:00.000 1 53 1 208 -34090.76025 015 2008-06-17 15:47:13.000 1 53 2 4 300 038 2008-06-23 09:14:58.000 1 53 2 4 100 044 2008-06-23 13:24:31.000 1 53 2 10 243679.47 0 |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-06-25 : 08:23:07
|
What does Transtype designate? |
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 2008-06-25 : 08:40:26
|
quote: Originally posted by visakh16 What does Transtype designate?
TransType designate a credit/payment(2) or debit/invoice(1)This is the result below when I run your statementIndex CreateDate UserIndex Total Balance3 2008-06-05 15:02:49.000 53 -288.2 -288.26 2008-06-10 08:59:10.000 53 1000 10007 2008-06-10 08:59:23.000 53 -13.2 -13.29 2008-06-12 11:42:46.000 53 -148.439793 -148.43979311 2008-06-16 03:00:00.000 53 -304.598546 -304.59854613 2008-06-17 03:00:00.000 53 -12040.136172 -12040.13617218 2008-06-18 03:00:00.000 53 -39688.180278 -39688.18027821 2008-06-19 03:00:00.000 53 -112987.200625 -112987.20062524 2008-06-20 03:00:00.000 53 -14679.635 -14679.63526 2008-06-20 18:20:12.000 53 60 6028 2008-06-21 03:00:00.000 53 -55195.615937 -55195.61593731 2008-06-22 03:00:00.000 53 -9790.15872 -9790.1587234 2008-06-23 03:00:00.000 53 -254.1 -254.136 2008-06-23 08:11:15.000 53 78 7839 2008-06-23 10:08:18.000 53 30 3040 2008-06-23 10:49:11.000 53 40 4041 2008-06-23 10:54:45.000 53 57 5742 2008-06-23 11:22:12.000 53 45 4543 2008-06-23 11:23:55.000 53 50 5046 2008-06-24 03:00:00.000 53 -2519.267803 -2519.26780349 2008-06-25 03:00:00.000 53 -34090.76025 -34090.7602515 2008-06-17 15:47:13.000 53 300 30038 2008-06-23 09:14:58.000 53 100 10044 2008-06-23 13:24:31.000 53 243679.47 243679.47 |
|
|
Next Page
|