| Author |
 Topic  |
|
tkotey
Yak Posting Veteran
75 Posts |
 Posted - 06/25/2008 : 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 Balance
2 6/5/2008 15:02 1 51 1 150 -288.2 -288.2
5 6/8/2008 11:55 1 51 1 157 -1.58674 -289.787
In 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 Balance
Please help
Thanks
|
Edited by - tkotey on 06/25/2008 04:31:57
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 04:40:10
|
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
|
 |
|
|
ganeshkumar08
Posting Yak Master
India
187 Posts |
Posted - 06/25/2008 : 04:44:40
|
Hello
If you have fixed number of known columns, which you want to sum use below query.
select Base.col1 + Child.col1 as 'MySum', Base.col2
From
Table1 Base
Inner Join Table1 Child On Base.col=Child.col
|
Edited by - ganeshkumar08 on 06/25/2008 05:05:05 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 04:51:06
|
quote:
Originally posted by ganeshkumar08
Hello
If you have fixed number of known columns, which you want to sum use below query.
select Base.col1 + Child.col1 as 'MySum', Base.col2
From
Table1 Base
Inner 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 - 06/25/2008 : 04:59:54
|
Total Balance
row 1 90 90
row 2 23
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. |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 05:12:52
|
quote:
Originally posted by tkotey
Total Balance
row 1 90 90
row 2 23
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
or this
Total Balance
row 1 90 90
row 2 23 90
row 3 15 113
row 4 20 128
|
|
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 06/25/2008 : 05:32:16
|
I want this
Total Balance
row 1 90 90
row 2 23 113
row 3 15 128
row 4 20 148
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 |
Edited by - tkotey on 06/25/2008 05:35:32 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 05:41:41
|
thayts exactly what this does:-
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 |
|
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 06/25/2008 : 05:55:29
|
quote:
Originally posted by visakh16
thayts exactly what this does:-
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 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 05:57:51
|
quote:
Originally posted by tkotey
quote:
Originally posted by visakh16
thayts exactly what this does:-
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. |
|
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 06/25/2008 : 06:10:41
|
quote:
Originally posted by visakh16
quote:
Originally posted by tkotey
quote:
Originally posted by visakh16
thayts exactly what this does:-
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.
The result was:
Total Balance
row 1 90 90
row 2 23 23
row 3 15 15
row 4 20 20
Instead of:
Total Balance
row 1 90 90
row 2 23 113
row 3 15 128
row 4 20 148
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 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 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.
The result was:
Total Balance
row 1 90 90
row 2 23 23
row 3 15 15
row 4 20 20
Instead of:
Total Balance
row 1 90 90
row 2 23 113
row 3 15 128
row 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. |
Edited by - visakh16 on 06/25/2008 06:17:29 |
|
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 06/25/2008 : 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 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.
The result was:
Total Balance
row 1 90 90
row 2 23 23
row 3 15 15
row 4 20 20
Instead of:
Total Balance
row 1 90 90
row 2 23 113
row 3 15 128
row 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 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 |
Edited by - tkotey on 06/25/2008 06:27:34 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 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 - 06/25/2008 : 07:30:58
|
This is the current look of the database
Index UserIndex CreateDate Total Balance
1 23 6/5/2008 15:02 -199.98 0
2 23 6/7/2008 15:02 25.95 0
3 8 6/8/2008 15:02 -56.70 0
4 23 6/11/2008 13:00 -23.80 0
5 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 Balance
1 23 6/5/2008 15:02 -199.98 -199.98
2 23 6/7/2008 15:02 25.95 -174.03
3 8 6/8/2008 15:02 -56.70 -56.70
4 23 6/11/2008 13:00 -23.80 197.83
5 8 6/11/2008 16:00 10.00 -46.70
Thank you so much for help
|
Edited by - tkotey on 06/25/2008 07:32:07 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 07:46:43
|
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 |
|
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 06/25/2008 : 08:00:20
|
quote:
Originally posted by visakh16
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)
|
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 08:13:24
|
quote:
Originally posted by tkotey
quote:
Originally posted by visakh16
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. |
|
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 06/25/2008 : 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 before
Data for UserIndex 53
Record Date Credit (BWP) Debit (BWP) Balance
Invoice # 151 06/05/2008 288.20 BWP -288.21
Payment # 2 06/10/2008 1,000.00 BWP 711.79
Invoice # 158 06/10/2008 13.20 BWP 698.59
Invoice # 160 06/12/2008 148.44 BWP 550.15
Invoice # 165 06/16/2008 304.60 BWP 245.55
Invoice # 181 06/17/2008 12,040.14 BWP -11,794.58
Payment # 4 06/17/2008 300.00 BWP -11,494.58
Invoice # 185 06/18/2008 39,688.18 BWP -51,182.76
Invoice # 188 06/19/2008 112,987.20 BWP -164,169.96
Invoice # 191 06/20/2008 14,679.63 BWP -178,849.59
Payment # 1 06/20/2008 60.00 BWP -178,789.59
Invoice # 194 06/21/2008 55,195.62 BWP -233,985.22
Invoice # 197 06/22/2008 9,790.16 BWP -243,775.38
Invoice # 201 06/23/2008 254.10 BWP -244,029.47
Payment # 2 06/23/2008 78.00 BWP -243,951.47
Payment # 4 06/23/2008 100.00 BWP -243,851.47
Payment # 5 06/23/2008 30.00 BWP -243,821.47
Payment # 6 06/23/2008 40.00 BWP -243,781.47
Payment # 7 06/23/2008 57.00 BWP -243,724.47
Payment # 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 UType
Index CreateDate UType UserIndex TransType Total Balance
3 2008-06-05 15:02:49.000 1 53 1 151 -288.2 0
6 2008-06-10 08:59:10.000 1 53 2 2 1000 0
7 2008-06-10 08:59:23.000 1 53 1 158 -13.2 0
9 2008-06-12 11:42:46.000 1 53 1 160 -148.439793 0
11 2008-06-16 03:00:00.000 1 53 1 165 -304.598546 0
13 2008-06-17 03:00:00.000 1 53 1 181 -12040.136172 0
18 2008-06-18 03:00:00.000 1 53 1 185 -39688.180278 0
21 2008-06-19 03:00:00.000 1 53 1 188 -112987.200625 0
24 2008-06-20 03:00:00.000 1 53 1 191 -14679.635 0
26 2008-06-20 18:20:12.000 1 53 2 1 60 0
28 2008-06-21 03:00:00.000 1 53 1 194 -55195.615937 0
31 2008-06-22 03:00:00.000 1 53 1 197 -9790.15872 0
34 2008-06-23 03:00:00.000 1 53 1 201 -254.1 0
36 2008-06-23 08:11:15.000 1 53 2 2 78 0
39 2008-06-23 10:08:18.000 1 53 2 5 30 0
40 2008-06-23 10:49:11.000 1 53 2 6 40 0
41 2008-06-23 10:54:45.000 1 53 2 7 57 0
42 2008-06-23 11:22:12.000 1 53 2 8 45 0
43 2008-06-23 11:23:55.000 1 53 2 9 50 0
46 2008-06-24 03:00:00.000 1 53 1 205 -2519.267803 0
49 2008-06-25 03:00:00.000 1 53 1 208 -34090.76025 0
15 2008-06-17 15:47:13.000 1 53 2 4 300 0
38 2008-06-23 09:14:58.000 1 53 2 4 100 0
44 2008-06-23 13:24:31.000 1 53 2 10 243679.47 0
|
Edited by - tkotey on 06/25/2008 08:18:51 |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47023 Posts |
Posted - 06/25/2008 : 08:23:07
|
| What does Transtype designate? |
|
|
|
tkotey
Yak Posting Veteran
75 Posts |
Posted - 06/25/2008 : 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 statement
Index CreateDate UserIndex Total Balance
3 2008-06-05 15:02:49.000 53 -288.2 -288.2
6 2008-06-10 08:59:10.000 53 1000 1000
7 2008-06-10 08:59:23.000 53 -13.2 -13.2
9 2008-06-12 11:42:46.000 53 -148.439793 -148.439793
11 2008-06-16 03:00:00.000 53 -304.598546 -304.598546
13 2008-06-17 03:00:00.000 53 -12040.136172 -12040.136172
18 2008-06-18 03:00:00.000 53 -39688.180278 -39688.180278
21 2008-06-19 03:00:00.000 53 -112987.200625 -112987.200625
24 2008-06-20 03:00:00.000 53 -14679.635 -14679.635
26 2008-06-20 18:20:12.000 53 60 60
28 2008-06-21 03:00:00.000 53 -55195.615937 -55195.615937
31 2008-06-22 03:00:00.000 53 -9790.15872 -9790.15872
34 2008-06-23 03:00:00.000 53 -254.1 -254.1
36 2008-06-23 08:11:15.000 53 78 78
39 2008-06-23 10:08:18.000 53 30 30
40 2008-06-23 10:49:11.000 53 40 40
41 2008-06-23 10:54:45.000 53 57 57
42 2008-06-23 11:22:12.000 53 45 45
43 2008-06-23 11:23:55.000 53 50 50
46 2008-06-24 03:00:00.000 53 -2519.267803 -2519.267803
49 2008-06-25 03:00:00.000 53 -34090.76025 -34090.76025
15 2008-06-17 15:47:13.000 53 300 300
38 2008-06-23 09:14:58.000 53 100 100
44 2008-06-23 13:24:31.000 53 243679.47 243679.47
|
|
|
|
Topic |
|
SUM two fileds from different rows from the same t
