Performing a Sum - SQL Server Forums

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

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

Performing a Sum

Author

Topic

ArielR
Starting Member

21 Posts

Posted - 2007-09-13 : 10:50:54

the table:
order...date.......Amount
1.......09/01/07...10.00
2.......09/01/07...15.00
3.......09/01/07...18.00
4.......09/01/07... 9.00
....
1......09/02/07....13.00
2......09/02/07....50.00
....

I need to perform a report that have a forth column "AcumAmount" where show the sum of Amounts where dates are less than the present date's record.

Result:
order...date......Amount....AcumAmount
1.......09/01/07.. 10.00..... 0.00
2.......09/01/07.. 15.00..... 10.00
3.......09/01/07.. 18.00..... 25.00
4.......09/01/07.. 9.00..... 43.00
....
1.......09/02/07.. 13.00..... 0.00
2.......09/02/07.. 50.00..... 13.00
....

Thank

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 10:56:57

What say the record with "2 09/02/07 50.00" should not be switched with the record of "2 09/02/07 15.00"?
What else do you have in your table to define true order?

E 12°55'05.25"
N 56°04'39.16"

ArielR
Starting Member

21 Posts

Posted - 2007-09-13 : 11:12:44

Sorry, now I have separated rows.They are Order_nro, Date(of Sale), amount, and other new for the view, AcumAmount. Thanks

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:16:27

In a relational database, the order of records has no meaning!
In yuor example above, the test data above

1.......09/01/07...10.00
2.......09/02/07...15.00
3.......09/03/07...18.00
4.......09/04/07... 9.00
....
1......09/01/07....13.00
2......09/02/07....50.00

can very well be interpreted as this instead

1.......09/01/07...10.00
2......09/02/07....50.00
3.......09/03/07...18.00
4.......09/04/07... 9.00
....
1......09/01/07....13.00
2.......09/02/07...15.00

Please notice that the two red records has shifted place.
This is what can happen in your code as of now.

I ask you again, what else do you have in your table that define your true order?
Do you have an identity column?

E 12°55'05.25"
N 56°04'39.16"

ArielR
Starting Member

21 Posts

Posted - 2007-09-13 : 11:31:48

I'm so sorry. I mean fixed the problem. Each day start the order at 1.

ArielR
Starting Member

21 Posts

Posted - 2007-09-13 : 11:33:36

and no have identity column in this sample. Thanks again

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:34:05

Each sequence of consecutive days, yes. We understand that.
Do you mean that the sample data above is flawed? Should the sample data above look like this instead?

1.......09/01/07...10.00
2......09/02/07....50.00
3.......09/03/07...18.00
4.......09/04/07... 9.00
....
1......09/08/07....13.00
2.......09/09/07...15.00

E 12°55'05.25"
N 56°04'39.16"

ArielR
Starting Member

21 Posts

Posted - 2007-09-13 : 11:49:37

Yes, is only an example, and may be like you wrote. The true intention is to obtain a sum of amount where date are less than the date of current record

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:55:10

Then help us by posting correct and accurate sample data.
Please remember that we are completely new to your problem!
We have no idea of your business rules.

All we have to go for, is the sample data you provide. If that is not correct, we have NO way to help you.

The question remains,

Can you, or can you not, have same date again for next sequence?

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 11:58:51

Because if you can have same date again in another sequence, there is no easy solution to your problem.

E 12°55'05.25"
N 56°04'39.16"

ArielR
Starting Member

21 Posts

Posted - 2007-09-13 : 12:02:46

No, the date is only one for each order. excuse me. And Thanks to be patient.

ArielR
Starting Member

21 Posts

Posted - 2007-09-13 : 12:18:50

Could you allow me a brute code:
SELECT date, order, amount, (select SUM(amount) from mytable where (date -1) )
FROM myTable;

something like this

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 12:22:30

Well, the aggregation is to be reset for each sequence.

E 12°55'05.25"
N 56°04'39.16"

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2007-09-13 : 12:24:03

(date - 1) is not a condition.

Do you need a running sum? or a sum for the previous day?

PS: If anyone is interested in a SQL Server Job in Connecticut with excellent pay send you resume to ValterBorges@msn.com

ArielR
Starting Member

21 Posts

Posted - 2007-09-13 : 12:34:16

Yes, really where the date will be less than the current record date.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-13 : 12:37:06

Wow... Sample data is totally new

DECLARE	@Sample TABLE ([Order] INT, Date DATETIME, Amount MONEY)

INSERT	@Sample
SELECT	1, '09/01/07', 10.00 UNION ALL
SELECT	2, '09/01/07', 50.00 UNION ALL
SELECT	3, '09/01/07', 18.00 UNION ALL
SELECT	4, '09/01/07',  9.00 UNION ALL
SELECT	1, '09/02/07', 13.00 UNION ALL
SELECT	2, '09/02/07', 15.00


SELECT		s1.[Order],
		s1.Date,
		s1.Amount,
		ISNULL((SELECT SUM(Amount) FROM @Sample AS s2 WHERE s2.Date = s1.Date AND s2.[Order] < s1.[Order]), 0)
FROM		@Sample AS s1

E 12°55'05.25"
N 56°04'39.16"

ArielR
Starting Member

21 Posts

Posted - 2007-09-13 : 13:16:11

Fantastic!!!, Great!!. Thank you very much!!.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources